Postgres by Example: NULLs

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

-- 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.

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.
• Two NULLs are distinct under <> but not distinct under DISTINCT. The standard chose differently in each place — yes, it is confusing.
• 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);.

Source: nulls.sql

Next: Expressions

Home: Postgres by Example