Self-joins without the headache: a sql self join example that sticks

A self-join is a table joined to a copy of itself. That sounds strange the first time you hear it, but the trick that makes it click is simple: give the two copies two different names, and it behaves exactly like joining any two normal tables.

🎯 Explain Like I'm Hired Picture a staff table for a dark store: every rider has an employee_id, and a manager_id that points at another row in the same table (their shift lead is also an employee). To print "rider name, shift lead name" side by side, you join the staff table to itself: once as "the rider," once as "the shift lead." Example: asked "list every rider and who they report to," you'd alias the table twice (staff AS r, staff AS m) and join r.manager_id = m.employee_id.

SELECT
  r.name  AS rider_name,
  m.name  AS shift_lead_name
FROM staff r
LEFT JOIN staff m ON r.manager_id = m.employee_id;

staff r and staff m are the same table, read twice, under two names. Postgres has no idea they share a source; it just sees two tables and joins them on the condition you gave it.

The condition is what makes it a "self" join

Nothing about the syntax is special. It's a LEFT JOIN like any other, the only unusual part is that both sides point at the same underlying table. A LEFT JOIN keeps riders with no manager on file (founders, or a data-entry gap) with a blank shift_lead_name.

A second use: comparing rows within the same table

Self-joins aren't only for hierarchies. Another common shape: finding pairs of rows in one table that relate to each other. "Which customers placed two orders within 10 minutes of each other?" is a self-join of orders against itself, matching on customer_id while excluding a row matching itself:

SELECT
  a.customer_id,
  a.id AS order_a,
  b.id AS order_b
FROM orders a
JOIN orders b
  ON a.customer_id = b.customer_id
  AND a.id < b.id                                    -- avoid matching a row to itself, and avoid duplicate pairs
  AND b.placed_at - a.placed_at < interval '10 minutes';

a.id < b.id is the detail that trips people up: without it, every row matches itself (a.id = b.id), and every pair shows up twice (once as A-B, once as B-A).

The interview trap

A self-join with no filtering condition on the join produces every row paired with every other row, a result set that explodes in size. Always ask: what's the actual relationship I'm matching on (manager_id = employee_id, same customer_id, id < id)? That condition is the whole point of the query.

---

Next: GROUPING SETS, ROLLUP, CUBE →