Cheatsheet

SQL NULL Handling Cheatsheet

IS NULL, COALESCE, NULLIF, and null-safe equality.

Test for NULL

IS NULL, never = NULL

where shipped_date is null       -- right
where shipped_date is not null
-- where shipped_date = null      -- wrong: matches no rows, ever

NULL means "unknown", so any comparison with it isNULL (not true), so the row drops out. That is also whyNULL = NULL is not true: the database can't confirm two unknown values are the same, so it won't say they match.

The functions

Function	Returns
`coalesce(a, b, c)`	First non-null argument.
`nullif(a, b)`	`NULL` if `a = b`, else `a`.
`a is not distinct from b`	Null-safe equality (two nulls count as equal).

Defaults for display

COALESCE fills gaps

select c.first_name,
       coalesce(c.phone, 'No phone on file') as phone,
       coalesce(o.shipped_date, o.order_date) as effective_date
from customers as c
left join orders as o on o.customer_id = c.customer_id

Guard divide-by-zero

NULLIF on the denominator

select sum(cost) / nullif(sum(price), 0) as cost_ratio
from   products

Watch out

Aggregates skip nulls (except count(*)).NOT IN (… a null …) can drop every row. UseNOT EXISTS. And nulls appear from LEFT JOINnon-matches, so filter those with IS NULL, not =.

Lesson

Missing Values with SQL NULL

Find missing values with IS NULL.

Lesson

Null-Safe Filtering in SQL

Keep NULLs from silently changing filter results.

Reference

SQL NULL Handling

IS NULL, COALESCE, NULLIF, and null-safe logic.

Mistake

Why = NULL Never Matches in SQL

Use IS NULL instead of equality.