SQL NULL Handling

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 returns NULL (treated as "not true"), not true or false.

Testing for NULL

-- wrong: this matches no rows, ever
where shipped_date = null

-- right
where shipped_date is null
where shipped_date is not null

Functions for nulls

Display-friendly defaults

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

Avoiding divide-by-zero

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

Without the guard, DuckDB does not stop on a zero denominator: 1 / 0 is Infinity and 0 / 0 is NaN, and one such row makes a SUM or AVG over it Infinity or NaN too. PostgreSQL and SQL Server raise an error instead. NULLIF turns the zero into NULL, so the result is NULL, which aggregates skip. Integer division (//) and % by zero already return NULL in DuckDB.

NULLs in joins

A join's ON clause follows the same rule as WHERE: a pair is kept only when the condition is true. So a row whose join key is NULL matches nothing, not even a NULL key on the other side, and an INNER JOIN drops it without an error. See why an INNER JOIN drops rows with NULL keys, and the null-safe join for when two NULL keys should match.

Every clause that keeps rows on a definite yes treats a NULL comparison as a no. Before trusting a filter, a join, or a count, check which of its columns can be NULL.