SQL Self Join

A self join in SQL is a way to join a table to itself. This is useful when you need to compare or relate rows within the same table based on some common attribute.

Self Join: Definition and Usage

You essentially treat the same table as two different tables by giving it two different aliases. This lets you compare rows based on shared values in columns. A common use case is identifying pairs of records that share a characteristic, such as customers in the same city or employees reporting to each other.

Syntax

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

T1 and T2 are aliases for the same table, allowing you to refer to the same table as two different entities within the query.

Example Database

We'll use a simplified 'Customers' table from the Northwind database:

Example: Finding Customers in the Same City

This query identifies pairs of customers who share the same city. Note that it excludes pairing a customer with themself.

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;
      

CustomerName1                      | CustomerName2                      | City
--------------------------------------------------------------------------
Ana Trujillo Emparedados y helados | Antonio Moreno Taquería             | México D.F.
(More pairs of customers from the same city would appear here)