NULL represents an unknown, missing, or not-applicable value — it is not zero, not an empty string, and not false, and it behaves differently from all three in comparisons.
Why This Matters
NULL is one of the most consistently misunderstood ideas in SQL, and it causes real, silent bugs — queries that look obviously correct can quietly return the wrong answer purely because of how NULL compares against other values.
Real World Story
A query filters WHERE age != 25, expecting it to return everyone except 25-year-olds — including anyone whose age was never recorded. It doesn't. Every row where age is NULL is silently excluded from BOTH age = 25 and age != 25, because NULL doesn't equal or not-equal anything, including itself. The query isn't broken syntactically — it's just answering a subtly different question than the one that was asked.
Core Concept
- NULL means "this value is unknown, missing, or doesn't apply" — it is explicitly not the same as zero, an empty string, or false, even though all four can look similar at a glance.
- Comparing anything to NULL using = or != doesn't return true or false — it returns NULL (meaning "unknown"), which is why NULL rows silently vanish from both a filter and its exact opposite.
Deep Dive
- The only correct way to check for NULL is IS NULL or IS NOT NULL — never = NULL or != NULL, both of which will silently fail to match anything, including actual NULLs.
- NULL also behaves specially in calculations: any arithmetic involving NULL produces NULL (5 + NULL is NULL, not 5), and most aggregate functions (SUM, AVG) simply skip NULL values rather than treating them as zero.
- A column allows NULL by default unless it's explicitly marked NOT NULL — you'll meet that constraint formally in the Constraints section.
Common Mistakes
Writing WHERE column = NULL (or != NULL) expecting it to match NULL values.
Why: NULL never equals anything using =, including another NULL — the comparison always evaluates to unknown, not true, so the row is excluded either way.
Fix: Use IS NULL or IS NOT NULL specifically — they're the only operators designed to test for NULL correctly.
Assuming NULL means the same thing as zero or an empty string.
Why: A NULL price means "we don't know the price"; a zero price means "this costs nothing" — treating them as interchangeable silently changes what a query or a calculation actually means.
Fix: Reserve NULL specifically for genuinely unknown or not-applicable values, and use an explicit 0 or empty string when that's what's actually meant.
Interview Questions
What does NULL mean in a database?
It represents an unknown, missing, or not-applicable value — it's distinct from zero, an empty string, or false, all of which represent a known value.
Why does WHERE age != 25 not return rows where age is NULL?
Because comparing NULL to anything with = or != returns NULL (unknown), not true or false — so a row with a NULL age fails the filter condition either way, and is excluded from both the match and its opposite.
Why do most aggregate functions like SUM and AVG ignore NULL values instead of treating them as zero?
Because NULL means "unknown", and treating an unknown value as zero would silently misrepresent the data — for example, AVG treating missing scores as zero would pull the average down incorrectly, when the honest answer is that those scores simply aren't known.
Best Practices
Do
✓Always use IS NULL / IS NOT NULL to test for NULL — never = or !=.
✓Reserve NULL for values that are genuinely unknown or not applicable, not as a stand-in for zero or empty.
✓Double-check how your query behaves when a column can be NULL, especially with NOT and != conditions.
Don't
✗Don't write column = NULL or column != NULL — both silently fail to do what they look like they do.
✗Don't assume aggregate functions treat NULL as zero — check each function's specific NULL-handling behavior.
✗Don't use NULL as a placeholder for "zero" or "empty" just because it's convenient — it changes the meaning of the data.
Chapter Summary
NULL means unknown, missing, or not applicable — never zero, empty, or false. Its comparison behavior (never equal to anything, including itself) is the source of a huge share of real SQL bugs, and IS NULL / IS NOT NULL are the only correct way to test for it.