SQL Self JOIN

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.

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

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.

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.

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

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.

• Our Sql JOIN Tutorial
• Our Sql FULL JOIN Tutorial
• Our Sql INNER JOIN Tutorial
• Our Sql OUTER JOIN Tutorial