SQL / JOINS
Self joins: relating rows within one table
Join a table to itself with two aliases to pair each row with a related row in the same table: manager lookups, coworker pairs, and multi-level chains.
What you will learn
- Alias both copies of the table so every column reference names one specific role
- Turn a pointer column into real data with ON m.id = e.manager_id
- Keep root rows whose parent is NULL by using LEFT JOIN, not INNER JOIN
- Pair rows once with b.id > a.id instead of getting self-matches and mirrors
Understanding Self joins: relating rows within one table
Some relationships live inside a single table. employee.manager_id does not point at another table, it points at employee.id, so the manager's name sits in a different row of the same table. To read both rows in one result you list the table twice under two aliases: the engine then treats e and m as two independent row sources that happen to share storage, and the ON condition decides which pair of rows lines up. Nothing about the join algorithm changes, so INNER and LEFT behave here exactly as they do across two different tables.
Direction is carried entirely by the ON condition, and it is easy to write backwards. m.id = e.manager_id means the m row is the one whose key the e row points at, which yields one output row per employee. Flip it to m.manager_id = e.id and you are reading from the manager's side instead, so a manager with three reports appears three times. Self joins also do not require equality: conditions like b.id > a.id or b.price > a.price turn the join into a row-pairing or row-comparison tool rather than a lookup.
The two behaviours that surprise people are NULL and multiplicity. A root row, the founder whose manager_id is NULL, has nothing to match, so an INNER JOIN silently removes it and the report comes out one person short with no error. Multiplicity is the mirror image: because the manager alias is repeated once per report, summing or counting a manager-side column over that result counts the same manager several times. Each ON clause also climbs exactly one level, which is why a fixed self join can answer "who is my boss's boss" but never "everyone above me".
CREATE TABLE employee (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER -- points at employee.id
);
INSERT INTO employee (id, name, manager_id) VALUES
(1, 'Ada', NULL),
(2, 'Grace', 1),
(3, 'Linus', 1),
(4, 'Barbara', 2),
(5, 'Ken', 2);
SELECT e.name AS person,
m.name AS reports_to
FROM employee AS e
LEFT JOIN employee AS m ON m.id = e.manager_id
ORDER BY e.name;A self join is the ordinary join machinery pointed at two aliases of one table, so the ON condition compares one row against another row of the same table.
Worked examples
Two levels up with three aliases
Following the same pointer column twice to reach a boss and that boss's boss.
CREATE TABLE staff (id INTEGER, name TEXT, boss_id INTEGER);
INSERT INTO staff (id, name, boss_id) VALUES
(1, 'Ada', NULL),
(2, 'Grace', 1),
(3, 'Barbara', 2),
(4, 'Ken', 2);
SELECT s.name AS employee,
b.name AS boss,
gb.name AS skip_level
FROM staff AS s
LEFT JOIN staff AS b ON b.id = s.boss_id
LEFT JOIN staff AS gb ON gb.id = b.boss_id
ORDER BY s.id;Example explained
Line 1b.id = s.boss_id resolves the first hop, then gb.id = b.boss_id follows the pointer that sits on the already-joined boss row.
Line 2Three roles need three aliases of one table; the aliases are just extra row sources, the stored table is untouched.
Line 3Grace shows boss Ada but a NULL skip_level because Ada's boss_id is NULL, so the second LEFT JOIN matches nothing and fills the column with NULL.
Pairing rows that share a value
Listing each pair of books by the same author exactly once, using an inequality in the join condition.
CREATE TABLE book (id INTEGER, title TEXT, author TEXT);
INSERT INTO book (id, title, author) VALUES
(1, 'Dune', 'Herbert'),
(2, 'Dune Messiah', 'Herbert'),
(3, 'Children of Dune', 'Herbert'),
(4, 'Solaris', 'Lem'),
(5, 'Anonymous Verse', NULL);
SELECT a.title AS book_a,
b.title AS book_b
FROM book AS a
JOIN book AS b
ON b.author = a.author
AND b.id > a.id
ORDER BY a.id, b.id;Example explained
Line 1ON b.author = a.author relates rows by a shared value rather than by a key-to-pointer link, so a self join is not only for hierarchies.
Line 2AND b.id > a.id keeps each pair once; without it the query returns ten rows, since every book also matches itself and each Herbert pair appears in both directions.
Line 3Solaris is absent because Lem has one book and an INNER JOIN drops partnerless rows, and Anonymous Verse is absent because NULL = NULL is unknown, so it cannot even match itself.
Important notes
One ON clause climbs one level. Three aliases reach a grandparent, but a chain of unknown depth needs a recursive CTE, not more aliases.
The manager side is repeated once per report, so SUM(m.salary) over a self-joined result adds that manager's salary once for each of their reports.
Common mistakes
Writing FROM employee JOIN employee ON manager_id = id with no aliases: Postgres reports the table name specified more than once, MySQL reports a non-unique alias, SQLite reports an ambiguous column, so nothing runs.
Using INNER JOIN for the employee-and-manager report: every top-level row with manager_id IS NULL disappears without any error, so the result quietly has fewer people than the table.
Pairing rows with ON b.dept = a.dept and no b.id > a.id: a department of n people returns n squared rows, with everyone paired to themselves and each real pair listed twice.
Try it yourself
Change, predict, then run
Using the employee table from the main example, insert (6, 'Sophie', 5) and write a self join that lists every pair of employees sharing a manager, with no self-pairs and no pair repeated. Then change the main query's LEFT JOIN to INNER JOIN and confirm which row vanishes and why.
Open the SQL workspaceCheck your understanding
A table staff(id, name, boss_id) has five rows: a founder with boss_id NULL and four people with boss_id = 1. How many rows does SELECT a.name, b.name FROM staff a JOIN staff b ON b.boss_id = a.boss_id return?
- 16
- 12
- 20
- 25
Show answer
Each of the four rows with boss_id = 1 matches all four rows carrying that same value, itself included, so the result is 4 x 4 = 16. 12 is what you get only after adding a.id <> b.id to remove the self-matches, and the founder contributes nothing because NULL = NULL is unknown rather than true, which rules out 20 and 25.