Joining a table to itself

The same table, twice, with two names

A join does not care whether the two sides are different tables. When the relationship you want is between rows of the same table, you join it to itself and give each copy an alias:

SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

e and m are the same table. The database does not find this strange. The LEFT JOIN matters: the chief executive has no manager, and an inner join would remove the one person the org chart is rooted on.

Three things self-joins are actually for

Hierarchies. Manager and report, category and parent category, comment and the comment it replies to, region within country. Any table with a column pointing at its own primary key.

Comparing a row to another row of the same kind. Two payments by the same student, this month’s figure against last month’s, a price against the price of the previous version.

Finding pairs. Which two products are bought together; which two students share a phone number.

For that last one, the essential detail:

SELECT a.id, b.id, a.phone
FROM students a
JOIN students b ON b.phone = a.phone AND b.id > a.id;

b.id > a.id does two jobs at once. It stops each student matching themselves, and it returns each pair once rather than twice in both orders. Without it you get every row paired with itself plus every genuine pair duplicated, which is the classic wrong answer to “how many duplicates do we have”.

Consecutive rows, before window functions

A self-join can find the previous row per group:

SELECT p.id, p.paid_on, prev.paid_on AS previous_payment
FROM payments p
LEFT JOIN payments prev
  ON prev.student_id = p.student_id
 AND prev.paid_on = (
       SELECT max(paid_on) FROM payments x
       WHERE x.student_id = p.student_id AND x.paid_on < p.paid_on
     );

It works, and it is horrible. Read it twice and you can see why: the join condition contains a correlated subquery that runs per row. The window-function module replaces this whole construction with lag(paid_on) OVER (PARTITION BY student_id ORDER BY paid_on), which is one line, faster, and obvious. Know the self-join version because you will meet it in old code and because it shows what the window function is doing underneath; do not write new code this way.

Walking a hierarchy of unknown depth

A plain self-join goes one level. Two joins go two levels. For a tree of unknown depth you need a recursive CTE:

WITH RECURSIVE chain AS (
  SELECT id, full_name, manager_id, 1 AS depth
  FROM employees WHERE id = 42            -- the anchor: where to start
  UNION ALL
  SELECT e.id, e.full_name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id     -- the step: one level down
)
SELECT * FROM chain;

Two halves: a starting row, and a rule for getting from rows you have to rows you do not. The database applies the rule repeatedly until it produces nothing new.

The honest warning: if the data contains a cycle (someone is transitively their own manager, which happens in real HR systems after a reorganisation) this runs until it exhausts memory. Guard it with a depth limit (WHERE c.depth < 20) or by carrying the path travelled so far and refusing to revisit a node. Add the guard when you write it, not after the incident.

Performance, briefly

A self-join reads the table twice, and an index on the joined column is doing double duty. employees(manager_id) is the index that makes the org-chart query fast. Without it, a table of 50,000 employees means 50,000 lookups against an unindexed column: a quadratic amount of work, which is why these queries have a reputation for suddenly becoming slow when a company grows.

Naming

Alias every self-join with something meaningful. e and m for employee and manager. a and b for a symmetric pair. Never t1 and t2, because six months later nobody, including you, will remember which side was which, and in a self-join the two sides are indistinguishable except by their names.