September 11, 2026

NULL in SQL: the complete guide

NULL isn't zero or an empty string — it's "unknown," and that one idea explains a dozen behaviors that otherwise look like bugs.

Most SQL surprises trace back to the same root cause: treating NULL like a value, when it's actually the absence of one. Every behavior on this page follows from a single idea — NULL means unknown, and almost anything you do with an unknown produces another unknown.

Three-valued logic

SQL doesn't have two truth values, it has three: TRUE, FALSE, and UNKNOWN (NULL, in boolean position). A comparison against an unknown is itself unknown — and AND/OR have special rules for combining an unknown with something you do know:

Loading editor…

cancelled_at='x' is unknown for both rows (cancelled_at is NULL). Order 101's status isn't 'pending' (FALSE), so FALSE AND unknown = FALSE — the AND short-circuits — but FALSE OR unknown can't rule out TRUE, so it stays unknown (NULL). Order 108's status is 'pending' (TRUE), so it's the mirror image: TRUE AND unknown = unknown, TRUE OR unknown = TRUE. AND/OR each have one input value that overrides an unknown, and one that doesn't.

Comparisons: NULL never equals anything, including NULL

Loading editor…

Zero — not because no row has that amount, but because x = NULL is always unknown, for every row, regardless of x. = and <> can never be used to test for NULL; that's what IS NULL / IS NOT NULL are for:

Loading editor…

Arithmetic and concatenation propagate it

Loading editor…

Any arithmetic expression touching a NULL is NULL — not an error, not zero. The same is true of || concatenation (see string functions for how CONCAT() differs).

Aggregates skip NULL — they don't treat it as zero

Loading editor…

AVG divides by the count of non-null values, not the row count — a real difference from what you'd get by treating a missing amount as $0. The same asymmetry shows up in COUNT:

Loading editor…

COUNT(*) counts rows. COUNT(column) counts non-null values of that column — two different questions that happen to share a function name.

GROUP BY treats NULL as its own group

Loading editor…

Even though NULL = NULL is unknown, GROUP BY (and DISTINCT) bucket all the NULLs together as a single group. This is a deliberate exception to three-valued logic — grouping needs some answer to "are these the same," and SQL picks "yes" for two NULLs specifically for this purpose.

ORDER BY: where NULL sorts depends on the engine

Loading editor…

SQLite (like MySQL and SQL Server) sorts NULL as the lowest possible value, so ascending order puts it first — as seen above. Postgres is the outlier: it defaults to NULLS LAST on ascending order. Don't rely on the default across engines; be explicit with ORDER BY col NULLS LAST (Postgres, SQLite 3.30+) or the portable trick that works everywhere:

Loading editor…

cancelled_at IS NULL is 0 for real values and 1 for NULL — sorting by that first pushes every NULL to the end, then the second key sorts the rest normally.

Filling in a default: COALESCE

Loading editor…

COALESCE(a, b, c, ...) returns the first non-null argument. Full reference: COALESCE.

The reverse: NULLIF

Loading editor…

NULLIF(a, b) returns NULL if a = b, otherwise a — useful for turning a sentinel value (a placeholder 0, an empty string standing in for "no data") into a real NULL before it feeds into an AVG or a division.

NULL-safe equality

Sometimes you genuinely want "are these the same, treating two NULLs as equal" — the opposite of standard =:

Loading editor…

IS NOT DISTINCT FROM (Postgres, SQLite) never returns unknown — it's a real two-valued TRUE/FALSE. MySQL spells it <=>. SQL Server has no direct equivalent; the common workaround is (a = b) OR (a IS NULL AND b IS NULL).

The one to actively avoid: NOT IN with a nullable column

This deserves its own page because it's the single most common NULL-caused bug: NOT IN (subquery) silently returns zero rows if the subquery's column can contain NULL — not an error, just an empty result that looks like "no matches" instead of "something's wrong." Full walkthrough, live demo, and the fix: EXISTS and IN.

Quick reference