DataslopeWorking with NULL
The special value that means "unknown," why it behaves so strangely, and how to test for it correctly — a concept that trips up nearly every beginner.
NULL is one of the most important — and most misunderstood — ideas
in SQL. It is the source of countless beginner bugs. Slow down here:
a clear understanding of NULL will save you real confusion later,
which is why this page has extra checks at the end.
NULL means "unknown," not "zero" or "empty"
A cell can be NULL, which means there is no value here — it is
unknown or missing. This is not the same as zero, and not the
same as an empty string '':
0is a known quantity: zero dollars.''is a known piece of text: the empty string.NULLis the absence of any value: we simply do not know.
Imagine a phone column. A customer with phone = '' told us their
phone is blank; a customer with phone = NULL never told us their
phone at all. Different meanings — and databases keep them distinct.
The surprising part: NULL is "contagious"
Because NULL means "unknown," any calculation involving it usually
produces NULL — because the answer is genuinely unknown. If you do
not know someone's age, you also do not know "their age plus one."
Look closely at the third column. Even NULL = NULL is not true
— it comes back as NULL (shown as blank), because "is one unknown
equal to another unknown?" cannot be answered. This is the trap that
catches everyone.
Testing for NULL: use IS NULL, never = NULL
Since = NULL does not work, SQL gives you special tests:
IS NULL— true when the value is missing.IS NOT NULL— true when the value is present.
WHERE phone = NULL returns nothing
A very common bug: writing WHERE phone = NULL. It does not error —
it just silently matches no rows, because the comparison is never
true. Always use IS NULL / IS NOT NULL to test for missing
values.
Filling in NULLs with COALESCE
Often you want to display a friendly fallback instead of a blank.
COALESCE(a, b) returns the first argument that is not NULL:
COALESCE reads as "use the phone, or if that is NULL, use this
text instead." It is the standard, clean way to handle missing
values for display or calculation.
Why allow NULL at all?
If NULL is so tricky, why have it? Because "unknown" is a real
and honest state. When a customer has not given a phone number,
forcing in a fake 0 or '' would lie about the data. NULL
lets the database tell the truth: "we do not know." The strange
behavior is the price of that honesty — and IS NULL plus
COALESCE make it manageable.
Check your understanding
What does NULL mean in SQL?
The number zero.
An empty string of text.
That the value is unknown or missing — there is no value present.
A syntax error in the query.
What does the expression NULL = NULL evaluate to?
true, because both sides are the same.
false, because NULL never equals anything.
NULL (unknown), because comparing two unknown values has no knowable answer.
It causes the query to crash.
How do you correctly select rows where the phone column has no value?
WHERE phone = NULL
WHERE phone = ''
WHERE phone IS NULL
WHERE phone = 'NULL'
What does COALESCE(phone, 'No phone') return for a row where phone is NULL?
NULL, because the first argument is NULL.
An error, because the arguments differ.
'No phone', because COALESCE returns the first argument that is not NULL.
The literal word "COALESCE".
Why does SQL bother having NULL instead of just storing 0 or '' for missing data?
Because NULL saves disk space compared to other values.
Because "unknown" is a genuinely different state from zero or empty, and NULL lets the data stay honest about what is truly missing.
Because NULL makes queries run faster.
Because every column is required to allow NULL.