AI Hints
Homeโ€บSQL Tutorialโ€บMore Kinds of Join
SQL ยท Lesson 51 of 126

Self Joins

A table that joins itself.

โฑ 2 min readโ–ถ Video at 1:12:16๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Self Joins
From the video: Self Joins
  • 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.

query.sqlsql
SELECT staff_id, name, role, manager_id
FROM staff;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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
Next up: RIGHT, FULL, and CROSS Joins โ€” the rest of the family