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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.