PostgreSQL - SELF JOIN

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

Syntax

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

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

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:

Name1	City1	Name2	City2	Salary
John	London	Ramesh	New Delhi	3000
Jo	Paris	Huang	Beijing	2800
Ramesh	New Delhi	John	London	3000
Huang	Beijing	Jo	Paris	2800