🗄 SQL & Databases · Chapter 3

NULL: the value that isn't there

NULL is not zero, not empty, not false. It is 'unknown' — and unknown behaves differently from everything else in SQL.

Study this lesson free in the app →

The basics

NULL is SQL's marker for a missing value — not zero, not an empty string, not false, but unknown.

The problem it solves (and creates): real data has holes. A cash ride's fare was never recorded; a completed ride has no cancellation reason; an outer join finds no match. SQL needs a way to say "no value here", and NULL is it. The cost: NULL follows its own rules, and none of them raise an error. They just quietly change your counts, averages and filters.

The mental model: a blank on a form. Someone who writes 0 told you a fact; someone who left it blank told you nothing. Anything computed from a blank is also blank — NULL + 100 is NULL. And asking "is this blank equal to 5?" gets the answer I don't know, which SQL calls unknown. WHERE keeps only rows that are true, so unknown rows vanish.

Explained

NULL = NULL          -- unknown, not true
NULL != NULL         -- also unknown
NULL + 100           -- NULL
'ride_' || NULL      -- NULL
count(fare)          -- silently skips them
WHERE fare != 100    -- silently excludes them

Six behaviours, none of which raise an error, all of which quietly change your numbers.

NULL causes more wrong-but-plausible results than any other feature of SQL. It deserves its own lesson before you write another query.

The blank on the form

Two people fill in a survey asking their monthly income.

One writes 0. They earn nothing — that's a fact you now know.

One leaves it blank. Maybe they earn nothing, maybe lakhs, maybe they didn't understand the question. You know nothing.

Now compute the average income. What do you do with the blank? Treat it as 0 and you invent a fact. Skip it and your average describes only people who answered.

Neither is wrong — but they're different numbers, and SQL picks one for you by default. It skips. If that isn't what you meant, nothing will tell you.

That's NULL: not a value, a hole where a value should be. And the hole is contagious — anything you compute with it becomes unknown too.

Three-valued logic

Most languages have true and false. SQL has true, false and unknown, and unknown is what comparisons with NULL produce.

  NULL = 5      → unknown
  NULL != 5     → unknown
  NULL = NULL   → unknown
  NULL IS NULL  → TRUE      ← the only way to test

WHERE keeps only rows where the condition is true. Unknown is discarded exactly like false — which is why a filter that looks complete silently isn't.

The combination rules are worth knowing because they're not symmetric:

  true  AND unknown → unknown
  false AND unknown → FALSE      ← false wins
  true  OR  unknown → TRUE       ← true wins
  false OR  unknown → unknown
  NOT unknown       → unknown

So WHERE (a = 1 OR b = 1) still matches rows where b is NULL, provided a = 1 is true. But WHERE (a = 1 AND b = 1) drops them.

The tools:

fare IS NULL / IS NOT NULL          -- the only correct test
coalesce(fare, 0)                   -- first non-null argument
nullif(fare, 0)                     -- turn 0 back into NULL (guards division)
case when fare is null then 'unknown' else 'known' end
fare IS DISTINCT FROM 100           -- NULL-safe !=, treats NULL as a value

The filter that lost 300 rides

A table of 10,000 rides. 300 have status = NULL because a producer bug stopped setting it.

An analyst wants everything that isn't cancelled:

SELECT count(*) FROM rides WHERE status != 'cancelled';

The result is 7,700. But count(*) where status is 'cancelled' is 2,000.

Where NULLs come from, and what to do

NULLs arrive four ways, and the right response differs for each.

1. Genuinely unknown — a cash ride whose fare was never recorded. Keep the NULL. Inventing a zero is inventing data.

2. Not applicable — a cancelled_reason on a completed ride. Also correctly NULL.

3. From an OUTER JOIN — no matching row on the right, so its columns are NULL. Expected and meaningful: it tells you the match failed.

4. A bug or missing default — the producer stopped setting a field. This one you want to alert on, not paper over with coalesce.

The common mistake is treating all four the same way by wrapping everything in coalesce(x, 0). That converts a data-quality problem into a plausible wrong number — the worst possible trade.

Reasonable defaults:

coalesce(fare, 0)          -- ✗ usually wrong for money: hides missing revenue
coalesce(count, 0)         -- ✓ usually right: no rows genuinely means zero
coalesce(city, 'unknown')  -- ✓ good: keeps the row visible in a GROUP BY

That last one matters — grouping by a nullable column puts all NULLs into a single group labelled NULL, which is easy to miss on a dashboard. Labelling it 'unknown' makes it visible.

The NOT IN that returned nothing

A query to find rides whose driver has left the platform:

SELECT * FROM rides
WHERE driver_id NOT IN (SELECT driver_id FROM active_drivers);

It returns zero rows. There are definitely departed drivers.

The cause: one row in active_drivers has a NULL driver_id.

NOT IN expands to driver_id != a AND driver_id != b AND driver_id != NULL … — and that last comparison is unknown. With AND, unknown poisons the whole chain to unknown, so no row is ever true, so nothing is returned.

One NULL in a subquery silently emptied the result.

The fix — use NOT EXISTS, which is immune:

SELECT * FROM rides r
WHERE NOT EXISTS (
  SELECT 1 FROM active_drivers a WHERE a.driver_id = r.driver_id
);

NOT EXISTS asks "did any row match?" — a genuine yes/no, with no three-valued logic involved.

The rule worth adopting permanently: prefer NOT EXISTS over NOT IN on any column that could be NULL. It is never worse, and it cannot produce this failure.

NULL is unknown, not zero. Nothing equals it — use IS NULL.

NULL is contagious: any arithmetic or comparison containing it becomes unknown.

Negative filters (!=, NOT IN) silently drop NULLs. Prefer NOT EXISTS.

Gotchas

• = NULL — always unknown, returns nothing. Use IS NULL
• NOT IN with a nullable subquery — returns zero rows, always. Use NOT EXISTS
• != on a nullable column — silently excludes the NULLs
• coalesce(fare, 0) on money — converts missing revenue into real-looking zero revenue
• Grouping by a nullable column — all NULLs collapse into one unlabelled group that's easy to miss
• sum() over all NULLs returns NULL, not 0 — and it propagates downstream
• Unique constraints — most engines allow multiple NULLs in a unique column, since no two NULLs are equal
• ORDER BY with NULLs — placement differs by engine. Say NULLS FIRST or NULLS LAST explicitly
• Concatenation — 'ride_' || NULL is NULL, not 'ride_'. Coalesce first

Words to know

In two minutes

• NULL means unknown, not zero or empty; it is a hole where a value should be.
• Any comparison with NULL yields unknown, and WHERE keeps only true rows — so = NULL and != x silently drop NULLs.
• Three-valued logic: false AND unknown is false, true OR unknown is true, NOT unknown is still unknown.
• NULL is contagious in arithmetic and concatenation; aggregates other than count(*) skip it.
• One NULL in a NOT IN subquery returns zero rows; default to NOT EXISTS.
• NULLs come from genuine unknowns, not-applicable fields, outer joins and bugs — alert on the bugs instead of coalescing them away.
• coalesce counts to 0, label NULL groups 'unknown', but never coalesce missing money to 0; check that your buckets add up to the total.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.