Practise · The staff chart loses its top row

Opening your saved work…

The reading

A table meets itself

Nothing says the two sides of a join must be different tables. The staff table has a supervisor_id that points at another row of staff. Give the table two aliases and it becomes two copies: e for the employee, m for the manager that e.supervisor_id points at. The aliases name roles, not two tables stored twice.

The bridge is ON m.id = e.supervisor_id. Write ON m.id = e.id instead and every person becomes their own supervisor, fourteen rows of nonsense that run without a complaint.

SELECT e.id AS employee_id, e.first_name AS employee, m.first_name AS supervisor
FROM staff e
LEFT JOIN staff m ON m.id = e.supervisor_id
ORDER BY e.id;

Fourteen rows. Marit, at the top, has no supervisor and is still on the chart. Open in the playground

One row of staff has a NULL supervisor_id: the director, at the top of the chart. An INNER JOIN drops her, because NULL matches nothing. If the chart must show every employee, the employee copy goes on the left of a LEFT JOIN and the director keeps her row with a NULL where a supervisor's name would be.

Rule of thumb. In a self-join the aliases are roles. Pair the pointing column with the key it points at, and keep the top row with a LEFT JOIN.

Going deeper (optional, not on the exam)

The same trick answers questions inside one table of facts: who ran the marathon in both 2024 and 2025, and did they get faster? Pair a results row with another results row of the same runner, one alias per year.

The staff chart loses its top row · one table, two roles · level 1 of 6

Who reports to whom

Level 1: Watch it answered · Exercise 1 of 1

Every staff member with their supervisor's id, including the director who has none. Watch the self-join, then run it.

Data: Twin Ports Tool Library.

Why it works, clause by clause

FROM staff e: the employee copy of the table.
LEFT JOIN staff m ON m.id = e.supervisor_id: the manager copy, paired where its id equals the employee's supervisor_id. LEFT, so the director with a NULL supervisor_id keeps her row.
The two id columns on the right agree on every matched row and are NULL together on the director's row, which proves the pairing.

From the reading: A table meets itself, paragraph 1

I would like help with this exercise

SELECT e.id AS employee_id, e.supervisor_id, m.id AS matched_supervisor
FROM staff e
LEFT JOIN staff m ON m.id = e.supervisor_id
ORDER BY e.id;

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.