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 a coupon code, write coupon_code <> 'SPRING10', and quietly 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'

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_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 byorder_id. Run the starter query first to see how many rows the naive <> filter misses.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer