SQL Self JOIN

The SQL Self JOIN syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename T1 JOIN tablename T2
  3. WHERE condition

T1 and T2 are different table aliases for the same table


 

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

“SQL Self JOIN”의 5개의 댓글

  1. 핑백: mix canzoni estate 2021

  2. 핑백: nam mo thay di hoc

  3. 핑백: tropical house mix 2020

  4. 핑백: Tyson vs Jones jr live stream online

댓글 남기기