Lesson 7.4

Null-Safe Filtering in SQL

Comparisons against null return null, not true or false, so ordinary filters silently lose rows.

Here's a trap that produces wrong answers with no error. You ask for orders that didn't use the SPRING10 coupon, write coupon_code <> 'SPRING10', and lose every order that had no coupon at all.

The reason: comparing anything to NULL returns NULL, which a WHERE treats as "not true" and drops. IS DISTINCT FROM compares two values treating null as an ordinary value, so the null rows stay.

Pattern

-- drops null rows unexpectedly
where plan <> 'pro'

-- null-safe: keeps null rows
where plan is distinct from 'pro'

Syntax reference

Need syntax details? See the SQL NULL handling reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Find every order that did not use the coupon 'SPRING10', including orders with no coupon at all. Return order_id and coupon_code, sorted by order_id. Run the starter query first to see how many rows the naive <> filter misses.