A self join is a join in which a table appears twice, once on each side of the ON clause, usually under two different aliases. It is the answer to every question of the shape of "the rows in this table compared with the other rows in the same table". A manager and their reports, two members who borrowed the same book, an employee and the employee who replaced them.
The previous lesson in this module covered "When Are FULL OUTER and CROSS Joins Right?", and the next lesson in this module is about "How Do You Chain Joins Across Many Tables?".
What is a self join?
The bookshop's staff table stores a manager reference in its own manager_id column. Every row points at another row in the same table. A bookseller reports to a sales manager, and a barista reports to a bookseller. Joining that table to itself is the only way to read the hierarchy.
SELECT s.name AS staff_member, s.role, m.name AS reports_to
FROM staff s
LEFT JOIN staff m ON s.manager_id = m.id
ORDER BY s.id;staff_member role reports_to
------------- ------------------- -----------
Priya Raman Buyer NULL
Dana Kowalski Buyer Priya Raman
Aisha Zahra Cafe Lead NULL
Marcus Bell Bookseller Lena Marsh
Lena Marsh Sales Manager NULL
Tom Whitfield Bookseller Lena Marsh
Joe Naylor Barista Marcus Bell
Ruth Adeyemi Seasonal Bookseller Lena MarshThere are three people who manage nobody. Priya Raman, Aisha Zahra and Lena Marsh. Their manager_id is a NULL, and so their reports_to is empty.
Using a LEFT JOIN rather than an inner join is what keeps those three rows in the result. An inner join would hide the people at the top of the hierarchy, and those are precisely the rows that a reader most wants to see.
Why does a self join need two aliases?
Because the staff.manager_id and the staff.id live in the same table, and the name certainly does. The aliases s and m are what turn the one table into the two distinct participants in the join. The s is the staff member that is being listed, and the m is their manager.
Without them the query is ambiguous. The condition ON staff.manager_id = staff.id would compare the table to itself with no way to say which row plays which role.
The same trick applies to any table that stores a relationship to itself, and the pattern is worth recognising wherever you meet it. There is an employees table with an employee_id and a manager_id. There is a categories table with a parent_category_id. There is a forum_posts table with a reply_to_post_id. The structure is identical every time.
What can a self join count?
The direct reports per manager, which is a self join that is aggregated with a GROUP BY.
SELECT m.name AS manager, COUNT(s.id) AS direct_reports
FROM staff m
LEFT JOIN staff s ON s.manager_id = m.id
GROUP BY m.name
ORDER BY direct_reports DESC, m.name;manager direct_reports
------------- --------------
Lena Marsh 3
Marcus Bell 1
Priya Raman 1
Aisha Zahra 0
Dana Kowalski 0
Joe Naylor 0
Ruth Adeyemi 0
Tom Whitfield 0The LEFT JOIN produces one row per manager, with the zero reports where there are none, and the COUNT counts the joined staff identifiers, so the zero is real and not an artefact of the row counting.
Lena Marsh has the three direct reports, more than anybody else on the roster, but a headcount is not the whole story. A hierarchy spans the levels, and counting the whole subtree needs either a recursive CTE in lesson 31 or a window function.
How do you find the rows that share a parent?
You compare the two rows from the same table against each other, and that is the other classic self join. You find out who works under the same manager by joining the staff table to itself, once as each person.
SELECT a.name AS staff_a, b.name AS staff_b, m.name AS shared_manager
FROM staff a JOIN staff b ON a.manager_id = b.manager_id AND a.id < b.id
JOIN staff m ON m.id = a.manager_id
ORDER BY m.name, a.name;staff_a staff_b shared_manager
------------- ------------- --------------
Marcus Bell Ruth Adeyemi Lena Marsh
Marcus Bell Tom Whitfield Lena Marsh
Tom Whitfield Ruth Adeyemi Lena MarshThe condition a.id < b.id is what stops every pair from appearing twice, once as the (A, B) and once as the (B, A). It is the standard way to ask a table about the unordered pairs, and the same trick finds the duplicate values, the repeated combinations and the co-occurring rows in any dataset.
How do you find the two people who borrowed the same book?
It is exactly the same idea, applied to the loans table, where the two members borrowing the same title is a genuine finding that is worth reporting.
SELECT l1.member_id AS member_a, l2.member_id AS member_b, l1.book_id, b.title
FROM loans l1
JOIN loans l2 ON l1.book_id = l2.book_id AND l1.member_id < l2.member_id
JOIN books b ON b.book_id = l1.book_id
ORDER BY l1.book_id, l1.member_id, l2.member_id;member_a member_b book_id title
-------- -------- ------- -----------------
1 4 1 The Salt Ledger
5 8 7 Signals and NoisePriya Shah and Louis Marchand both borrowed The Salt Ledger, and Amara Nwosu and Ethan Brooks both borrowed Signals and Noise.
Notice that the self join here sits on the loans table while the join to the books is an ordinary join. The self joins and the normal joins can be mixed freely in the one query.
Also notice the sort keys. There is the l1.book_id first, and then the two member ids, so that the output is deterministic and not dependent on the scan order.
Quick reference
- A self join puts the same table on both of the sides of the ON clause under the two aliases.
- One alias is the subject and the other is the related row, and the alias names are the documentation.
- You use the LEFT JOIN when the top of a hierarchy has no parent and it must still be listed.
- You add an inequality such as
a.id < b.idto stop the unordered pairs from appearing twice. - The self joins work alongside the ordinary joins in the same query.
Self-join drill: you write a query that lists the pairs of the books that are written by the same author, which is the classic "customers who bought the same product" question in a library. Then you explain in one sentence what the inequality condition is protecting against.