NULL Handling and COALESCE

The three tools

• COALESCE(a, b, ...) returns the first non-NULL argument. COALESCE(bonus, 0) turns a missing bonus into a real zero for arithmetic.
• NULLIF(a, b) is the reverse: returns NULL if a equals b, otherwise a. Useful for turning a sentinel value like 0 or "" back into a proper NULL before averaging.
• IS NULL / IS NOT NULL are the only correct way to test for NULL. = NULL and != NULL both silently evaluate to UNKNOWN and match nothing.

NULL is not a value

The single idea that unlocks this topic: NULL does not mean zero, and it does not mean an empty string. It means no value was recorded.

That distinction has consequences everywhere. A missing salary is not a salary of 0. A missing middle name is not a middle name of "". And two rows with missing phone numbers do not have the same phone number — you have no idea whether they match, because you do not know either value.

That last point is why NULL = NULL is not TRUE. It is UNKNOWN, which is a third truth value SQL carries alongside TRUE and FALSE.

The arithmetic row causes real bugs. SELECT price + shipping FROM orders returns NULL for every row where shipping was never entered — not the price, but nothing at all. One missing value poisons the whole expression.

The three tools, and when each is right

IS NULL / IS NOT NULL — the only way to test for absence. WHERE column = NULL returns zero rows, always, silently.

COALESCE(a, b, c, ...) — returns the first argument that is not NULL. This is the workhorse:

SELECT name,
       COALESCE(nickname, first_name, 'Unknown')  AS display_name,
       price + COALESCE(shipping, 0)              AS total
FROM   orders;

NULLIF(a, b) — returns NULL when the two arguments are equal, otherwise a. Its main use is preventing division by zero:

SELECT revenue / NULLIF(orders_count, 0) AS avg_order_value FROM daily;

When orders_count is 0, the division becomes revenue / NULL, which is NULL — a missing result instead of an error. Wrap the whole thing in COALESCE(..., 0) if you would rather show zero.

Some engines add their own: IFNULL in MySQL, ISNULL in SQL Server, NVL in Oracle. COALESCE is the standard one and works everywhere, so prefer it.

How aggregates and grouping treat NULLs

The rules differ from clause to clause, which is why NULLs are confusing even to people who know the definition.

• Aggregates skip NULLs. AVG(salary) over 10 rows with 3 missing divides by 7, not 10. SUM ignores them. This is usually sensible and occasionally wrong — if a missing value should count as zero, write AVG(COALESCE(salary, 0)).
• COUNT(*) counts rows; COUNT(column) counts non-NULL values. The gap between the two is a quick way to measure missingness: SELECT COUNT(*) - COUNT(email) FROM users.
• GROUP BY puts all NULLs in one group, treating them as equal — a deliberate exception to the usual rule.
• DISTINCT keeps one NULL, for the same reason.
• ORDER BY must decide where they go. PostgreSQL and Oracle sort them last ascending; MySQL and SQL Server sort them first. Say what you mean with ORDER BY col ASC NULLS LAST where supported, or ORDER BY col IS NULL, col as a portable trick.
• UNION treats NULLs as duplicates of each other when deduplicating.

Two more places NULLs behave unexpectedly: a UNIQUE constraint generally allows several NULLs, because they are not equal to each other, and an outer join manufactures NULLs for the unmatched side — which is precisely how the anti-join pattern works.

Why the row you expected is missing

NULL is not a value, it is the absence of one, and every comparison with it returns NULL rather than true or false. That single rule explains every surprising empty result involving nullable columns, and it is visible in four queries.

-- Dara has no city. Watch which queries find her.
--
-- Read the numbers: "not London" finds 3 customers, not 4. Dara's
-- city is certainly not London, and she is still excluded -- because
-- NULL <> 'London' is NULL, and WHERE keeps only rows that are TRUE.
-- Then note that IS NULL (1) plus IS NOT NULL (4) does reach 5, which
-- is the pair that always partitions a table properly.

SELECT 'city = NULL'      AS test, count(*) AS rows_found FROM customers WHERE city = NULL
UNION ALL
SELECT 'city not equal London',   count(*) FROM customers WHERE city <> 'London'
UNION ALL
SELECT 'city IS NULL',            count(*) FROM customers WHERE city IS NULL
UNION ALL
SELECT 'city IS NOT NULL',        count(*) FROM customers WHERE city IS NOT NULL
UNION ALL
SELECT 'total customers',         count(*) FROM customers;

Try it yourself

1. Look at the raw column. Three employees have no bonus recorded — NULL, not zero.Run this
2. Try bonus = 0. Every NULL row evaluates to UNKNOWN, not TRUE, so none of them match a filter on this condition — even though "no bonus" feels like it should mean zero.Run this
3. Try COALESCE(bonus, 0). The NULLs become real zeros, usable in arithmetic.Run this
4. Compare AVG(bonus) with and without COALESCE. AVG ignores NULLs entirely — it divides by the count of non-NULL rows, not by all rows — so wrapping in COALESCE changes the answer because it changes what counts as a value.Run this
5. Compare COUNT(bonus) with COUNT(*). COUNT(column) skips NULLs; COUNT(*) counts every row regardless. Same table, different answer.Run this

Worth remembering

NULL means unknown, so comparisons involving it are UNKNOWN rather than TRUE or FALSE, and aggregates skip it by default. COALESCE substitutes a default, NULLIF creates a NULL on purpose, and IS NULL is the only comparison that actually works — everything else is a trap that looks like it should work and quietly does not.

Designing so there are fewer of them

Most NULL bugs are really schema decisions arriving late. Three questions to ask when designing a column:

Is a missing value meaningful here? A deleted_at timestamp that is NULL for live rows is a good use of NULL — the absence carries information. A quantity column that is NULL when it should be 0 is not.

Should this be NOT NULL DEFAULT ...? Declaring quantity INT NOT NULL DEFAULT 0 removes an entire class of arithmetic surprises, and it documents the intent to everyone reading the schema.

Is the NULL hiding a missing table? Columns that are NULL for most rows — shipping_address, cancellation_reason — often indicate a one-to-optional relationship that belongs in its own table.

There is one important trade-off in the other direction: sentinel values are worse than NULL. Storing 0 for "unknown salary", or 1900-01-01 for "no date", puts fake data into aggregates and eventually gets treated as real. NULL is honest; a sentinel lies quietly.

Checking your data for missingness

A short audit query that pays for itself on any unfamiliar table:

SELECT COUNT(*)                                        AS rows,
       COUNT(*) - COUNT(email)                         AS email_missing,
       COUNT(*) - COUNT(phone)                         AS phone_missing,
       ROUND(100.0 * (COUNT(*) - COUNT(phone)) / COUNT(*), 1) AS phone_pct_missing
FROM   users;

Two follow-ups worth running. Check whether missingness is patterned — group by signup source or date and see whether the gaps cluster, which usually points at a broken integration rather than genuine absence. And check for disguised NULLs: empty strings, the literal text 'NULL', 'N/A', '-' and 0 where 0 is impossible.

SELECT COUNT(*) FROM users WHERE email = '' OR email = 'NULL' OR email = 'N/A';

Those are far more dangerous than real NULLs, because none of the NULL-handling machinery applies to them.

Questions people ask

Why does WHERE x != 'a' exclude rows where x is NULL? Because NULL != 'a' is UNKNOWN, and only TRUE rows are returned. Add OR x IS NULL.

Is NULL the same as an empty string? No, except in Oracle, which historically treats empty strings as NULL for VARCHAR2 — a notorious portability trap.

Do NULLs take up storage? Very little — typically a bit in a null bitmap rather than a full column width. This is not a reason to avoid them.

Can a primary key be NULL? No. Primary keys are NOT NULL by definition. Unique constraints usually do allow NULLs, and allow several of them.

How do I compare two nullable columns safely? a IS DISTINCT FROM b where supported, or COALESCE(a, sentinel) <> COALESCE(b, sentinel) with a sentinel that cannot occur in the data.

Should I avoid NULLs entirely? No. Some designers argue for it, but the alternatives — sentinel values or an explosion of side tables — are usually worse. Use NULL where absence is genuine, and NOT NULL DEFAULT where it is not.

Recap in one screen

• NULL means "not recorded", not zero and not empty.
• Any comparison with NULL is UNKNOWN, so only IS NULL and IS NOT NULL can test for it.
• Arithmetic and concatenation with NULL produce NULL — wrap in COALESCE.
• Aggregates skip NULLs, GROUP BY collects them into one group, and ORDER BY placement is engine-specific.
• NULLIF is the standard guard against division by zero.
• Prefer NOT NULL DEFAULT to sentinel values, and audit for disguised NULLs like '' and 'N/A'.

NULLs in joins and constraints

Two places where NULL behaviour is easy to forget until it bites.

Outer joins manufacture NULLs. A LEFT JOIN with no match fills every right-hand column with NULL, and those NULLs are indistinguishable from real ones stored in the table. If the right-hand table legitimately contains NULLs, WHERE right.col IS NULL finds both the unmatched rows and the matched-but-empty ones. Test a NOT NULL column — usually the primary key — when you mean "no match".

Constraints treat NULL loosely. A UNIQUE constraint permits many NULLs in most engines, because no two of them are equal. So UNIQUE(email) does not stop a thousand rows with no email — usually the behaviour you want, occasionally a surprise. CHECK constraints pass when the condition evaluates to UNKNOWN, so CHECK (age >= 18) allows a NULL age. Add NOT NULL if you meant to require a value.

The same looseness appears in foreign keys: a NULL foreign key is allowed and means "no relationship", which is how optional links are modelled.