SQL Server - SELF JOIN

The SQL Server (Transact-SQL) SELF JOIN is a regular join where a table is joined with itself.

Syntax

The syntax of SELF JOIN in SQL Server (Transact-SQL) is given below:

SELECT A.column1, A.column2, B.column1, B.column2, ...
FROM table A, table B
WHERE condition(s);

Where A and B are different table aliases for the same table.

Example:

Consider database tables called Employee and Contact_Info with the following records:

Table 1: Employee table

In the below query Employee table is joined with itself. The query is used to fetch the Name and City of those employees who are earning same salary.

SELECT A.Name AS Name1, A.City AS City1, 
B.Name AS Name2, B.City AS City2, A.Salary
FROM Employee A, Employee B
WHERE A.Salary = B.Salary AND A.EmpID <> B.EmpID

This will produce the result as shown below: