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 for testing missingness

NULL marks a missing or unknown value. Ordinary comparison operators such as =, <>, and > involving a null evaluate to UNKNOWN, which a WHERE clause does not keep. Null-aware predicates such as IS NULL and IS [NOT] DISTINCT FROM are designed for these cases.

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 is especially risky when its list or subquery can contain NULL: rows that do not otherwise match can evaluate to UNKNOWN and disappear. NOT EXISTS is usually clearer for missing-match checks. Nulls also appear on the unmatched side of an outer join, so test those with IS NULL, not = NULL.

Lesson

Missing Values with SQL NULL

Find missing values with IS NULL.

Reference

SQL NULL Handling

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

Interview

SQL CTE Interview Questions

Practice WITH clauses and step-by-step reports.

Mistake

Why = NULL Never Matches in SQL

Use IS NULL instead of equality.