Canteen Staff Whose Supervisor Has Left — SQL Easy Problem

Problem statement

The college canteen keeps one row per current staff member. When someone leaves, their row is deleted — but the people they supervised still carry the old supervisor_id. The canteen manager wants to reassign the lower-paid ones among them first.

Return the staff_id and full_name of every staff member who earns less than 25000 a month and whose supervisor_id names a person who is no longer in the table. Staff with no supervisor (supervisor_id NULL) are not in the answer, and pay of exactly 25000 is not "less". Order the rows by staff_id.

Tables

Table: CanteenStaff

Primary key: staff_id.

supervisor_id is the staff_id of the person's supervisor at the time they were assigned, or NULL for someone with no supervisor. It may name a person who has since left.

Examples

Example 1

CanteenStaff

Output

How to solve Canteen Staff Whose Supervisor Has Left

The question has two parts: a plain filter on the row itself (monthly_pay < 25000) and a question about other rows — is there still someone whose staff_id equals my supervisor_id? That second part is an anti join, and a subquery states it most directly: supervisor_id NOT IN (SELECT staff_id FROM CanteenStaff).

NULLs need a moment's thought. For someone with no supervisor the test is NULL NOT IN (…), which is NULL rather than true, so WHERE drops the row — exactly what the statement asks. The opposite trap (a NULL inside the list making every NOT IN unknown) cannot happen here, because staff_id is the primary key and is never NULL. Pay of exactly 25000 fails the strict <.

Two other shapes give the same answer. A LEFT JOIN from each person to their supervisor's row keeps the person even when no supervisor row matches, and s.staff_id IS NULL then picks the unmatched ones — but here you must exclude NULL supervisors yourself. NOT EXISTS with a correlated subquery reads the same way and is NULL-safe in either direction. With an index on the primary key, each check is one lookup, so all three are linear in the table size.

Reference solution (MySQL)

SELECT staff_id, full_name
FROM CanteenStaff
WHERE monthly_pay < 25000
  AND supervisor_id NOT IN (SELECT staff_id FROM CanteenStaff)
ORDER BY staff_id

Another way

SELECT c.staff_id, c.full_name
FROM CanteenStaff c
LEFT JOIN CanteenStaff s ON s.staff_id = c.supervisor_id
WHERE c.monthly_pay < 25000 AND c.supervisor_id IS NOT NULL AND s.staff_id IS NULL
ORDER BY c.staff_id

Another way

SELECT c.staff_id, c.full_name
FROM CanteenStaff c
WHERE c.monthly_pay < 25000 AND c.supervisor_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM CanteenStaff s WHERE s.staff_id = c.supervisor_id)
ORDER BY c.staff_id