SQL SELF JOIN

A self-join joins a table to itself, using two aliases. It's the standard way to traverse hierarchical data like manager/employee, category/parent, and friend-of-friend.

Employees and their managers

SELECT a.name AS employee,
       b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

Using LEFT JOIN includes the CEO (whose manager_id is NULL).

Categories and parents

SELECT c.name AS category,
       p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Finding pairs that share a value

-- Customers in the same country
SELECT a.name, b.name, a.country
FROM customers a
JOIN customers b
  ON a.country = b.country
 AND a.id     < b.id;

The a.id < b.id trick keeps each pair only once and skips self-pairs.

Multi-level hierarchies

A single self-join walks one level up. For arbitrary depth, you need a recursive CTE — WITH RECURSIVE in PostgreSQL, MySQL 8, SQLite, and SQL Server.

Example

SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

Exercise

Test yourself