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 isn't the same as zero, an empty string, or the word "null".

Because NULL marks a missing or unknown value, equality doesn't work the way it does for ordinary values: ordinary comparisons involving NULL evaluate to UNKNOWN. Use IS 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 doesn't 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 shouldn't appear.

NULL changes counting too: COUNT(column) skips missing values, while COUNT(*) counts every row. See why COUNT(column) ignores NULL.

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

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