Self joins for hierarchical data

Concepts covered: Self-Join

An employees table stores each person's manager as a manager_id that points back into the same table. Asking for each employee's manager's name is therefore a join between employees and employees. The database does not care that both sides are the same table: it treats each alias as an independent copy, and the row pairing model from the beginner lesson applies unchanged.

SELECT e.name AS employee, m.name AS manager
FROM employee_metrics e
LEFT JOIN employee_metrics m
  ON e.manager_id = m.emp_id

employee_metrics
emp_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1

The alias e stands for the employee side and m for the manager side, and the ON clause reads: the employee's manager_id equals the manager's emp_id. Bob's manager_id of 1 finds Alice's emp_id of 1, and so does Carol's. Alice has a NULL manager_id, and NULL equals nothing, so her comparison with every m row is NULL rather than true. The LEFT JOIN keeps her with a NULL manager; an INNER JOIN would drop her, and the report would be missing the person at the top of the company.

Climbing 2 levels

Each extra level is another alias and another join. The skip level manager is the manager's manager, so join m to a third copy g on m.manager_id = g.emp_id. The 6 person company below has Alice at the top, Bob and Carol reporting to her, Dave and Erin under Bob, and Frank under Carol.

SELECT
  e.name AS employee,
  m.name AS manager,
  g.name AS skip_level
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.emp_id
LEFT JOIN employees g
  ON m.manager_id = g.emp_id
ORDER BY e.emp_id

employees
emp_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2
5	Erin	2
6	Frank	3

Result
employee	manager	skip_level
Alice	NULL	NULL
Bob	Alice	NULL
Carol	Alice	NULL
Dave	Bob	Alice
Erin	Bob	Alice
Frank	Carol	Alice

Dave, Erin and Frank each show both levels: Dave's manager is Bob and his skip level is Alice. Bob and Carol show Alice as manager and NULL as skip level, because Alice's manager_id is NULL and the second LEFT JOIN finds nothing to pair it with. Alice shows NULL in both columns. Chaining aliases works for a known number of levels; a tree whose depth nobody has counted needs the recursive CTE in the next section.

The 6 person employees table is joined to itself with JOIN instead of LEFT JOIN on e.manager_id = m.emp_id. How many rows does SELECT e.name, m.name return?

5 rows, because Alice's NULL manager_id matches no emp_id and INNER JOIN drops her
6 rows, because every employee is on the left side and the left side is preserved
4 rows, because Alice is dropped along with the employees who report to Alice
6 rows, because NULL = 1 is treated as false and the pair is kept with a NULL manager

Counting in the other direction

Flip the roles and the same join counts direct reports. Start from the manager copy, LEFT JOIN the employee copy on the same condition, and group by the manager. LEFT JOIN keeps managers with no reports, and COUNT of a column from the employee side counts only matched rows, so those managers show 0 instead of vanishing.

SELECT
  m.name AS manager,
  COUNT(e.emp_id) AS direct_reports
FROM employees m
LEFT JOIN employees e
  ON e.manager_id = m.emp_id
GROUP BY m.name
ORDER BY direct_reports DESC, m.name

employees
emp_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2
5	Erin	2
6	Frank	3

Result
manager	direct_reports
Alice	2
Bob	2
Carol	1
Dave	0
Erin	0
Frank	0

Alice and Bob manage 2 people each, Carol manages 1, and the 3 individual contributors show 0 because their only pair carries NULLs on the e side. With COUNT(*) in place of COUNT(e.emp_id), each of those 3 would show 1, because COUNT(*) counts the NULL filled row itself.

About this lesson

This section is part of the Joins: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.