Lesson 2.6

Missing Values with SQL NULL

NULL marks missing or unknown data, so it needs special comparison syntax.

NULL means a value is missing or unknown. It is not the same as zero, an empty string, or the word "null".

Because NULL means unknown, equality does not work the way it does for ordinary values. UseIS NULL to find missing values and IS NOT NULL to keep rows where a value is present.

Pattern

select id_column, nullable_column
from table_name
where nullable_column is null
  and status_column <> 'cancelled'

The common mistake

= NULL does not find missing values. It evaluates to unknown, so the WHERE clause drops the row. Reach for IS NULL or IS NOT NULL whenever the question is about whether a value exists.

Check the result by looking at the nullable column itself. In this lesson, every returned shipped_date should be blank or NULL, and cancelled orders should not appear.

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

Fix the query so it returns unshipped orders: rows whereshipped date is missing. Exclude cancelled orders and sort from oldest to newest.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer