Canteen Staff Whose Supervisor Has Left

Easy
25.

Canteen Staff Whose Supervisor Has Left

EasySubqueries

MySQL

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

ColumnType
staff_idint
full_namevarchar
monthly_payint
supervisor_idint

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.

Example

The rows must come back in the order shown.

CanteenStaff

staff_idfull_namemonthly_paysupervisor_id
1Ramesh32000NULL
3Lakshmi240001
4Imran180002
6Geeta250002
7Joseph210005
8Kavya19000NULL
9Suresh300005

Output

staff_idfull_name
4Imran
7Joseph
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Canteen Staff Whose Supervisor Has Left — SQL Problem

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…

  • Difficulty: Easy
  • Topics: Subqueries
  • Dialect: MySQL
  • Problem: #25

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

ColumnType
staff_idint
full_namevarchar
monthly_payint
supervisor_idint

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

staff_idfull_namemonthly_paysupervisor_id
1Ramesh32000NULL
3Lakshmi240001
4Imran180002
6Geeta250002
7Joseph210005
8Kavya19000NULL
9Suresh300005

Output

staff_idfull_name
4Imran
7Joseph

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

← Median Delivery Time of Each Restaurant · Second-Best Innings in the College Cup →