Canteen Orders Not Placed With the FEST50 Coupon — SQL Easy Problem

Problem statement

The college canteen is withdrawing its FEST50 coupon and wants to see which orders the change would not have touched.

Return order_id, student_name and coupon_code for every order that was not placed with the coupon FEST50. An order with no coupon at all (coupon_code is NULL) counts as not using it and must be in the result. Return the rows in any order.

Tables

Table: CanteenOrder

Primary key: order_id.

One row per order. coupon_code is the coupon applied to the order (always in capitals), or NULL when the student paid full price.

Examples

Example 1

CanteenOrder

Output

How to solve Canteen Orders Not Placed With the FEST50 Coupon

The obvious filter, coupon_code <> 'FEST50', returns the orders with other coupons but silently loses every order with no coupon. In SQL a comparison with NULL is neither true nor false but unknown, and WHERE keeps only the rows where the condition is true — so NULL <> 'FEST50' drops the row exactly as NULL = 'FEST50' would.

The fix is to state what should happen to NULL: coupon_code <> 'FEST50' OR coupon_code IS NULL. IS NULL is the one test that is true for a missing value, and the OR brings those rows back.

Other spellings of the same idea:

• replace NULL with a value that can never be the coupon before comparing: IFNULL(coupon_code, '') <> 'FEST50';
• use MySQL's NULL-safe equality <=>, which treats NULL as an ordinary value (NULL <=> 'FEST50' is 0, not NULL), and negate it;
• turn the question around and keep every order whose id is NOT IN the list of FEST50 orders. That list holds ids, never NULL, so NOT IN is safe here — a NOT IN over a list containing a NULL would return nothing at all.

Each version reads the table once (the NOT IN one twice), so all are linear in the number of orders.

Reference solution (MySQL)

SELECT order_id, student_name, coupon_code
FROM CanteenOrder
WHERE coupon_code <> 'FEST50' OR coupon_code IS NULL

Another way

SELECT order_id, student_name, coupon_code FROM CanteenOrder WHERE IFNULL(coupon_code, '') <> 'FEST50'

Another way

SELECT order_id, student_name, coupon_code FROM CanteenOrder WHERE NOT (coupon_code <=> 'FEST50')

Another way

SELECT order_id, student_name, coupon_code FROM CanteenOrder WHERE order_id NOT IN (SELECT order_id FROM CanteenOrder WHERE coupon_code = 'FEST50')