SQL Self JOIN

SQL Self JOIN Explained

A Self JOIN occurs when a table is joined with itself.

This can be useful when modeling hierarchies.

Self JOINs are also useful for comparisons within a table.

Example

#

Problem: Match suppliers from the same country.

SELECT A.CompanyName AS Company1, B.CompanyName AS Company2, A.Country
  FROM Supplier A, Supplier B
 WHERE A.Id <> B.Id
   AND A.Country = B.Country 
 ORDER BY A.Country

Note: A and B are table Aliases for the same Supplier table.
Company1 and Company2 are column Aliases.


Syntax

Self JOIN syntax.

SELECT column-names
  FROM table-name T1 
  JOIN table-name T2
 WHERE condition

Note: T1 and T2 are different table Aliases for the same table.


More Examples

SQL Self JOIN

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Match customers that are from the same city and country.
SELECT B.FirstName AS FirstName1, B.LastName AS LastName1, 
       A.FirstName AS FirstName2, A.LastName AS LastName2, 
       B.City, B.Country
  FROM Customer A, Customer B
 WHERE A.Id <> B.Id
   AND A.City = B.City 
   AND A.Country = B.Country
 ORDER BY A.Country

Note: A and B are table Aliases for the same Customer table.

Result:  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

You may also like



Guides