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 "rows in this table compared with 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.
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, a barista 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 Marsh
Three people manage nobody — Priya Raman, Aisha Zahra and Lena Marsh — so their manager_id is NULL and 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, which are precisely the rows a reader most wants to see.
Why does a self join need two aliases?
Because staff.manager_id and staff.id live in the same table, and name certainly does. The aliases s and m are what turn one table into two distinct participants in the join: s is the staff member being listed, m is their manager. Without them the query is ambiguous — 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: an employees table with an employee_id and a manager_id, a categories table with a parent_category_id, a forum_posts table with a reply_to_post_id. The structure is identical every time.
What can a self join count?
Direct reports per manager, which is a self join 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 0
The LEFT JOIN produces one row per manager with zero reports where there are none, and COUNT counts the joined staff identifiers so the zero is real rather than an artifact of row counting. Lena Marsh has three direct reports, more than anyone else on the roster, but a headcount is not the whole story — a hierarchy spans levels, and counting the whole subtree needs either a recursive CTE (lesson 31) or a window function.
How do you find rows that share a parent?
Comparing two rows from the same table against each other is the other classic self join. Who works under the same manager? 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 Marsh
The a.id < b.id condition is what stops every pair appearing twice — once as (A, B) and once as (B, A). It is the standard way to ask a table about unordered pairs, and the same trick finds duplicate values, repeated combinations and co-occurring rows in any dataset.
How do you find two people who borrowed the same book?
Exactly the same idea, applied to the loans table, where two members borrowing the same title is a genuine finding 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 Noise
Priya Shah and Louis Marchand both borrowed The Salt Ledger, and Amara Nwosu and Ethan Brooks both borrowed Signals and Noise. Note that the self join here sits on the loans table while the books join is an ordinary join — self joins and normal joins can be mixed freely in one query. Also note the sort keys: l1.book_id first, then the two member ids, so the output is deterministic rather than dependent on scan order.
Quick reference
- A self join puts the same table on both sides of the ON clause under two aliases.
- One alias is the subject, the other is the related row — the alias names are the documentation.
- Use LEFT JOIN when the top of a hierarchy has no parent and must still be listed.
- Add an inequality such as a.id < b.id to stop unordered pairs appearing twice.
- Self joins work alongside ordinary joins in the same query.
Self-join drill: write a query that lists pairs of books written by the same author — the classic "customers who bought the same product" question in a library — and explain in one sentence what the inequality condition is protecting against.