Step-by-step lessons

Missing, Unknown, and What to Do About It

Four short lessons: what NULL actually means, why equality never works on it, how "unknown" spreads through a query and drops rows you wanted, and how to substitute something readable.

1

NULL Is the Absence of a Value

NULL is not zero. It is not an empty string. It is not false. It is a marker meaning there is no value here, and the difference matters because zero is an answer and NULL is the absence of one.

A column that is allowed to hold NULL is called nullable — the schema panel in the Lab tags them for you. In this database users.company is nullable, because plenty of people sign up without one, and subscriptions.cancelled_on is nullable because a subscription that is still running has no cancellation date.

Ask what the NULL means here

"Not supplied", "not applicable" and "not yet" are three different facts, and databases store all three the same way. A NULL in company means the user has no company. A NULL in cancelled_on means the subscription is active — the absence of a date is itself the information you want.

Not the same

`0` and `''`

Both are values. Something is recorded, and it happens to be zero or empty.

Different

`NULL`

Nothing is recorded. There is no answer to compare against.

Quick check

In the subscriptions table, what does a NULL in cancelled_on tell you?

2

= NULL Never Matches Anything

This is the single most important rule on the page. WHERE company = NULL does not return the rows with no company. It returns nothing at all, and it does not error.

The reason follows from what NULL means. Asking "is this unknown value equal to that unknown value" cannot be answered yes or no — the honest answer is unknown. And WHERE only keeps rows where the condition is definitely true, so unknown rows are dropped. Even NULL = NULL is unknown.

WHERE company = NULL       -- always zero rows, no error
WHERE company IS NULL      -- the 5 users with no company
WHERE company IS NOT NULL  -- the other 7

IS NULL and IS NOT NULL are dedicated operators that exist precisely because equality cannot do this job. They are the only correct way to test for presence or absence.

Why this one is dangerous

A filter that errors gets fixed in thirty seconds. A filter that silently returns zero rows gets interpreted — "there must not be any" — and shipped. That is the failure mode here, and it is why this rule is worth over-learning rather than half-remembering.

Quick check

What does SELECT COUNT(*) FROM users WHERE company = NULL return?

3

NULL Is Contagious

Anything computed from an unknown value is itself unknown. 5 + NULL is NULL. NULL > 3 is unknown. 'a' || NULL is NULL. Once a NULL enters a calculation the result carries it out the other side.

The consequence that catches people is in filters that look like they cover everything:

WHERE referred_by <> 1

You would read that as "everyone not referred by user 1". It is not. The four users with referred_by IS NULL are silently dropped, because for them the comparison is unknown rather than true. Together with the previous query WHERE referred_by = 1, the two results do not add up to the whole table — which is the symptom to watch for.

WHERE referred_by <> 1 OR referred_by IS NULL

NOT does not rescue it either: NOT unknown is still unknown. Nor does IN — x IN (1, 2) where x is NULL is unknown, and x NOT IN (subquery containing a NULL) is a famous way to get zero rows out of a query that should have returned plenty.

+

Arithmetic

amount + NULL is NULL, not amount.

>

Comparison

Any comparison with NULL is unknown, so the row is filtered out.

!

NOT

NOT unknown is unknown. Negation does not fix it.

Σ

Aggregates

SUM and AVG skip NULLs entirely, which changes the denominator.

Quick check

12 users. WHERE referred_by = 1 returns 2. WHERE referred_by <> 1 returns 6. Where are the other 4?

4

COALESCE Supplies a Fallback

COALESCE returns the first of its arguments that is not NULL. It takes as many as you like and reads them left to right.

SELECT name,
       COALESCE(company, 'Individual') AS company
FROM users;

That is the display use, and it is uncontroversial: a report full of blank cells is a report someone will email you about, and "Individual" is a truer label than an empty box.

Do not use it to make a problem disappear

COALESCE(amount, 0) in the middle of a calculation turns "we do not know what this order was worth" into "this order was worth nothing", and the total that comes out looks perfectly reasonable. Substitute at the edge of a report, where a human can see the substitution. Inside a calculation, decide what the NULL means first.

Two relatives worth recognising. NULLIF(a, b) returns NULL when the two are equal — the standard trick for avoiding a divide by zero, as x / NULLIF(y, 0). And IFNULL in SQLite (or ISNULL in SQL Server) is a two-argument COALESCE; the portable one is COALESCE.

Quick check

What does COALESCE(company, nickname, 'Unknown') return for a user whose company is NULL and nickname is 'Ada'?

Write it yourself

Query Lab

Four queries about the value that is not a value. NULL breaks the intuitions you built in the last four stages, and every one of these exercises is a place it does so.

0 of 4 cleared

Real SQLite runs right here in your browser — nothing to install, nothing sent to a server. The database is rebuilt from scratch before every single run, so nothing you write can break it and nothing carries over between exercises.

The Northwind Analytics database A small SaaS product's database: who signed up, what they subscribed to, what they bought, and what they did in the app.

Loading the tables…

01

Find the missing values

To do

NULL means "no value here". It is not zero, and it is not an empty string — it is the absence of an answer, and in this database it is how a user with no company is stored.

The one rule that matters: nothing equals NULL, including NULL. WHERE company = NULL is not false, it is unknown, so no row ever passes it. There is a dedicated operator instead:

WHERE company IS NULL

Your task: return name and company for every user with no company recorded.

query.sql

SELECT name, company
FROM users
WHERE company = NULL;

SQLite

Hint

The starter query is the classic mistake and returns nothing at all. Swap the = NULL for IS NULL.

Output

02

Find the values that are present

To do

IS NOT NULL is the other half. In the subscriptions table, cancelled_on is NULL while a subscription is still running and holds a date once it has been cancelled — so the presence of a value is the fact you care about.

This pattern is everywhere: deleted_at, completed_at, churned_on. A nullable timestamp is how databases usually record "this thing happened, and here is when".

Your task: return user_id, plan and cancelled_on for every subscription that has been cancelled.

query.sql

SELECT user_id, plan, cancelled_on
FROM subscriptions
WHERE

SQLite

Hint

WHERE cancelled_on IS NOT NULL — three subscriptions have a cancellation date.

Output

03

Substitute something readable

To do

A report full of blank cells is a report someone will email you about. COALESCE returns the first of its arguments that is not NULL, which makes it the standard way to supply a fallback:

COALESCE(company, 'Individual')

It takes as many arguments as you like and walks them left to right, so COALESCE(a, b, 'unknown') tries two columns before giving up. Use it for display; do not use it to hide a NULL you have not understood yet.

Your task: return every user's name and their company, with Individual shown where there is none. Name that second column company.

query.sql

SELECT name,
FROM users;

SQLite

Hint

COALESCE(company, 'Individual') AS company — the alias matters, since without it the column is headed by the whole expression.

Output

04

NULL spreads

To do

NULL is contagious through arithmetic. Anything computed from an unknown value is itself unknown, so 5 + NULL is NULL and NULL > 3 is neither true nor false.

That is why a filter on a nullable column silently drops rows. This query does not return everyone whose referrer is not user 1 — the four users with no referrer at all vanish, because for them the comparison is unknown:

WHERE referred_by <> 1

Your task: return name and referred_by for every user who was not referred by user 1 — including the four who were not referred by anybody. Sort by user_id.

query.sql

-- This misses the four users whose referred_by is NULL. Fix it.
SELECT name, referred_by
FROM users
WHERE referred_by <> 1
ORDER BY user_id;

SQLite

Hint

You need both cases: WHERE referred_by <> 1 OR referred_by IS NULL. COALESCE(referred_by, 0) <> 1 works too.

Output

Boss round

Assignment: The Subscription Status Board

One graded query. Read a NULL as a fact about the world rather than as a missing character.

This one is graded. You can see 2 of the checks up front, and 2 stay hidden until you run. The hidden ones test the same job from angles you have not been shown, so code that genuinely solves the problem clears this and code shaped around the visible examples does not. Pass them all and the stage is yours.

The subscription status board

To do

Support want a board showing every subscription and whether it is still running. In this table that fact is not stored as a flag — it is stored as the presence or absence of a cancellation date, and your query has to turn that into something a human can read.

Your task: from subscriptions, return four columns named exactly:

user_id   the user
plan      the plan
mrr       the monthly revenue
status    the cancellation date, or the word 'active' where there is none

All ten subscriptions, sorted by user_id. No blank cells.

query.sql

-- status: the cancellation date, or 'active' where there is not one.
SELECT user_id, plan, mrr,
FROM subscriptions

SQLite

Hint

COALESCE(cancelled_on, 'active') AS status, then ORDER BY user_id. The fallback is text, so it needs single quotes.

Output

Lock it in

Guess the Term

Read the clues and name the concept. The fewer clues you need, the more points you score.

Round 1 Score 0

Keep it handy

NULL Quick Reference

The whole stage on one screen.

Testing for NULL

`IS NULL`

The only correct test for absence.

`IS NOT NULL`

The only correct test for presence.

`= NULL`

Zero rows, every time, with no error.

How it spreads

+

Arithmetic

Any NULL operand makes the whole expression NULL.

?

Comparison

Produces unknown, and WHERE keeps only true.

!

NOT

NOT unknown is still unknown.

Substituting

1

`COALESCE(a, b, 'x')`

First non-NULL argument, left to right.

2

`NULLIF(a, b)`

NULL when equal. Guards a divide by zero.

3

At the edge only

Substitute for display, not inside a calculation.

Traps

1

`<>` drops NULLs

Add OR col IS NULL when you want them.

2

`NOT IN` with a NULL

Returns nothing at all. A classic.

3

AVG skips NULLs

The denominator is the count of non-NULL values, not of rows.

Sign in to train

Not unlocked yet

NULL and Three-Valued Logic

Missing, Unknown, and What to Do About It

NULL Is the Absence of a Value

Ask what the NULL means here

0 and ''

NULL

Quick check

= NULL Never Matches Anything

Why this one is dangerous

Quick check

NULL Is Contagious

Arithmetic

Comparison

NOT

Aggregates

Quick check

COALESCE Supplies a Fallback

Do not use it to make a problem disappear

Quick check

Query Lab

Find the missing values

Find the values that are present

Substitute something readable

NULL spreads

Assignment: The Subscription Status Board

The subscription status board

Guess the Term

NULL Quick Reference

Testing for NULL

IS NULL

IS NOT NULL

= NULL

How it spreads

Arithmetic

Comparison

NOT

Substituting

COALESCE(a, b, 'x')

NULLIF(a, b)

At the edge only

Traps

<> drops NULLs

NOT IN with a NULL

AVG skips NULLs

Notification

`0` and `''`

`NULL`

`IS NULL`

`IS NOT NULL`

`= NULL`

`COALESCE(a, b, 'x')`

`NULLIF(a, b)`

`<>` drops NULLs

`NOT IN` with a NULL