Dofactory.com
Dofactory.com

SQL Self JOIN

In a SQL Self JOIN, a table is joined with itself.

This can be useful when modeling hierarchies.

Another usage is to find duplicates within a table.

Example

#

Match suppliers that are from the same country.

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

A and B are table aliases for the same Supplier table.
Company1 and Company2 are column aliases.

Result:  36 records
Company1 Company2 Country
Pavlova, Ltd. G'day, Mate Australia
G'day, Mate Pavlova, Ltd. Australia
Ma Maison Forêts d'érables Canada
Forêts d'érables Ma Maison Canada
Aux joyeux ecclésiastiques Escargots Nouveaux France
Aux joyeux ecclésiastiques Gai pâturage France
Escargots Nouveaux Aux joyeux ecclésiastiques France
Escargots Nouveaux Gai pâturage France

Syntax

Self JOIN syntax.

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

T1 and T2 are different table aliases for the same table.


More Examples

Self JOIN

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

Self JOIN, finding DUPLICATES

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Find customers with the same first name (i.e. duplicates).
SELECT CONCAT(C1.FirstName, ' ', C1.LastName) AS Name, 
       CONCAT(C2.FirstName, ' ', C2.LastName) AS Duplicate
  FROM Customer C1
  JOIN Customer C2 ON C1.Id <> C2.Id
 WHERE C1.FirstName = C2.FirstName
 ORDER BY C1.FirstName

C1 and C2 are table aliases for the same Customer table.

Result:  10 records
Name Duplicate
Carlos González Carlos Hernández
Carlos Hernández Carlos González
Elizabeth Brown Elizabeth Lincoln
Elizabeth Lincoln Elizabeth Brown
Maria Anders Maria Larsson
Maria Larsson Maria Anders
Paula Parente Paula Wilson
Paula Wilson Paula Parente
Yoshi Tannamuri Yoshi Latimer
Yoshi Latimer Yoshi Tannamuri

You may also like



Last updated on Dec 21, 2023