Self-joins

The idea

A self-join is one table wearing two hats. You name employees twice, give each copy its own alias, and from that moment they behave like two independent tables that happen to hold identical rows.

The join condition decides which hat each copy wears. e.manager_id = m.id makes one copy the report and the other the manager; the rows never moved, only the reading of them.

Pairing a table with itself is different. With no direction in the condition, every row meets itself and every pair arrives twice: once forwards, once backwards. A strict inequality like a.id < b.id is the usual cure.

How it works

Nothing special happens in the engine: the planner treats the second mention as a second input stream over the same physical rows. All the work is in the aliases and the ON clause. Qualify every column, or the parser cannot know which copy you mean.

-- one table, two roles: e is the report, m is the manager
SELECT e.name AS employee,
       m.name AS manager
FROM   employees AS e
JOIN   employees AS m
       ON e.manager_id = m.id;

-- keep the person at the top (manager_id IS NULL matches nothing)
SELECT e.name, m.name AS manager
FROM   employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id;

-- every unordered pair of distinct rows, exactly once
SELECT a.name, b.name
FROM   employees AS a
JOIN   employees AS b ON a.id < b.id;

-- each event paired with the NEXT one for the same user
SELECT e.id, MIN(nx.ts) AS next_ts
FROM   events AS e
JOIN   events AS nx
       ON nx.user_id = e.user_id
      AND nx.ts > e.ts
GROUP BY e.id;

Read a.id < b.id as “keep the pair only when it points uphill”. Any strict total order works (an id, a timestamp, a (date, id) tuple) as long as no two rows tie.

Cost

The trade-off: an equality self-join on a key is cheap and linear-ish; an inequality self-join is a nested loop in disguise. a.id < b.id does not change the asymptotics of the comparison, but it halves everything downstream: output rows, sorts, aggregates, network.

Watch out for

• Unqualified columns. With two copies in scope, bare name is ambiguous and the query fails, or worse, silently resolves to the wrong copy in a WHERE or ORDER BY. Alias both sides and prefix every single column.
• <> where you meant <. a.id <> b.id removes the self-pairs but keeps both (2,3) and (3,2). Only a strict inequality on a total order collapses the mirror image.
• The inner join eats the root. The CEO has manager_id IS NULL, and NULL equals nothing, not even NULL. An inner self-join drops that row without a word. Use LEFT JOIN when the hierarchy top matters.
• “Next event” is not “any later event”. nx.ts > e.ts pairs each row with every row after it. Narrow it with MIN(nx.ts), a NOT EXISTS gap check, or just use LEAD().
• Duplicated join keys multiply. Self-joining on a non-unique column turns a group of k identical keys into k² rows. Check the row count before you trust the aggregate sitting on top of it.

Worked example

Take the five rows from the diagram and add salaries: Ada 190, Brie 120, Cal 130, Dev 125, Eli 90. The question is who earns more than their manager? A comparison between two rows of the same table, so the table must appear twice.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary
FROM   employees AS e
JOIN   employees AS m ON e.manager_id = m.id
WHERE  e.salary > m.salary;

The join builds four pairs: Brie→Ada, Cal→Ada, Dev→Brie, Eli→Brie. Ada never appears on the left, because her manager_id is NULL and finds no partner. Then the filter runs on the pair, comparing two salaries that live in two different rows: 120>190 no, 130>190 no, 125>120 yes, 90>120 no. One row comes back: Dev, 125, Brie, 120.

Notice how naturally the condition reads once the roles are named. Had you written the pairing as a.id < b.id instead, you would be asking a completely different question: pairs of colleagues, ten of them, with no hierarchy implied at all.

Check yourself