Hostel Students Who Never Ordered From the Canteen — SQL Easy Problem

Problem statement

The college canteen takes orders through its app. An order placed by a hostel student carries the student's roll number; walk-in guests pay at the counter, and their orders are stored with roll_no NULL.

Return every student who has never placed an order, with the columns roll_no and name. Guest orders belong to no student and change nothing for anyone. Return the rows in any order.

Tables

Table: Student

Primary key: roll_no.

One row per student living in a college hostel.

Table: CanteenOrder

Primary key: order_id.

roll_no is the student who ordered, or NULL for a walk-in guest. A non-NULL roll_no is always in Student.

Examples

Example 1

Student

CanteenOrder

Output

How to solve Hostel Students Who Never Ordered From the Canteen

This is an anti join: keep the rows of one table that have no partner in another. The most common way to write it is a LEFT JOIN from Student to CanteenOrder on the roll number. A student with orders appears once per order; a student without any appears exactly once, with NULL in every column of CanteenOrder. Filtering on o.order_id IS NULL keeps only those — the primary key is never NULL on a real order, so a NULL there can only mean "no match".

NOT EXISTS says the same thing directly: keep the student if no order carries their roll number. It stops at the first matching order and is usually what an optimiser turns the LEFT JOIN into anyway.

The trap is NOT IN. Guest orders have roll_no NULL, and 101 NOT IN (102, 104, NULL) is not true but unknown — SQL cannot tell whether 101 equals the missing value — so the filter drops every student and the answer comes back empty. Either filter the NULLs out of the subquery, as the third query does, or use one of the forms above, which never compare against NULL. With an index on CanteenOrder.roll_no each form is one lookup per student.

Reference solution (MySQL)

SELECT s.roll_no, s.name
FROM Student s
LEFT JOIN CanteenOrder o ON o.roll_no = s.roll_no
WHERE o.order_id IS NULL

Another way

SELECT roll_no, name FROM Student s WHERE NOT EXISTS (SELECT 1 FROM CanteenOrder o WHERE o.roll_no = s.roll_no)

Another way

SELECT roll_no, name FROM Student WHERE roll_no NOT IN (SELECT roll_no FROM CanteenOrder WHERE roll_no IS NOT NULL)