Python SQL SELF JOIN: Hierarchies, Trees & Parent-Child Relationships
A `SELF JOIN` is a regular join in which a table is joined with ITSELF. It is used to query hierarchical data stored in a single table, such as organizational charts (employee -> manager), category subtrees (parent category -> subcategory), or graph network edges.
"A self-join is like looking up family relationships in a single genealogy register: you find a person's entry, note their parent's ID number, and look up that same parent's name in another row of the same book."
Deep Dive: How It Works
Mandatory Table Aliases: Because both sides of the join reference the same table name, distinct aliases (e.g. `emp` and `mgr`) are strictly required to resolve column ambiguity.
Hierarchical Adjacency List: The `manager_id` column stores the primary key `id` of another row in the same `employees` table.
Preserving Top of Hierarchy (CEO): Always use `LEFT JOIN` so the CEO/Root (who has `manager_id IS NULL`) is not dropped from results.
Syntax Blueprint
SELECT e.name AS employee, m.name AS manager FROM employees AS e LEFT JOIN employees AS m ON e.manager_id = m.id;
Join the table to itself using two distinct aliases (e for employee, m for manager).
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Using `INNER JOIN` in a self-join and wondering why the CEO / Top Manager disappeared.Why it happens: The CEO has `manager_id = NULL`, which fails the INNER JOIN equality condition.
How to fix: Use `LEFT JOIN` on the manager relationship.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Self-Join Category Sub-trees
- Select `c.name AS subcategory`, `p.name AS parent_category` from `categories c LEFT JOIN categories p ON c.parent_id = p.id` ordered by `c.id`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.