SQL Self Join

A self-join is essentially a join operation where a table is joined with itself.

Self Join Syntax

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

T1 and T2 serve as distinct table aliases representing the same underlying table.

Demo Database

Throughout this tutorial, we’ll be working with the widely recognized Northwind sample database.

Here’s a portion 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
México D.F.3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

SQL Self Join Example

This SQL statement pairs customers who are located in the same city.

Example

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