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

Pair the pointer with the key

Level 2: Fill the blank · Exercise 1 of 1

Employee and supervisor first names, side by side, every employee kept. One piece of the ON clause is missing.

Finish line: 14 rows · columns employee, supervisor · in the order the question gives

Data: Twin Ports Tool Library.

I would like help with this exercise

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

Blank 1 of 1: the employee's column that points at a supervisor

Open in the playground

Query results

Check to see the result here.

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