{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"self-joins","title":"Joining a table to itself","url":"https://addaly.com/learn/data-and-sql/self-joins","position":18,"of":66,"minutes":8,"answer":"A self-join is the same table under two names; add a.id < b.id so that no row matches itself and no pair appears twice.","keyIdea":"A self-join is the same table under two names; add `a.id < b.id` so that no row matches itself and no pair appears twice."},"body":{"format":"markdown","text":"## The same table, twice, with two names\n\nA join does not care whether the two sides are different tables. When the relationship you want is between rows of the *same* table, you join it to itself and give each copy an alias:\n\n```sql\nSELECT e.full_name AS employee, m.full_name AS manager\nFROM employees e\nLEFT JOIN employees m ON m.id = e.manager_id;\n```\n\n`e` and `m` are the same table. The database does not find this strange. The LEFT JOIN matters: the chief executive has no manager, and an inner join would remove the one person the org chart is rooted on.\n\n## Three things self-joins are actually for\n\n**Hierarchies.** Manager and report, category and parent category, comment and the comment it replies to, region within country. Any table with a column pointing at its own primary key.\n\n**Comparing a row to another row of the same kind.** Two payments by the same student, this month's figure against last month's, a price against the price of the previous version.\n\n**Finding pairs.** Which two products are bought together; which two students share a phone number.\n\nFor that last one, the essential detail:\n\n```sql\nSELECT a.id, b.id, a.phone\nFROM students a\nJOIN students b ON b.phone = a.phone AND b.id > a.id;\n```\n\n`b.id > a.id` does two jobs at once. It stops each student matching themselves, and it returns each pair once rather than twice in both orders. Without it you get every row paired with itself plus every genuine pair duplicated, which is the classic wrong answer to \"how many duplicates do we have\".\n\n## Consecutive rows, before window functions\n\nA self-join can find the previous row per group:\n\n```sql\nSELECT p.id, p.paid_on, prev.paid_on AS previous_payment\nFROM payments p\nLEFT JOIN payments prev\n  ON prev.student_id = p.student_id\n AND prev.paid_on = (\n       SELECT max(paid_on) FROM payments x\n       WHERE x.student_id = p.student_id AND x.paid_on < p.paid_on\n     );\n```\n\nIt works, and it is horrible. Read it twice and you can see why: the join condition contains a correlated subquery that runs per row. The window-function module replaces this whole construction with `lag(paid_on) OVER (PARTITION BY student_id ORDER BY paid_on)`, which is one line, faster, and obvious. Know the self-join version because you will meet it in old code and because it shows what the window function is doing underneath; do not write new code this way.\n\n## Walking a hierarchy of unknown depth\n\nA plain self-join goes one level. Two joins go two levels. For a tree of unknown depth you need a recursive CTE:\n\n```sql\nWITH RECURSIVE chain AS (\n  SELECT id, full_name, manager_id, 1 AS depth\n  FROM employees WHERE id = 42            -- the anchor: where to start\n  UNION ALL\n  SELECT e.id, e.full_name, e.manager_id, c.depth + 1\n  FROM employees e\n  JOIN chain c ON e.manager_id = c.id     -- the step: one level down\n)\nSELECT * FROM chain;\n```\n\nTwo halves: a starting row, and a rule for getting from rows you have to rows you do not. The database applies the rule repeatedly until it produces nothing new.\n\nThe honest warning: if the data contains a cycle (someone is transitively their own manager, which happens in real HR systems after a reorganisation) this runs until it exhausts memory. Guard it with a depth limit (`WHERE c.depth < 20`) or by carrying the path travelled so far and refusing to revisit a node. Add the guard when you write it, not after the incident.\n\n## Performance, briefly\n\nA self-join reads the table twice, and an index on the joined column is doing double duty. `employees(manager_id)` is the index that makes the org-chart query fast. Without it, a table of 50,000 employees means 50,000 lookups against an unindexed column: a quadratic amount of work, which is why these queries have a reputation for suddenly becoming slow when a company grows.\n\n## Naming\n\nAlias every self-join with something meaningful. `e` and `m` for employee and manager. `a` and `b` for a symmetric pair. Never `t1` and `t2`, because six months later nobody, including you, will remember which side was which, and in a self-join the two sides are indistinguishable except by their names."},"check":{"question":"To find students who share a phone number in an 800-row table, you write `students a JOIN students b ON a.phone = b.phone`, expecting about six pairs, and get 812 rows. What produced that count?","options":[{"text":"The join is matching NULL phone numbers to each other, adding a row for every pair of students with no number recorded on file.","correct":false,"tempting":"NULL never equals NULL, so students with no phone number match nothing and drop out of an inner join entirely."},{"text":"Every student matches themselves, giving 800 rows, and each of the 6 real pairs is returned twice, once in each direction.","correct":true},{"text":"A self-join always produces the square of the table size unless a LIMIT is applied.","correct":false,"tempting":"That would be a cross join; this join does have a condition, so only rows with equal phone numbers are paired."},{"text":"The phone column has trailing whitespace on some rows, creating spurious matches.","correct":false,"tempting":"Whitespace differences would prevent matches rather than create them, and would reduce the count rather than inflate it."}]},"prev":{"slug":"the-fan-trap","title":"Two child tables, one very wrong total","url":"https://addaly.com/learn/data-and-sql/the-fan-trap"},"next":{"slug":"set-operations","title":"UNION, INTERSECT and EXCEPT","url":"https://addaly.com/learn/data-and-sql/set-operations"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Joining a table to itself', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/self-joins (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Joining a table to itself","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"A self-join is the same table under two names; add `a.id < b.id` so that no row matches itself and no pair appears twice.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-same-table-twice-with-two-names","runs":[{"text":"The same table, twice, with two names"}]},{"t":"p","runs":[{"text":"A join does not care whether the two sides are different tables. When the relationship you want is between rows of the "},{"text":"same","i":true},{"text":" table, you join it to itself and give each copy an alias:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT e.full_name AS employee, m.full_name AS manager\nFROM employees e\nLEFT JOIN employees m ON m.id = e.manager_id;"},{"t":"p","runs":[{"text":"e","code":true},{"text":" and "},{"text":"m","code":true},{"text":" are the same table. The database does not find this strange. The LEFT JOIN matters: the chief executive has no manager, and an inner join would remove the one person the org chart is rooted on."}]},{"t":"h2","id":"three-things-self-joins-are-actually-for","runs":[{"text":"Three things self-joins are actually for"}]},{"t":"p","runs":[{"text":"Hierarchies.","b":true},{"text":" Manager and report, category and parent category, comment and the comment it replies to, region within country. Any table with a column pointing at its own primary key."}]},{"t":"p","runs":[{"text":"Comparing a row to another row of the same kind.","b":true},{"text":" Two payments by the same student, this month’s figure against last month’s, a price against the price of the previous version."}]},{"t":"p","runs":[{"text":"Finding pairs.","b":true},{"text":" Which two products are bought together; which two students share a phone number."}]},{"t":"p","runs":[{"text":"For that last one, the essential detail:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT a.id, b.id, a.phone\nFROM students a\nJOIN students b ON b.phone = a.phone AND b.id > a.id;"},{"t":"p","runs":[{"text":"b.id > a.id","code":true},{"text":" does two jobs at once. It stops each student matching themselves, and it returns each pair once rather than twice in both orders. Without it you get every row paired with itself plus every genuine pair duplicated, which is the classic wrong answer to “how many duplicates do we have”."}]},{"t":"h2","id":"consecutive-rows-before-window-functions","runs":[{"text":"Consecutive rows, before window functions"}]},{"t":"p","runs":[{"text":"A self-join can find the previous row per group:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT p.id, p.paid_on, prev.paid_on AS previous_payment\nFROM payments p\nLEFT JOIN payments prev\n  ON prev.student_id = p.student_id\n AND prev.paid_on = (\n       SELECT max(paid_on) FROM payments x\n       WHERE x.student_id = p.student_id AND x.paid_on < p.paid_on\n     );"},{"t":"p","runs":[{"text":"It works, and it is horrible. Read it twice and you can see why: the join condition contains a correlated subquery that runs per row. The window-function module replaces this whole construction with "},{"text":"lag(paid_on) OVER (PARTITION BY student_id ORDER BY paid_on)","code":true},{"text":", which is one line, faster, and obvious. Know the self-join version because you will meet it in old code and because it shows what the window function is doing underneath; do not write new code this way."}]},{"t":"h2","id":"walking-a-hierarchy-of-unknown-depth","runs":[{"text":"Walking a hierarchy of unknown depth"}]},{"t":"p","runs":[{"text":"A plain self-join goes one level. Two joins go two levels. For a tree of unknown depth you need a recursive CTE:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH RECURSIVE chain AS (\n  SELECT id, full_name, manager_id, 1 AS depth\n  FROM employees WHERE id = 42            -- the anchor: where to start\n  UNION ALL\n  SELECT e.id, e.full_name, e.manager_id, c.depth + 1\n  FROM employees e\n  JOIN chain c ON e.manager_id = c.id     -- the step: one level down\n)\nSELECT * FROM chain;"},{"t":"p","runs":[{"text":"Two halves: a starting row, and a rule for getting from rows you have to rows you do not. The database applies the rule repeatedly until it produces nothing new."}]},{"t":"p","runs":[{"text":"The honest warning: if the data contains a cycle (someone is transitively their own manager, which happens in real HR systems after a reorganisation) this runs until it exhausts memory. Guard it with a depth limit ("},{"text":"WHERE c.depth < 20","code":true},{"text":") or by carrying the path travelled so far and refusing to revisit a node. Add the guard when you write it, not after the incident."}]},{"t":"h2","id":"performance-briefly","runs":[{"text":"Performance, briefly"}]},{"t":"p","runs":[{"text":"A self-join reads the table twice, and an index on the joined column is doing double duty. "},{"text":"employees(manager_id)","code":true},{"text":" is the index that makes the org-chart query fast. Without it, a table of 50,000 employees means 50,000 lookups against an unindexed column: a quadratic amount of work, which is why these queries have a reputation for suddenly becoming slow when a company grows."}]},{"t":"h2","id":"naming","runs":[{"text":"Naming"}]},{"t":"p","runs":[{"text":"Alias every self-join with something meaningful. "},{"text":"e","code":true},{"text":" and "},{"text":"m","code":true},{"text":" for employee and manager. "},{"text":"a","code":true},{"text":" and "},{"text":"b","code":true},{"text":" for a symmetric pair. Never "},{"text":"t1","code":true},{"text":" and "},{"text":"t2","code":true},{"text":", because six months later nobody, including you, will remember which side was which, and in a self-join the two sides are indistinguishable except by their names."}]}],"check":{"question":[{"text":"To find students who share a phone number in an 800-row table, you write "},{"text":"students a JOIN students b ON a.phone = b.phone","code":true},{"text":", expecting about six pairs, and get 812 rows. What produced that count?"}],"options":[{"text":[{"text":"The join is matching NULL phone numbers to each other, adding a row for every pair of students with no number recorded on file."}],"correct":false,"tempting":[{"text":"NULL never equals NULL, so students with no phone number match nothing and drop out of an inner join entirely."}]},{"text":[{"text":"Every student matches themselves, giving 800 rows, and each of the 6 real pairs is returned twice, once in each direction."}],"correct":true,"tempting":null},{"text":[{"text":"A self-join always produces the square of the table size unless a LIMIT is applied."}],"correct":false,"tempting":[{"text":"That would be a cross join; this join does have a condition, so only rows with equal phone numbers are paired."}]},{"text":[{"text":"The phone column has trailing whitespace on some rows, creating spurious matches."}],"correct":false,"tempting":[{"text":"Whitespace differences would prevent matches rather than create them, and would reduce the count rather than inflate it."}]}],"order":[3,1,0,2],"why":null},"prev":{"slug":"the-fan-trap","title":"Two child tables, one very wrong total","minutes":9},"next":{"slug":"set-operations","title":"UNION, INTERSECT and EXCEPT","minutes":7}}}