When you need one
Self-joins answer questions that involve checking a row to another row of the same kind, which SQL or else makes awkward.
Hierarchies stored in one table. An employee's manager is another employee. A category's parent is another category. A reply's target is another comment. Joining the table to itself resolves the ref into real columns.
Row-to-row checks. "Who earns more than their manager" needs both salaries available at once. Run the third variant: Barbara earns 101,000 and reports to Grace on 98,000, and the join is what puts those two numbers in one row so a WHERE can compare them.
Pairing rows within a group. "Which colleagues share a manager" pairs the table with itself on manager_id.
The two conditions that keep pairs sane
The fourth variant holds a detail that looks small and isn't:
ON a.manager_id = b.manager_id AND a.id < b.id
Without a.id < b.id, two things go wrong. each row pairs with itself, because a row trivially shares its own manager. And each genuine pair appears twice, once as (Alan, Grace) and once as (Grace, Alan).
Writing a.id <> b.id fixes the first problem and not the second. Writing a.id < b.id fixes both at once. For any two distinct rows just one sorting meets it. That is the standard idiom for "each unordered pair once" and it's worth recognising on sight.
The top of the hierarchy disappears
Run the first variant and count the rows: five, not six. Ada is missing.
Ada is the root — her manager_id is NULL — and an inner join needs a match on both sides. NULL matches nothing. So Her row is dropped without comment.
The second variant uses LEFT JOIN and Ada reappears with a NULL manager. This is nearly always what was intended, and the failure is quiet enough to reach production: the query works, returns plausible rows, and is missing just the most senior person in the organisation.
Depth is where it stops
A self-join resolves one level. Employee to manager is one join. Employee to manager's manager is two, written out by hand:
FROM employees e
JOIN employees m ON e.manager_id = m.id
JOIN employees mm ON m.manager_id = mm.id
Three levels needs three, and an any depth can't be written this way at all, because the number of joins would have to depend on the data.
That is the boundary where the technique runs out and recursive CTEs take over. A recursive CTE walks down as many levels as exist. It's the correct tool the moment the depth is not known in advance.
Why the pair condition is not optional
The a.id < b.id line is easy to read past. It's worth seeing what its lack does to the row count. Pairing a group against itself is a Cartesian product within the group: n colleagues under one manager give n × n sorted pairs before any rule trims them.
| colleagues under a manager | no condition (n²) | a.id <> b.id | a.id < b.id |
|---|
| 5 | 25 | 20 | 10 |
| 10 | 100 | 90 | 45 |
| 50 | 2,500 | 2,450 | 1,225 |
The middle column still counts each pair twice. Only a.id < b.id gives the n(n-1)/2 unordered pairs you meant. And the gap is quadratic, so on a manager with fifty reports the wrong rule doesn't return a few extra rows — it returns 2,500 where 1,225 were wanted, and any total found over them comes out roughly doubled. That is why the idiom is worth recognising on sight rather than rediscovering after a report reads twice what it should.
A self-join reads the table twice
The other cost hides in the plan. A self-join names the table twice, so the engine reads it twice, and if the join column is unindexed each read is a full scan with a hash build in between. On employees self-joined on manager_id:
manager_id | plan |
|---|
| indexed | SEARCH the second copy per row — a seek |
| unindexed | SCAN both copies, hash-join in memory |
On a hundred employees no one notices. On a hundred thousand, the unindexed version builds a hash table of each row and probes it with each row — the work grows with the square of the table where the indexed plan grows with its logarithm. A hundred thousand employees joined without that index is on the order of ten billion row checks. With it, a hundred thousand seeks of about seventeen steps each — the difference between a query that returns and one that does not. The index on the parent column that a foreign key would give you is on the *referenced* side. The self-join reads the *referencing* column, which is the one that often lacks an index. This is easy to miss until the table grows.
Questions people ask about Self-Joins
What is a self join? It is a join of a table to itself, using two different aliases so each copy can be referred to separately.
Why do I need aliases in a self join? Without aliases the column names are ambiguous, because both copies share the same names. Aliases such as e and m let you say which copy you mean.
How do I stop a self join pairing a row with itself? Add a condition such as a.id <> b.id. For unordered pairs use a.id < b.id, which also removes mirrored duplicates.
Why does the top of my hierarchy vanish from the result? An inner join drops rows with no match, and the top manager has no manager. Use a LEFT JOIN to keep them.
How many levels can a self join walk? One level per join. For a hierarchy of unknown depth, use a recursive CTE instead.
Are self joins slow? They are as fast as the join condition allows. Index the column you join on, such as manager_id, and they stay quick.
Where it goes wrong with Self-Joins
Inner join on a nullable parent. The root vanishes. This is the single most common self-join bug.
Losing the anti-self rule. Without a.id < b.id each row pairs with itself, and totals come out roughly double.
Using one for any depth. If the query has four copies of the same table and someone is about to add a fifth, it wants to be recursive.
Leaving the join column unindexed. A self-join reads the table twice. On a large table with no index on manager_id, that is two full scans and a hash in between. Add the index before the table grows.