SQL Self JOIN

What is a SQL Self JOIN?

  • 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

SELECT column-names
  FROM table-name T1 JOIN table-name T2
 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
SELECT B.FirstName AS FirstName1, B.LastName AS LastName1, 
       A.FirstName AS FirstName2, A.LastName AS LastName2, 
       B.City, B.Country
  FROM Customer A JOIN Customer B
    ON A.Id <> B.Id
   AND A.City = B.City 
   AND A.Country = B.Country
 ORDER BY A.Country
A and B are 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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas