SQL NULL Handling Cheat Sheet: COALESCE, IS NULL, and the Traps

The Behavior Table

Operation	Result with NULL	Notes
NULL = NULL	UNKNOWN (not true)	Rows are dropped by WHERE
col != 'x' when col is NULL	UNKNOWN — row excluded	"Everything except x" silently loses NULL rows
COUNT(*)	Counts all rows	—
COUNT(col)	Skips NULLs	The usual source of "wrong" counts
SUM/AVG/MIN/MAX	Ignore NULLs	AVG divides by non-NULL count only
SUM over zero rows	NULL (not 0)	Wrap in COALESCE for reports
'a' || NULL	NULL	Any concat touching NULL is NULL — use CONCAT() in MySQL, which also returns NULL
GROUP BY with NULLs	All NULLs form one group	— (except some engines' ROLLUP edge cases)
DISTINCT with NULLs	One NULL kept	Multiple NULLs collapse to one
ORDER BY with NULLs	Dialect-dependent	PG: last on ASC. MySQL: first on ASC
UNIQUE index	Multiple NULLs allowed	Standard SQL; use partial/filtered index to forbid
NULL IN (1, 2, NULL)	UNKNOWN	Why NOT IN is dangerous

The Eight Traps, With Fixes

1. NOT IN That Returns Nothing

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders); — if any row in orders has NULL user_id (orphaned data, bad imports), the whole result is empty. Rewrite as NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id) and the NULLs stop mattering. Full comparison: EXISTS vs IN.

2. COUNT(col) vs COUNT(*)

After a LEFT JOIN, COUNT(o.id) counts only matched rows, COUNT(*) counts everything including the null-extended side. Use COUNT(*) for "how many rows", COUNT(col) for "how many have a value" — and pick deliberately, because dashboards silently disagree otherwise.

3. AVG Skips NULLs

AVG(rating) over ratings 5, 4, NULL is 4.5, not 3. If NULL means "rated zero", you wanted AVG(COALESCE(rating, 0)). If NULL means "no rating given", the default behavior is correct. The number is the same query away — the mistake is not deciding which NULL means what.

4. String Concatenation Poisons the Whole Expression

first_name || ' ' || last_name returns NULL when either name is NULL. The portable fix: COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''). PostgreSQL also has CONCAT_WS(' ', first_name, last_name) — the separator form of CONCAT skips NULLs entirely and drops the stray space.

5. Division by NULL (and by Zero)

total / NULL is NULL — which propagates into your percentage column silently. total / 0 is a division-by-zero error in PostgreSQL (and in MySQL returns NULL unless ERROR_FOR_DIVISION_BY_ZERO is strict). The defensive recipe that keeps rows: COALESCE(total / NULLIF(cnt, 0), 0) — NULLIF turns the zero denominator into NULL, the division becomes NULL, and the outer COALESCE gives you a reportable 0.

6. NULL in ORDER BY

PostgreSQL sorts NULLs last on ASC, MySQL sorts them first, and some engines let you choose: ORDER BY col ASC NULLS LAST (PG, Oracle, SQL Server 2022+). For deterministic cross-engine output, sort on CASE WHEN col IS NULL THEN 1 ELSE 0 END, col or use the explicit NULLS clause where supported.

7. Unique Constraints Do Not Stop Duplicate NULLs

Every mainstream engine allows multiple NULLs in a UNIQUE column — the standard says NULL is not equal to anything, including itself. If you need a nullable-but-unique column (one active promo code per user, say), PostgreSQL offers a partial index: CREATE UNIQUE INDEX ON codes (user_id) WHERE active; — MySQL 8 does not have partial indexes, so enforce it in application logic or a trigger.

8. Aggregating an Empty Set

SUM and MIN over zero rows return NULL, which breaks charts expecting zero. But the classic bug is subtler: WHERE total > 0 after a LEFT JOIN drops every unmatched row. Use WHERE COALESCE(total, 0) > 0 — or better, WHERE total IS NULL OR total > 0, which states the intent that unmatched rows should survive.

The Portable Toolkit

Need	Write
Default a value	COALESCE(col, default) — works everywhere
Treat empty string as NULL	NULLIF(TRIM(col), '')
Boolean flags from NULLs	col IS NULL AS is_missing (PostgreSQL), IF(col IS NULL, 1, 0) (MySQL)
Count population vs rows	COUNT(*), COUNT(col) side by side
Safe average	AVG(COALESCE(col, 0)) or AVG(NULLIF(col, 0)) — choose by semantics

Try It Yourself

Stuck on a NULL-shaped bug? Describe the query to our SQL generator — say "top 10 customers by spend, treating NULL spend as zero" — and it writes the COALESCE/NULLIF pattern in your dialect.