NULL values
Understanding NULL
Database columns do not always contain a value.
For example, our orders table contains a shipped_at column.
An order that has already shipped can contain a timestamp:
12026-08-10 14:30:00+00But an order that has not been shipped yet can contain:
1NULLNULL represents a missing or unknown value.
Understanding how SQL treats NULL is important because it behaves differently from ordinary values.
NULL is not zero
Consider:
10This is a known numeric value.
It means zero.
NULL means that there is no known value.
So:
10and:
1NULLmean completely different things.
For example:
1balance = 0might mean:
1We know the account balance, and it is zero.While:
1balance = NULLmight mean:
1We do not know the account balance.NULL is not an empty string
The same distinction applies to text.
An empty string:
1''is a real text value whose length is zero.
NULL represents the absence of a known text value.
Conceptually:
12345678'PostgreSQL'→ a string containing text''→ a string containing no charactersNULL→ no known string valueThese are three different states.
Comparing values with NULL
Suppose we want to find orders that have not been shipped.
You might try:
12345SELECT id, shipped_atFROM ordersWHERE shipped_at = NULL;This does not work the way you might expect. It returns no rows at all.
The reason is that NULL represents an unknown value.
PostgreSQL cannot say:
1unknown value = unknown valueis definitely TRUE.
Instead, the result of the comparison is itself unknown.
For example:
1SELECT NULL = NULL;returns:
123 ?column?---------- NULLIt does not return:
1TRUEThe same idea applies to ordinary comparisons involving NULL:
12310 = NULL → NULL10 <> NULL → NULL10 > NULL → NULLIS NULL
To test whether a value is NULL, use:
1IS NULLFor example:
12345678SELECT id, status, shipped_atFROM ordersWHERE shipped_at IS NULLORDER BY idLIMIT 10;You should see:
123456789101112 id | status | shipped_at----+------------+------------ 85 | processing | NULL 86 | processing | NULL 87 | processing | NULL 88 | processing | NULL 89 | processing | NULL 90 | processing | NULL 91 | processing | NULL 92 | processing | NULL 93 | cancelled | NULL 94 | cancelled | NULLThese are orders that have not shipped yet, so their shipped_at value is NULL.
The correct comparison is therefore:
123wrongshipped_at = NULL123correctshipped_at IS NULLIS NOT NULL
To find rows where a value exists, use:
1IS NOT NULLFor example:
12345678SELECT id, status, shipped_atFROM ordersWHERE shipped_at IS NOT NULLORDER BY idLIMIT 10;You should see:
123456789101112 id | status | shipped_at----+-----------+------------------------ 1 | delivered | 2021-01-04 00:00:00+00 2 | delivered | 2021-01-06 00:00:00+00 3 | delivered | 2021-01-08 00:00:00+00 4 | delivered | 2021-01-10 00:00:00+00 5 | delivered | 2021-01-07 00:00:00+00 6 | delivered | 2021-01-09 00:00:00+00 7 | delivered | 2021-01-11 00:00:00+00 8 | delivered | 2021-01-13 00:00:00+00 9 | delivered | 2021-01-15 00:00:00+00 10 | delivered | 2021-01-12 00:00:00+00A useful mental model is:
12345IS NULL→ Is the value missing?IS NOT NULL→ Is there a known value?SQL uses three-valued logic
Most programming conditions are commonly thought of as having two possible results:
12TRUEFALSESQL introduces another possibility because of NULL:
123TRUEFALSEUNKNOWNIn PostgreSQL, this unknown result is represented by NULL.
For example:
1234567810 > 5→ TRUE10 < 5→ FALSE10 > NULL→ UNKNOWNThis is known as three-valued logic.
12345678910comparison │ ├── definitely true ─────▶ TRUE │ ├── definitely false ────▶ FALSE │ └── cannot be known ─────▶ UNKNOWN │ ▼ NULLWHERE keeps only TRUE
This has an important effect on WHERE.
A row is kept by WHERE only when the condition evaluates to:
1TRUERows whose condition evaluates to:
1FALSEor:
1UNKNOWNare not returned.
Consider:
12345678SELECT *FROM ( VALUES (1, 10), (2, 20), (3, NULL)) AS values_table(id, amount)WHERE amount > 15;The conditions are:
123row 1: 10 > 15 → FALSErow 2: 20 > 15 → TRUErow 3: NULL > 15 → UNKNOWNso you should see only:
123 id | amount----+-------- 2 | 20The NULL row is not returned because UNKNOWN is not TRUE.
NULL can affect NOT conditions too
Three-valued logic can sometimes produce results that initially look surprising.
For example:
12345678SELECT *FROM ( VALUES (10), (20), (NULL)) AS values_table(amount)WHERE NOT (amount = 10);You might expect this to return both 20 and NULL.
But:
123456789101110 = 10→ TRUE→ NOT TRUE = FALSE20 = 10→ FALSE→ NOT FALSE = TRUENULL = 10→ UNKNOWN→ NOT UNKNOWN = UNKNOWNSo the result is only:
123 amount-------- 20NOT does not turn an unknown comparison into TRUE.
Comparing NULL safely
Sometimes we want to compare two values while treating NULL as something that can participate in equality.
PostgreSQL provides:
1IS NOT DISTINCT FROMFor example:
1SELECT NULL IS NOT DISTINCT FROM NULL;returns:
1trueCompare that with:
1SELECT NULL = NULL;which returns:
1NULLIS NOT DISTINCT FROM behaves much like an equality comparison that also handles NULL values predictably.
For example:
123456789101110 IS NOT DISTINCT FROM 10→ true10 IS NOT DISTINCT FROM 20→ falseNULL IS NOT DISTINCT FROM NULL→ true10 IS NOT DISTINCT FROM NULL→ falsePostgreSQL also provides the opposite:
1IS DISTINCT FROMFor example:
1234567810 IS DISTINCT FROM 20→ trueNULL IS DISTINCT FROM NULL→ false10 IS DISTINCT FROM NULL→ trueThese operators are useful when two nullable values need to be compared without producing UNKNOWN.
When should a column allow NULL?
Whether a column should allow NULL is a database-design decision.
For example, our orders.shipped_at column can reasonably be NULL because an order might not have shipped yet.
But a value such as:
1customers.emailis required in our schema.
That column therefore uses:
1NOT NULLA useful question is:
Does the absence of this value represent a valid state for this row?
If yes, allowing NULL may make sense.
If every row must have the value, enforce that rule with NOT NULL.
The core mental model
Remember:
12345678NULL≠ 0NULL≠ ''NULL = NULL→ UNKNOWNTo test for missing values:
12IS NULLIS NOT NULLAnd when ordinary equality needs to treat two NULL values as matching:
1IS NOT DISTINCT FROMThe important point is:
`NULL` represents missing or unknown information, so SQL uses three-valued logic rather than treating `NULL` like an ordinary value.