beginnerintermediateadvanced

PostgreSQL · NULL and Data Semantics

NULL, Three-Valued Logic and Data Semantics

NULL as genuine absence (not zero), IS NULL vs =, one rule across WHERE/JOIN/aggregates/GROUP BY, COALESCE/NULLIF, the NOT IN trap, and designing NULL-ability on purpose

NULL ≠ 0, '', falseit means no value was recorded at all
IS NULL, never = NULLthe only operators guaranteed true/false
GROUP BY groups NULLsthe one deliberate exception to "NULL ≠ NULL"
NOT IN + any NULL = 0 rowsliteral list or subquery — same rule either way

1.NULL as Unknown/Missing, Not an Ordinary Value

NULL is not zero, not an empty string, and not false — it is the absence of a known value. A numeric column that is NULL is not "0," a text column that is NULL is not "''," and a boolean column that is NULL is not "false." Each of those is a real, specific value; NULL is the claim that no value is recorded at all.

Key features

NULL is not 0, not an empty string, and not false — those are all real, specific values
A NULL in a numeric column does not participate in SUM/AVG as if it were zero — it is skipped entirely by aggregate functions
Two NULLs are never considered equal to each other, because "unknown" is not "equal to another unknown"
A column being NULL-able is a schema decision about whether "no value" is a legitimate state for that column
PostgreSQL's aggregate functions (SUM, AVG, COUNT(col)) all ignore NULL rows rather than treating them as zero

NULL vs the nearest "empty-looking" real value, per type

NULL vs the nearest "empty-looking" real value, per type
Type	A real value that looks "empty"	NULL
integer	0 — a specific count	no count recorded at all
text	'' — a specific zero-length string	no string recorded at all
boolean	false — a specific negative answer	no answer recorded at all

A real "zero-like" value vs a genuinely missing one

bonus = 0

a fact: this employee's bonus is exactly zero
participates normally in SUM/AVG
counted by COUNT(bonus)

bonus = NULL

no fact recorded — bonus amount unknown/not applicable
skipped entirely by SUM/AVG, not treated as 0
NOT counted by COUNT(bonus)

Command

sql

SELECT count(*) AS all_rows, count(bonus) AS rows_with_a_bonus, avg(bonus) AS avg_of_known_bonuses
FROM employees;

NULL means no value was recorded — not zero, not empty string, not false. Aggregate functions like SUM/AVG/COUNT(col) skip NULL rows entirely rather than treating them as zero.

2.IS NULL and IS NOT NULL

IS NULL and IS NOT NULL are the only operators built specifically to test for the presence or absence of a value — they always return true or false, never NULL, which is what makes them safe to use in a WHERE clause where = NULL would silently fail.

Key features

IS NULL and IS NOT NULL always evaluate to true or false — never NULL
IS NULL works on any type: integer, text, timestamp, boolean — it tests presence, not a specific value
IS DISTINCT FROM / IS NOT DISTINCT FROM extend the same idea to comparisons, treating two NULLs as equal to each other, unlike =
A CHECK constraint or WHERE clause combining NULL logic with AND/OR should be tested against NULL rows explicitly, not assumed correct
IS NULL is not the same operator as = , and cannot be indexed or optimized identically in every case — though a plain B-tree index does support it

IS NULL vs IS DISTINCT FROM — two ways to sidestep three-valued logic

IS NULL vs IS DISTINCT FROM — two ways to sidestep three-valued logic
Expression	Always true/false?	What it tests
col = NULL	no — always NULL	invalid — never use this
col IS NULL	yes	is this value absent?
a IS NOT DISTINCT FROM b	yes	are these equal, treating two NULLs as equal?

Command

sql

SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;
SELECT a IS NOT DISTINCT FROM b;  -- true even when both a and b are NULL

IS NULL/IS NOT NULL always return true or false, never NULL — they test presence, not value, which is why they replace = for this specific question. IS NOT DISTINCT FROM extends the same idea to full comparisons.

3.How NULL Affects Comparisons, Joins, Aggregates and Boolean Expressions

One rule explains all of NULL's behavior across SQL: any comparison involving NULL evaluates to NULL (unknown), and NULL is treated as not-true everywhere a true/false decision is required. This single rule produces different-looking effects in WHERE, JOIN ON, aggregates and boolean expressions — but it is the same rule every time.

Key features

A JOIN ON predicate follows the same three-valued rule as WHERE — a NULL join key never matches anything, including another NULL
LEFT/RIGHT/FULL JOIN's "padding" NULLs are a different mechanism — they are inserted by the join itself for unmatched rows, not compared
Aggregate functions (SUM, AVG, MIN, MAX, COUNT(col)) skip NULL inputs; COUNT(*) counts rows regardless of NULLs
GROUP BY treats all NULL values as one group, together — an intentional exception to "NULL is never equal to NULL"
ORDER BY places NULLs either first or last depending on NULLS FIRST/LAST (default is NULLS LAST for ASC, NULLS FIRST for DESC)

The same NULL rule, four different clauses

The same NULL rule, four different clauses
Clause	What happens with a NULL involved
WHERE / JOIN ON	a NULL comparison is never true — row/match excluded
Aggregate functions	NULL input rows are skipped, not treated as zero
GROUP BY	all NULLs are grouped together as one group — an exception to "NULL ≠ NULL"
ORDER BY	NULLs sort first or last, controlled by NULLS FIRST/LAST

One rule, four clauses

WHERE / JOIN ON

NULL comparison never true

Aggregates

NULL inputs skipped

GROUP BY

NULLs grouped together — the exception

ORDER BY

NULLS FIRST/LAST controls placement

Command

sql

SELECT region, count(*) FROM customers GROUP BY region ORDER BY region NULLS LAST;

One rule — any comparison with NULL is unknown, and unknown is treated as not-true — explains WHERE, JOIN ON, and boolean expressions. GROUP BY is the one deliberate exception, grouping all NULLs together.

4.COALESCE and NULLIF

COALESCE(a, b, c, ...) returns the first non-NULL argument — the standard way to supply a default for a missing value. NULLIF(a, b) returns NULL if a equals b, otherwise returns a — the standard way to turn a sentinel value into a real NULL.

Key features

COALESCE accepts any number of arguments and returns the first one that is not NULL, evaluated left to right
COALESCE stops evaluating once it finds a non-NULL argument — later arguments are not evaluated
NULLIF(a, b) is shorthand for CASE WHEN a = b THEN NULL ELSE a END
COALESCE(NULLIF(x, sentinel), default) is the standard idiom for "treat a sentinel value as absence, then supply a default"
Both functions determine their result type from their arguments and require compatible types across all arguments

COALESCE vs NULLIF — opposite directions

COALESCE vs NULLIF — opposite directions
Function	Direction	Typical use
COALESCE(a, b, ...)	NULL → a real value	supply a default for a missing value
NULLIF(a, b)	a real value → NULL	treat a sentinel (like empty string or -1) as missing

Command

sql

SELECT COALESCE(phone, 'no phone on file') FROM customers;
SELECT NULLIF(status, 'unset') FROM tasks;
SELECT COALESCE(NULLIF(status, 'unset'), 'pending') FROM tasks;

COALESCE returns the first non-NULL argument (NULL → a real value); NULLIF returns NULL when two values are equal (a real value → NULL) — chain them to normalize a legacy sentinel into NULL, then supply a default.

5.NOT IN Pitfalls When NULL Values Exist

NOT IN against any list — a literal list or a subquery result — that contains even one NULL always returns zero rows, silently, for every row in the outer query. This happens whether the NULL comes from a hardcoded list written by hand or from a subquery's result.

Key features

x NOT IN (a, b, NULL) is always NULL, for every possible x — never true, regardless of a and b
This applies to a literal list written by hand, not only a subquery — WHERE x NOT IN (1, 2, NULL) matches nothing, ever
IN (without NOT) is unaffected: x IN (a, b, NULL) can still be true if x equals a or b
The safe general fix is NOT EXISTS for subqueries, or explicitly filtering NULLs out of a literal list before using NOT IN
PostgreSQL raises no warning or error for this — the query runs successfully and returns an empty, silently wrong result

NOT IN vs IN when the list contains a NULL

NOT IN vs IN when the list contains a NULL
Expression	Result
5 IN (1, 2, NULL)	NULL (unknown) — but if 5 were actually in the list, e.g. 5 IN (1, 5, NULL), it would be true
5 NOT IN (1, 2, NULL)	NULL (unknown) — always, since NULL makes the AND chain unknown regardless of whether 5 matches 1 or 2
5 NOT IN (1, 2)	true — no NULL present, behaves as expected

Why NOT IN with a NULL in the list always returns unknown

x NOT IN (1, 2, NULL)

expands to an AND chain

x<>1 AND x<>2 AND x<>NULL

the last term is always NULL

true AND true AND NULL

AND with any NULL operand is NULL

Result: NULL

WHERE discards it — 0 rows, always

Command

sql

-- dangerous: a literal list containing NULL, not just a subquery
SELECT * FROM products WHERE category_id NOT IN (1, 2, NULL);
-- returns ZERO rows regardless of category_id's value

-- safe
SELECT * FROM products WHERE category_id NOT IN (1, 2)
   OR category_id IS NULL;

NOT IN against ANY list — literal or subquery — containing even one NULL always returns zero rows, silently. This is a property of the NOT IN operator itself, not something specific to subqueries.

6.Designing Schemas So NULL Means Meaningful Absence

A NULL-able column should answer a specific question: "why might this legitimately have no value?" If there is no good answer — if every row should always have a value — the column should be NOT NULL, and any current gaps are a data problem to fix, not a schema feature to accommodate.

Key features

A NULL-able column should have a specific, articulable reason values can legitimately be absent
A column that is NULL-able "just in case" or "to avoid migration friction" tends to accumulate genuinely ambiguous data over time
Defaulting a column to NOT NULL and requiring an explicit value catches missing-data bugs at write time instead of query time
A group of columns that are all NULL together whenever one specific condition holds often signals the table is really modeling two different entities
Once NULL-ability is chosen deliberately, every query author downstream inherits a predictable, documented meaning for that column's NULL

A NULL-able column, with and without an articulable reason

A NULL-able column, with and without an articulable reason
Column	NULL-able?	Why (or why not)
orders.shipped_at	yes	legitimately absent until the order actually ships
orders.cancelled_at	yes	legitimately absent for every order that was never cancelled
orders.customer_id	no	every order must belong to a customer — no legitimate absence case
orders.total (no clear reason to allow absence)	should be no	a missing total is a data bug, not a real business state

Command

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    total NUMERIC NOT NULL,
    shipped_at TIMESTAMPTZ,      -- legitimately absent until shipped
    cancelled_at TIMESTAMPTZ     -- legitimately absent unless cancelled
);

A NULL-able column should have a specific, articulable reason values can legitimately be absent — if there is no such reason, the column belongs as NOT NULL, and existing gaps are a data bug to fix, not a schema feature to accommodate.

Spotted a mistake on this page? Tell us.

Read Sheet