9 NULLs

Understand NULL, unknown comparisons, and NULL-aware expressions.

NULL is SQL’s marker for unknown or missing. It is not zero, not the empty string, and not equal to anything — not even to another NULL. This three-valued logic (true, false, unknown) is the source of more SQL bugs than any other feature, but the rules are simple once you know them.

What you’ll learn

What NULL represents
Testing for NULL with IS NULL / IS NOT NULL
How NULL propagates through expressions and comparisons
How NULL behaves in AND/OR (three-valued logic)
Replacing NULL with COALESCE and NULLIF

Example

Example 9.1 NULLs

-- NULL is missing/unknown
SELECT 1 AS a, NULL AS b;

-- = NULL is NULL (not true), so this returns 0 rows
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name = NULL;

-- IS NULL / IS NOT NULL are the correct tests
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name IS NULL;
SELECT * FROM (VALUES (1, 'x')) AS t(id, name) WHERE name IS NOT NULL;

-- Any arithmetic with NULL is NULL
SELECT 1 + NULL AS one_plus_null, 'a' || NULL AS concat_null;

-- COALESCE returns the first non-null argument
SELECT COALESCE(NULL, NULL, 'default') AS coalesced;

-- NULLIF returns NULL if the two args are equal, else the first
SELECT NULLIF(0, 0) AS hides_zero, NULLIF(5, 0) AS keeps_five;

-- Three-valued logic
SELECT (TRUE AND NULL) AS t_and_n, (TRUE OR NULL) AS t_or_n, (FALSE AND NULL) AS f_and_n;

= NULL does not work because the comparison evaluates to NULL, which is not TRUE, so the WHERE discards the row. The same applies to <>, <, >, IN, BETWEEN, and so on — almost any operator returns NULL when either operand is NULL. The exceptions are IS NULL, IS NOT NULL, IS DISTINCT FROM, and IS NOT DISTINCT FROM (the last two are NULL-aware versions of <> and =).

COALESCE(a, b, c) returns the first argument that is not NULL. It is the standard way to provide defaults: COALESCE(nickname, first_name, 'Anonymous'). NULLIF(a, b) returns NULL when a = b — handy when you want to turn sentinel values back into NULL (e.g. NULLIF(comment, '')).

The three-valued logic table is short and worth memorizing: TRUE OR NULL is TRUE (we know at least one side is true), but TRUE AND NULL is NULL (we don’t know the other side). FALSE AND NULL is FALSE (one false is enough). FALSE OR NULL is NULL.

Run the example

To run:

$ psql -f source/nulls.sql postgres
 a | b
---+---
 1 |
(1 row)
 ...

Common pitfalls

Forgetting that aggregates skip NULL: SUM, AVG, MIN, MAX, COUNT(col) ignore NULL inputs. COUNT(*) counts every row including those with NULLs.
NOT IN (subquery) becomes the empty result if the subquery returns a single NULL — because x <> NULL is NULL. Prefer NOT EXISTS.
NULL <> NULL is unknown (NULL), not true. DISTINCT groups NULLs together; IS DISTINCT FROM provides a comparison that always returns true or false.
Default ordering: ORDER BY col ASC puts NULLs last; DESC puts them first. Specify NULLS FIRST/NULLS LAST for predictable behavior.

Tip

When you really want NULL-aware equality, use IS NOT DISTINCT FROM. For example, WHERE a IS NOT DISTINCT FROM b is true when both are equal or both are NULL.

Try it

Run SELECT 1 = NULL;, then SELECT 1 IS NOT NULL;, then SELECT NULL IS NOT DISTINCT FROM NULL;. See how the answers differ. Then try SELECT count(*) AS rows, count(b) AS non_null_b FROM (VALUES (1, NULL), (2, 'x')) AS t(a, b);.

Downloads

SQL file: nulls.sql · All lesson files · Setup guide