NULL Handling and COALESCE
NULL means "unknown", not zero and not empty. Pick an expression and watch the same rows get handled differently by each one.
The Expression
Reading It
COALESCE(a, b) — first non-NULL argument.
NULLIF(a, b) — NULL if a = b, else a.
x = NULL — always UNKNOWN, never TRUE. Use IS NULL.
employees.bonus, and this expression applied
—Query
Result
Overview
A column value of NULL means the value is missing, unknown, or intentionally not applicable according to the data model. It is not equal to zero, an empty string, or even another NULL. If the reason for absence matters, store that reason separately.
NULL is not zero or an empty string
SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN. For example, score = NULL is UNKNOWN, not TRUE. WHERE keeps only rows whose condition is TRUE, which explains why a comparison can silently exclude the row you expected.
Test and replace missing values explicitly
Use IS NULL or IS NOT NULL to test for missingness.
COALESCE(a, b) returns the first non-NULL argument. Use it when a fallback has the right meaning, not simply to make a display look full. Replacing missing revenue with zero is valid only if “no recorded value” means “no revenue” for that analysis.
Aggregates and membership
COUNT(*) counts every row; COUNT(column) ignores NULL values. SUM and AVG also ignore NULL inputs, and an aggregate over no values may return NULL. See aggregate functions.
A subtle case is NOT IN: if its candidate list contains a NULL, comparisons can become UNKNOWN and filter out rows. NOT EXISTS can better express “no related match,” with a correct correlation condition. See EXISTS, IN, and JOIN.
Count what the metric means
If a survey has 100 responses and 18 respondents skipped a question, COUNT(*) is 100 while COUNT(answer) is 82. Neither count is wrong: one measures response rows, the other measures supplied answers. AVG(answer) uses the known answers only. If missing answers should be treated as a category, count them explicitly or group by the nullable column. Decide how missingness affects the metric before replacing NULL with a default, because that transformation can erase useful information.
Do not collapse distinct missing states
If an address is absent because it is unknown, not collected, or deliberately withheld, a single NULL cannot tell those cases apart. A separate status column can preserve that distinction. A default value such as an empty string may make reports easier but can turn missing data into a value that users mistake for a real answer. Decide whether absence is allowed, how it affects constraints, and how it appears in aggregates before choosing a replacement.
Further reading
- SQLite expressions documents NULL comparisons and
IS. - SQLite aggregate functions explains how aggregates treat NULL and empty inputs.