DataslopeWorking with NULL
Understand SQLite NULL values, why comparisons with NULL are tricky, and how to use IS NULL, COALESCE, and IFNULL.
NULL is SQL's way to say, "there is no known value here."
That sounds simple, but it changes how comparisons, filters, and
calculations behave. Many beginner SQL bugs come from treating NULL
like a normal value. It is not zero. It is not an empty string. It is
missing or unknown.
NULL is not zero or empty text
These three values mean different things:
0means a known number: zero.''means known text with no characters.NULLmeans no value is known.
For a phone column, an empty string could mean someone intentionally
entered a blank phone. NULL means the phone number is not known.
The blank-looking cells may not all mean the same thing. A NULL value
means SQLite does not have a known value for that cell.
What does NULL mean in SQL?
The number zero.
An empty string.
A missing or unknown value.
The word NULL stored as text.
Use IS NULL and IS NOT NULL
Because NULL means unknown, you do not test for it with =. Use the
special tests IS NULL and IS NOT NULL.
Notice that IS NOT NULL includes Linus, whose phone is an empty string.
That cell has a known value, even though the value is blank text.
Do not write = NULL
WHERE phone = NULL is a classic mistake. It does not find missing
phones, because ordinary comparisons with NULL do not evaluate to
true. Use IS NULL instead.
Comparisons with NULL are unknown
When SQLite compares a value to NULL, the answer is usually NULL,
not true and not false. You can think of SQL logic as having three
possible outcomes: true, false, and unknown.
NULL IS NULL is true, so SQLite shows 1. The equality comparisons
produce NULL, often displayed as a blank cell.
Why does WHERE phone = NULL fail to find missing phone numbers?
NULL is the same as an empty string.
Comparing with NULL gives unknown, not true.
SQLite does not allow NULL in tables.
The phone column must be numeric.
NULL can propagate through expressions
A calculation involving an unknown value usually has an unknown result. If you do not know someone's reward points, you also do not know their reward points plus 5.
This behavior is useful because it avoids pretending to know an answer when part of the answer is missing.
Linus has NULL reward points, so adding 5 still gives NULL.
Use COALESCE for fallback values
COALESCE returns the first value in its list that is not NULL. It is
one of the most useful tools for displaying or calculating with missing
values.
COALESCE(phone, 'No phone on file') means: use phone if it is known;
otherwise use the fallback text.
This query treats missing reward points as 0 for the purpose of the calculation. That may or may not be the right business choice, but the query is explicit about it.
IFNULL is a shorter two-value form
SQLite also provides IFNULL(value, fallback). It is similar to
COALESCE, but it takes exactly two arguments. COALESCE can take two
or more.
For beginners, COALESCE is a great default because it is standard SQL
and can handle more than two choices.
Check your understanding
Which condition correctly finds rows where phone is missing?
phone = NULL
phone = ''
phone IS NULL
phone LIKE NULL
What does NULL = NULL evaluate to?
true
false
NULL, meaning unknown
The number zero
What happens to 5 + NULL in SQLite?
It returns 5.
It returns 0.
It returns NULL.
It causes the table to be deleted.
What does COALESCE(phone, 'No phone') return when phone is NULL?
NULL
'No phone'
The text COALESCE
An empty string every time
How is IFNULL(value, fallback) different from COALESCE(value, fallback, another_fallback)?
IFNULL can take many fallback choices.
IFNULL takes exactly two arguments, while COALESCE can check two or more.
COALESCE only works with numbers.
IFNULL changes NULLs stored in the table permanently.