SQL Tutorial SQL Advanced SQL Database SQL References

SQL - SELF JOIN



The SQL SELF JOIN is a regular join where a table is joined with itself.

Syntax

The syntax of SELF JOIN 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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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:

Name1City1Name2City2Salary
JohnLondonRameshNew Delhi3000
JoParisHuangBeijing2800
RameshNew DelhiJohnLondon3000
HuangBeijingJoParis2800