Self JOIN
A self join joins a table to itself, using aliases to treat it as two tables.
In this page:
Syntax
SELECT a.column, b.column
FROM table_name a
JOIN table_name b ON a.foreign_key = b.id;Self JOIN
The classic use is an employee table with a manager_id column pointing to another row in the same table.
Give the table two aliases and join them. Use LEFT JOIN to keep employees without a manager, such as the CEO.
Recursive queries handle multi-level hierarchies.
Note:
Different aliases for the same table are what make a self join possible.
Example: Self JOIN
CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER);
INSERT INTO staff VALUES (1, 'Ada', NULL), (2, 'Bob', 1), (3, 'Cy', 1), (4, 'Di', 2);
SELECT e.name AS employee, m.name AS manager FROM staff e LEFT JOIN staff m ON e.manager_id = m.id ORDER BY e.id;
-- Output:
-- employee | manager
-- Ada | NULL
-- Bob | Ada
-- Cy | Ada
-- Di | BobLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Forgetting aliases
- Using INNER JOIN and losing the top of the hierarchy
- Confusing the two roles of the table
Chapter Summary
- Join a table to itself
- Use two aliases
- LEFT JOIN keeps top-level rows
- Recursive CTEs do deeper hierarchies
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: