Part 2 — Querying › Chapter 09

NULL and three-valued logic

NULL is not a value, = NULL is never true, and NOT IN with a NULL returns nothing. All three bite in production.

From the advert

“Ottima conoscenza del linguaggio SQL” — the fastest way to prove you have written SQL in production rather than only in tutorials is to be fluent about NULL.

NULL is not a value. It is the absence of one.

The bottom row is the one that surprises people: CHECK (price > 0) does not stop a null price, and WHERE price > 0 will never find it again.

🧒 Simple

Two envelopes, both sealed. Are the numbers inside them the same? You cannot say yes and you cannot say no — you can only say I don't know. That third answer is what breaks everything, because computers usually only have two.

🎓 Professional

SQL uses three-valued logic: TRUE, FALSE, UNKNOWN. Any comparison involving NULL yields UNKNOWN, including NULL = NULL and, notoriously, NULL <> NULL. WHERE, ON and HAVING keep rows only where the predicate is TRUE, so UNKNOWN behaves like FALSE there — but CHECK constraints accept UNKNOWN, which is the opposite and is the source of a whole class of surprises.

`AND`	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

Note the two bold cells: FALSE AND UNKNOWN is FALSE, because whatever the unknown turns out to be, the conjunction is false. Similarly TRUE OR UNKNOWN is TRUE. Three-valued logic is not arbitrary; it is what you get by asking “is this true for every possible value of the unknown?”

Six places NULL changes the answer

1 · `= NULL` never matches

The first place NULL changes the answer: equality cannot test it.

WHERE shipped_at = NULL      -- ALWAYS empty — even for rows where shipped_at really is NULL
WHERE shipped_at IS NULL     -- correct: IS NULL is the only operator that can see a NULL

Why the first line matches nothing

NULL means unknown, so unknown = unknown is not TRUE — it is UNKNOWN. WHERE keeps only TRUE, so the row is discarded.
It is not an error and no engine warns you. The query returns zero rows and looks like a correct answer to a question about a table that happens to be empty.
The same applies to <> NULL, > NULL and every other comparison. Only IS NULL, IS NOT NULL and IS DISTINCT FROM can see through it.

2 · `NOT IN` collapses

The second place: one NULL anywhere in the subquery empties the whole result.

-- If ANY row of the subquery is NULL, this returns ZERO rows. Silently.
WHERE id NOT IN (SELECT customer_id FROM orders)  -- customer_id nullable? empty result.
WHERE NOT EXISTS (SELECT 1 FROM orders o          -- correct, and null-safe
                  WHERE o.customer_id = c.id);    -- the correlation, again

The expansion that causes it

x NOT IN (1, 2, NULL) is shorthand for x <> 1 AND x <> 2 AND x <> NULL. The last term is UNKNOWN for every possible x, and TRUE AND UNKNOWN is UNKNOWN — so the whole conjunction is never TRUE and no row survives.
NOT EXISTS asks whether a row exists. That is a two-valued question, so there is no UNKNOWN to swallow the result.
If you must keep NOT IN, add WHERE customer_id IS NOT NULL to the subquery. It works — and it is one edit away from being wrong again the day somebody removes it.

3 · Aggregates ignore NULL — except `COUNT(*)`

The third place, and the one that quietly produces wrong reports: what each aggregate does with a NULL.

-- Four rows in the column `amount`: 10, 20, NULL, 30
COUNT(*)        -- 4    counts ROWS. Never NULL, never affected by the values.
COUNT(amount)   -- 3    counts NON-NULL VALUES of that column
SUM(amount)     -- 60   ignores the null. NULL over an empty set, though — not 0.
AVG(amount)     -- 20   = 60/3, NOT 60/4  ← the one that produces wrong reports

How to read this, and what to do about it

This is a reference list, not a runnable statement: four expressions over the same four rows, with the answer each one gives.
AVG divides by the count of non-null values. If NULL means “nothing happened” — no discount, no refund — then the honest average is over four rows, and you must write AVG(COALESCE(amount, 0)) to get it.
If NULL means “we do not know”, then skipping it is right and the default is what you want. The bug is never in the function; it is in not deciding which of the two your NULL means.
SUM over no rows at all returns NULL rather than 0. A total on a dashboard should therefore be COALESCE(SUM(x), 0).

⚠ Trap

AVG divides by the count of non-null values. If a missing rating means “no rating” that is right; if it means “zero” it is wrong and your average is inflated. Decide which, and if it is the latter write AVG(COALESCE(rating, 0)) — explicitly, where the reviewer can see the decision.

4 · `UNIQUE` does not constrain NULLs (mostly)

Two nulls are not equal, so they do not collide. PostgreSQL, Oracle and MySQL allow many null rows in a unique index; SQL Server allows only one, which is a genuine portability trap. PostgreSQL 15+ offers UNIQUE NULLS NOT DISTINCT to opt in to the SQL Server behaviour. If you need "at most one active row per customer", use a filtered/partial unique index instead of relying on null semantics:

“At most one active cart per customer” — enforced by a partial index rather than by hoping about NULLs.

CREATE UNIQUE INDEX uq_one_active_cart      -- a UNIQUE index is a constraint that is also fast
  ON carts (customer_id)                    -- the key: one entry per customer…
  WHERE status = 'active';                  -- …but only over the rows that match. PostgreSQL, SQL Server.

Why not just rely on NULL

Most engines allow many NULLs in a unique column, because two NULLs are not equal. SQL Server allows exactly one. So “make the closed carts NULL and let UNIQUE sort it out” behaves differently on two engines — and one of the behaviours is a production incident.
A partial (PostgreSQL) or filtered (SQL Server) index states the rule directly: unique among active rows. It is smaller than a full index, and it is checked by the engine on every write, which no application code can bypass.
MySQL and SQLite have no filtered index. There the portable trick is a generated column that is customer_id when active and NULL otherwise, with a unique index on it.

5 · `ORDER BY` disagrees across engines

PostgreSQL and Oracle sort nulls last ascending; SQL Server, MySQL and SQLite sort them first. If the position matters, say so: ORDER BY x NULLS LAST where supported, otherwise ORDER BY CASE WHEN x IS NULL THEN 1 ELSE 0 END, x.

6 · Concatenation and arithmetic propagate

The sixth place: NULL propagates through arithmetic and concatenation — except where a function is defined not to.

'Rossi' || NULL       -- NULL in PostgreSQL, SQL Server, SQLite: one unknown poisons the whole
                      -- (Oracle treats '' as NULL and concatenation ignores it — a real difference)
100 + NULL            -- NULL, everywhere. Unknown plus a hundred is still unknown.
CONCAT('Rossi', NULL) -- 'Rossi' — CONCAT() is DEFINED to treat NULL as an empty string

How to read this, and the practical consequence

Four expressions with the value each produces — not a statement to run.
The classic symptom: an address label built as street || ' ' || suite || ' ' || city comes out completely blank for everybody with no suite number. One NULL in the middle erases the entire string.
Two fixes: CONCAT(…), which ignores NULLs by definition, or COALESCE(suite, '') around each nullable part. Prefer the first — it says the intention rather than patching each hole.

The tools for handling it

Function	Does	Note
`COALESCE(a, b, c)`	First non-null argument	Standard, any number of arguments. The default choice
`ISNULL(a, b)` / `IFNULL` / `NVL`	Two-argument version	Dialect-specific. `ISNULL` in T-SQL also truncates to the first argument's type
`NULLIF(a, b)`	`NULL` if a = b, else a	The idiom for “treat empty string as missing”, and for guarding division by zero
`IS [NOT] DISTINCT FROM`	Null-safe comparison	PostgreSQL, SQLite. MySQL spells it `<=>`. SQL Server has no equivalent

NULLIF as a division guard: no CASE, no error, and an honest answer.

-- Divide-by-zero without a CASE, and without an exception.
SELECT delivered_count * 1.0                 -- * 1.0 forces REAL: integer division would truncate
       / NULLIF(total_count, 0) AS delivery_rate  -- 0 becomes NULL, and x / NULL is NULL
FROM daily_stats;                            -- one row per day, already aggregated
-- total_count = 0 now gives NULL ("undefined"), which is honest, rather than an error.

Two conversions doing the work

* 1.0 — without it, delivered_count / total_count is INTEGER ÷ INTEGER, so a rate of 0.83 arrives as 0. This is the single most common silent arithmetic bug in SQL reports.
NULLIF(total_count, 0) — returns NULL when the two arguments are equal. Dividing by NULL yields NULL instead of raising, so one row with no data cannot abort a report over a million rows.
Render the NULL deliberately at the edge: COALESCE(rate, 0) if a dash is unacceptable in the UI. The point is that the database says “undefined” and the presentation layer decides how to draw it.

Designing so it comes up less

🎓 Ask what the null means

NULL is overloaded in practice, and different meanings want different designs:

Not yet known — shipped_at on an unshipped order. Legitimate. Keep the null; it is exactly what the model means.
Not applicable — vat_number on a private customer. Often a sign the table is really two kinds of thing and would be better as a subtype table.
Nobody filled it in — a nullable country that everybody leaves blank. This is a missing NOT NULL and a missing default.

✔ Say this in an interview

“I try to make columns NOT NULL unless the null carries meaning, and when it does I make sure I know which meaning — unknown, inapplicable, or not yet. Most of the NULL bugs I have seen came from a column where nobody had decided.”

NULL is unknown, not empty and not zero. Every comparison with it is UNKNOWN.
WHERE keeps only TRUE, so UNKNOWN filters the row out. CHECK accepts UNKNOWN, which is the reverse.
Use IS NULL, never = NULL.
One null in a NOT IN subquery returns the empty set. Use NOT EXISTS.
COUNT(*) counts rows; COUNT(col) counts non-null values; AVG divides by the non-null count.
UNIQUE permits multiple nulls in most engines but only one in SQL Server. For “at most one active”, use a filtered unique index.
Null ordering differs by engine. State it with NULLS FIRST/LAST if it matters.
NULLIF(x, 0) is the idiomatic division guard.
Decide what a null means in each column: unknown, inapplicable, or not yet. Undecided is where the bugs live.

Interview questions this chapter answers

What is the result of NULL = NULL?

UNKNOWN, not TRUE and not FALSE. In a WHERE clause that means the row is dropped. To compare null-safely use IS NULL, or IS NOT DISTINCT FROM where the dialect supports it.

Difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows regardless of content. COUNT(column) counts rows where that column is not null. In a LEFT JOIN that difference is the whole point: COUNT(*) reports 1 for a customer with no orders, COUNT(o.id) reports 0.

Why did this NOT IN query suddenly return nothing?

A null appeared in the subquery result. x NOT IN (…, NULL) expands to a conjunction containing x <> NULL, which is UNKNOWN, so the predicate can never be true for any row. Rewrite as NOT EXISTS, which is null-safe and usually plans the same or better.