🎓 A1Learner
🔍

SQL Self Join

A self join is a join where a table joins itself. Two aliases give the same table two separate names in one query.

One table, two copies

The aliases a and b read the customers table as two copies. Each city matches itself here, so most rows pair a customer with the same customer.

SELECT a.name AS customer_a, b.name AS customer_b, a.city
FROM customers AS a
JOIN customers AS b ON a.city = b.city
ORDER BY a.customer_id, b.customer_id;

Keeping the pairs apart

The id test keeps only pairs where the first id is smaller. One pair survives, and no customer pairs with the same customer.

SELECT a.name AS customer_a, b.name AS customer_b, a.city
FROM customers AS a
JOIN customers AS b ON a.city = b.city AND a.customer_id < b.customer_id
ORDER BY a.customer_id, b.customer_id;

Example

SELECT a.name AS first_customer, b.name AS second_customer, a.city, a.country
FROM customers AS a
JOIN customers AS b ON a.city = b.city AND a.customer_id < b.customer_id
ORDER BY a.customer_id;

Key points

  • Self join: a join of one table to itself under two aliases
  • Use two aliases to keep the two copies of the table apart.
  • Each alias reads its own copy of the rows.
  • A test on the ids can stop a row from pairing with itself.
  • The same id test also stops the reversed pair from appearing twice.

In this example, alias a and alias b read two copies of the customers table. The ON clause matches the copies on city and then keeps the pairs with the smaller id first. Ben Carter and Eli Ross share Austin, so they form the one pair. Hana Sato has a NULL city, and NULL matches nothing, not even itself, so she forms no pair.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

Why does a self join give the table two aliases?