Limits of Self Joins
When you need recursion instead.
What Is a Self Join?
A self join is when a table is joined to itself. It is useful for comparing rows within the same table, such as finding employees and their managers stored in a single employees table.
Before exploring its limits, let us remind ourselves how a basic self join works in practice.
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;One Level Deep
A self join handles one hop in a hierarchy elegantly. If you want each employee paired with their direct manager, one self join is all you need.
This works perfectly when your data is only one level deep or when you only care about direct parent-child relationships.
SELECT child.name AS employee, parent.name AS direct_manager
FROM employees child
LEFT JOIN employees parent ON child.manager_id = parent.id;All lessons in this course
- What Is a Self Join
- Employees and Managers
- Comparing Rows in the Same Table
- Limits of Self Joins