Reference
SQL NULL Handling
NULL means 'unknown', and it follows its own rules. Here is how to work with it safely.
NULL is not zero and not an empty string. It means the value is missing or unknown. Because the database can't know whether two unknowns are equal, almost any comparison with NULL returnsNULL (treated as "not true"), not true orfalse.
Testing for NULL
Use IS NULL, never = NULL
-- wrong: this matches no rows, ever
where shipped_date = null
-- right
where shipped_date is null
where shipped_date is not nullFunctions for nulls
| 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 (treats two nulls as equal). |
Display-friendly defaults
COALESCE
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_idAvoiding divide-by-zero
NULLIF guards the denominator
select sum(cost) / nullif(sum(price), 0) as cost_ratio
from productsWatch out
Aggregates skip nulls, and NOT IN (list with a null) can drop every row. Nulls also appear from LEFT JOIN non-matches. SeeJOIN types.
Related
Lesson
Why SQL Data Types Matter
See why text, numbers, dates, and booleans behave differently.
Lesson
Numbers and Calculations in SQL
Compare, sort, and calculate with numeric values.
Lesson
Why SQL Strings Need Quotes
Filter text values with string literals.
Lesson
Working with Dates and Times in SQL
Compare and sort date values chronologically.