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 3 of 6

How many rows with INNER?

Level 3: Predict the result · Exercise 1 of 1

Fourteen staff members; one has a NULL supervisor_id. Before you run it, how many rows does an INNER self-join on m.id = e.supervisor_id return?

SELECT COUNT(*) AS rows_returned
FROM staff e
JOIN staff m ON m.id = e.supervisor_id;

The query. Predict before you run it.

Data: Twin Ports Tool Library.

I would like help with this exercise

Opens once your prediction is right, or after the worked answer.

Query results

The real result appears here after you run it.

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