When would you use a self-join, and how do you write one?

How to think about it

A self-join isn’t a special join type — it’s an ordinary join where both sides reference the same table through different aliases. The whole skill is spotting when it’s the natural tool: the tell is rows in one table that relate to each other — a hierarchy, a pair, a sequence.

A worked example — employee–manager hierarchy

The classic case. One table holds every person, and manager_id points back to another row in the same table. Use LEFT JOIN, not INNER, so the CEO (no manager) survives instead of being silently dropped:

SELECT e.name AS employee,
       COALESCE(m.name, '(no manager)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

employee	manager
CEO	(no manager)
VP Eng	CEO
VP Sales	CEO
Aarav	VP Eng
Bea	VP Eng
Chen	VP Sales

Two aliases of employees — e for the person, m for their manager — joined on e.manager_id = m.id. The CEO’s manager_id is NULL, so an INNER JOIN would drop that row entirely; LEFT JOIN plus COALESCE keeps it as “(no manager)”.

The asymmetric-guard pattern

The other big use is comparing each row to other rows in the table — finding duplicates, or repeat purchases within a window. The catch is that a naive self-join pairs every row with itself and counts each pair twice. An asymmetric guard (a.id < b.id) fixes both:

-- products with the same SKU = duplicates, each pair once
SELECT a.id, b.id AS duplicate_id
FROM products a
JOIN products b ON a.sku = b.sku AND a.id < b.id;

The a.id < b.id does double duty: < (not <=) drops the trivial self-match, and the ordering ensures the pair (A, B) appears once, never also as (B, A). Forgetting this guard is the classic self-join mistake — and it’s a Cartesian explosion, not a subtle off-by-one.