Self Joins
A table that joins itself.
Look at Helen's staff table. Every person has a manager ID. And that manager is also in the staff table. So to show each person next to their manager's name, the staff table has to join to itself. That sounds strange, but it's easy with aliases.

- Every person has a manager_id
- The manager is staff too
- Join the table to itself
The staff table
Here's the whole staff table. Run it. Marcus Lee has manager ID one. Staff number one is Helen Ward, the owner. Helen has no manager, so her manager ID is empty.
SELECT staff_id, name, role, manager_id
FROM staff;โโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ staff_id โ name โ role โ manager_id โ โโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโค โ 1 โ Helen Ward โ Owner โ โ โ 2 โ Marcus Lee โ Store Manager โ 1 โ โ 3 โ Priya Nair โ Online Manager โ 1 โ โ 4 โ Tom Baker โ Bookseller โ 2 โ โ 5 โ Sara Odum โ Bookseller โ 2 โ โ 6 โ Leo Grant โ Warehouse โ 3 โ โ 7 โ Amy Chen โ Web Support โ 3 โ โ 8 โ Noah Price โ Bookseller โ 2 โ โโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโ
A self join
We use the staff table twice, with two different aliases. S is the employee, and M is the manager. Then we match the employee's manager ID, to the manager's staff ID. We use a left join, so Helen stays in the list. Run it. Every employee, with their manager's name.
SELECT s.name AS employee,
s.role,
m.name AS manager
FROM staff AS s
LEFT JOIN staff AS m ON m.staff_id = s.manager_id;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ employee โ role โ manager โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโค โ Helen Ward โ Owner โ โ โ Marcus Lee โ Store Manager โ Helen Ward โ โ Priya Nair โ Online Manager โ Helen Ward โ โ Tom Baker โ Bookseller โ Marcus Lee โ โ Sara Odum โ Bookseller โ Marcus Lee โ โ Leo Grant โ Warehouse โ Priya Nair โ โ Amy Chen โ Web Support โ Priya Nair โ โ Noah Price โ Bookseller โ Marcus Lee โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโ
Team sizes
Now we can group by the manager, and count their team. Run it. Marcus Lee manages three booksellers. Helen and Priya each manage two people.
SELECT m.name AS manager,
COUNT(*) AS team_size
FROM staff AS s
JOIN staff AS m ON m.staff_id = s.manager_id
GROUP BY m.staff_id;โโโโโโโโโโโโโโฌโโโโโโโโโโโโ โ manager โ team_size โ โโโโโโโโโโโโโโผโโโโโโโโโโโโค โ Helen Ward โ 2 โ โ Marcus Lee โ 3 โ โ Priya Nair โ 2 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโ
Summary
A self join is just a normal join, where both tables happen to be the same, with different aliases. It's perfect for managers, referrals, and family trees.
Key takeaways- Same table, two aliases
- Employee and manager
- Use LEFT to keep the top boss