SELF JOIN for Hierarchies
Joining a table to itself to model employee-manager and parent-child relationships.
What a SELF JOIN Really Is
A self join is simply a join where a table appears on both sides. There is no special SELF JOIN keyword; you write a normal INNER or LEFT JOIN and reference the same table twice.
The trick that makes it work is table aliases. You give each copy a different alias so the engine treats them as two independent tables.
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;Why Aliases Are Mandatory
Without distinct aliases the query is ambiguous: every column name appears twice and the engine cannot tell which copy you mean. Aliasing each instance solves this.
Read the join as 'pair each employee row with the employee row that is its manager.' The alias e is the worker, m is the manager, and both come from the same physical table.
-- e = the employee, m = that employee's manager
SELECT e.id, e.name, m.name AS reports_to
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;All lessons in this course
- CROSS JOIN and Cartesian Products
- SELF JOIN for Hierarchies
- Comparing Rows Within One Table
- Choosing the Right Join Type