SQL Self JOIN

SQL Self JOIN : SQL Self JOIN is a regular join, but the table is joined with itself.

Self JOIN Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

 

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

 

SQL Self JOIN Example

The following SQL statement matches customers that are from the same city:

Example

SELECT A.CustomerName AS CustomerName1, B.CustomerName ASCustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

 

Output

Number of Records: 7

CustomerName1	CustomerName2	City
Cactus Comidas para llevar	Océano Atlántico Ltda.	Buenos Aires
Cactus Comidas para llevar	Rancho grande	Buenos Aires
Océano Atlántico Ltda.	Cactus Comidas para llevar	Buenos Aires
Océano Atlántico Ltda.	Rancho grande	Buenos Aires
Rancho grande	Cactus Comidas para llevar	Buenos Aires
Rancho grande	Océano Atlántico Ltda.	Buenos Aires
Furia Bacalhau e Frutos do Mar	Princesa Isabel Vinhoss	Lisboa