SQL Self JOIN ~ niit post

• A self JOIN occurs when a table takes a 'selfie'.
• A self JOIN is a regular join but the table is joined with itself.
• This can be useful when modeling hierarchies.
• They are also useful for comparisons within a table.

The SQL Self JOIN syntax

The general syntax is: 

1.  SELECT column-names

2.    FROM table-name T1 JOIN table-name T2

3.    WHERE condition

T1 and T2 are different table aliases for the same table 

CUSTOMER

Id

FirstName

LastName

City

Country

Phone

CUSTOMER

Id

FirstName

LastName

City

Country

Phone

SQL Self JOIN Examples

Problem: Match customers that are from the same city and country 

1.  SELECT B.FirstName AS FirstName1, B.LastName AS LastName1, 

2.         A.FirstName AS FirstName2, A.LastName AS LastName2, 

3.         B.City, B.Country

4.    FROM Customer A, Customer B

5.   WHERE A.Id <> B.Id

6.     AND A.City = B.City 

7.     AND A.Country = B.Country

8.   ORDER BY A.Country

A and B are aliases for the same Customer table. 

Results: 88 records 

FirstName1	LastName1	FirstName2	LastName2	City	Country
Patricio	Simpson	Yvonne	Moncada	Buenos Aires	Argentina
Patricio	Simpson	Sergio	Gutiérrez	Buenos Aires	Argentina
Yvonne	Moncada	Patricio	Simpson	Buenos Aires	Argentina
Yvonne	Moncada	Sergio	Gutiérrez	Buenos Aires	Argentina
Sergio	Gutiérrez	Patricio	Simpson	Buenos Aires	Argentina
Sergio	Gutiérrez	Yvonne	Moncada	Buenos Aires	Argentina
Anabela	Domingues	Lúcia	Carvalho	Sao Paulo	Brazil
Anabela	Domingues	Aria	Cruz	Sao Paulo	Brazil
Anabela	Domingues	Pedro	Afonso	Sao Paulo	Brazil
Bernardo	Batista	Janete	Limeira	Rio de Janeiro	Brazil
Bernardo	Batista	Mario	Pontes	Rio de Janeiro	Brazil
Lúcia	Carvalho	Anabela	Domingues	Sao Paulo	Brazil
Lúcia	Carvalho	Aria	Cruz	Sao Paulo	Brazil
Lúcia	Carvalho	Pedro	Afonso	Sao Paulo	Brazil
Janete	Limeira	Bernardo	Batista	Rio de Janeiro	Brazil
Janete	Limeira	Mario	Pontes	Rio de Janeiro	Brazil
Aria	Cruz	Anabela	Domingues	Sao Paulo	Brazil
Aria	Cruz	Lúcia	Carvalho	Sao Paulo	Brazil
Aria	Cruz	Pedro	Afonso	Sao Paulo	Brazil
Mario	Pontes	Bernardo	Batista	Rio de Janeiro	Brazil
Mario	Pontes	Janete	Limeira	Rio de Janeiro	Brazil
Pedro	Afonso	Anabela	Domingues	Sao Paulo	Brazil
Pedro	Afonso	Lúcia	Carvalho	Sao Paulo	Brazil
Pedro	Afonso	Aria	Cruz	Sao Paulo	Brazil
Dominique	Perrier	Marie	Bertrand	Paris	France
Marie	Bertrand	Dominique	Perrier	Paris	France
Janine	Labrune	Carine	Schmitt	Nantes	France
Carine	Schmitt	Janine	Labrune	Nantes	France
...