Staff Without a Current Supervisor

A clinic records each employee's supervisor as another row in the same table. When someone leaves, their row is deleted, but the people they supervised still point to the old id.

Find staff who earn less than 40.00 per hour and whose supervisor no longer exists in the table. Staff with no supervisor at all (supervisor_id is NULL) are not orphaned and must not be returned.

Return one column, staff_id, sorted in ascending order. Return no rows if nobody matches.

Tables

clinic_staff — One row per current employee; rows of people who left are deleted.

Example

clinic_staff

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Subqueries & CTEs · problem 27 of 70 · SQLite

The idea

The question is an anti-join: keep rows whose supervisor_id has no match among the staff_id values. supervisor_id NOT IN (SELECT staff_id FROM clinic_staff) says exactly that, and the pay filter hourly_rate < 40 is an ordinary AND.

NULLs need care. For the director, NULL NOT IN (...) is unknown rather than true, so WHERE drops the row. That is the behaviour we want here. (The usual NOT IN trap is a NULL inside the list. It cannot happen here because staff_id is a primary key.)

The LEFT JOIN ... IS NULL alternative has the opposite risk. A NULL supervisor also fails to match, so the condition supervisor_id IS NOT NULL has to be written explicitly. Finally, ORDER BY staff_id is required. Without it SQLite returns rows in whatever order it reads them.

Solution

SELECT staff_id
FROM clinic_staff
WHERE hourly_rate < 40
  AND supervisor_id NOT IN (SELECT staff_id FROM clinic_staff)
ORDER BY staff_id;

Another way

SELECT s.staff_id
FROM clinic_staff AS s
LEFT JOIN clinic_staff AS boss
  ON boss.staff_id = s.supervisor_id
WHERE s.hourly_rate < 40
  AND s.supervisor_id IS NOT NULL
  AND boss.staff_id IS NULL
ORDER BY s.staff_id;

Why common answers fail

A NULL supervisor also finds no match in the LEFT JOIN, so people with no supervisor at all are wrongly reported as orphaned.

SELECT s.staff_id
FROM clinic_staff AS s
LEFT JOIN clinic_staff AS boss ON boss.staff_id = s.supervisor_id
WHERE s.hourly_rate < 40
  AND boss.staff_id IS NULL
ORDER BY s.staff_id;

Uses <= 40, so someone earning exactly 40.00 is included.

SELECT staff_id
FROM clinic_staff
WHERE hourly_rate <= 40
  AND supervisor_id NOT IN (SELECT staff_id FROM clinic_staff)
ORDER BY staff_id;

Puts the pay filter inside the subquery, so it filters the supervisors instead of the staff, and anyone whose supervisor earns 40 or more looks orphaned.

SELECT staff_id
FROM clinic_staff
WHERE supervisor_id NOT IN (
  SELECT staff_id FROM clinic_staff WHERE hourly_rate < 40
)
ORDER BY staff_id;

What the interviewer is checking

Checks that you can write an anti-join and reason about NULL on both sides of it: in the column you test and in the list you test against.

Mistakes to avoid

• Treating a NULL supervisor as a missing supervisor in a LEFT JOIN anti-join.
• Writing NOT IN against a column that can contain NULL (here it cannot, but you should check).
• Putting the pay filter inside the subquery, where it filters supervisors instead of staff.

Follow-ups to expect

• Reassign the orphans to the director (UPDATE ... SET supervisor_id = (SELECT staff_id ... WHERE supervisor_id IS NULL)).
• How would you prevent orphans in the first place? (A FOREIGN KEY with ON DELETE SET NULL.)
• Count orphans per missing supervisor id (GROUP BY supervisor_id on the same filter).

In real work

Data-quality checks for dangling foreign keys after deletes or migrations, such as orders pointing at removed customers or tickets assigned to deactivated users.

Practice next