sql / intermediate
Snippet
Self-Joins for Hierarchical Relationships
A self-join is a regular join where a table is joined with itself. This is primarily used to query hierarchical data, such as employees and their managers stored in the same table.
snippet.sql
1
2
3
4
5
SELECTe.name AS employee,m.name AS managerFROM staff eJOIN staff m ON e.manager_id = m.staff_id;
Breakdown
1
FROM staff e
Defines the table 'staff' with the alias 'e' representing the subordinate.
2
JOIN staff m
Joins the same table 'staff' again with the alias 'm' representing the manager.
3
ON e.manager_id = m.staff_id
The join condition that links the employee's manager reference to the manager's actual ID.