SQL Tutorial · Joins and Subqueries · Lesson 22 of 36
SQL Self Join
Join a table to itself — for example employees and their managers.
Joining a table to itself
When rows reference other rows in the same table, join the table to itself with two aliases. Each employee's manager_id points to another employee:
SELECT e.name AS employee, e.title, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;
Output
employee | title | manager Sofia Reyes | CEO | NULL Omar Haddad | CTO | Sofia Reyes Mei Chen | Engineering Manager | Omar Haddad Jonas Berg | Senior Engineer | Mei Chen Priya Nair | Engineer | Mei Chen Lucas Martin | Engineer | Mei Chen Hannah Okafor | Head of Sales | Sofia Reyes Diego Silva | Account Executive | Hannah Okafor Emma Novak | Account Executive | Hannah Okafor Ravi Kumar | Support Specialist | Sofia Reyes
The LEFT JOIN keeps the CEO, who has no manager.
Counting direct reports
SELECT m.name AS manager, COUNT(e.employee_id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY direct_reports DESC;
Output
manager | direct_reports Sofia Reyes | 3 Mei Chen | 3 Hannah Okafor | 2 Omar Haddad | 1
For whole hierarchies (managers of managers…), use a recursive CTE.
Test yourself
What do you need to join a table to itself?
A SELF keywordTwo different aliases for the same tableA temporary copyIt isn't possible
Show answer
Answer: Two different aliases for the same table