NULL is SQL’s way of representing the total absence of a value in a given cell — not zero, not an empty string, but a blank spot where no data was ever recorded for that row. This tutorial has already come up in nearly every other guide in this series: the WHERE clause guide, the JOIN guides, the ORDER BY guide, always as a side note or a warning about an edge case. It deserves better than footnote treatment, so here it is, pulled together into one complete reference.
What NULL Actually Represents
NULL marks the explicit absence of a value for a specific cell in a specific row — it isn’t zero, and it isn’t an empty string of text.
The distinction matters more than it first appears. A numeric column holding zero still holds a value: zero. A text column holding an empty string still holds a value too: a real piece of text data, just one with no characters in it. A column holding NULL has neither of these things recorded — nothing at all was entered for that row.
Here are the common, legitimate reasons NULL shows up in real data: an optional field a user skipped (a middle name box, say, for someone who doesn’t have one), information that simply isn’t known or applicable yet (a project’s completion date before the project has wrapped up), or a value produced by a JOIN where no matching row existed on the other side (covered in the JOIN tutorials, where a LEFT JOIN fills in NULL for whatever side has no match).
Why You Cannot Use Equals to Check for NULL
This is the single most important rule about NULL, touched on briefly in the WHERE clause tutorial, and it’s worth spelling out clearly here since everything else in this tutorial builds on it.
Writing “column equals NULL” doesn’t behave the way it looks like it should. SQL runs on three-valued logic for comparisons: any comparison can resolve to true, false, or unknown, rather than the two options (true or false) that typical programming logic trains you to expect. Comparing anything to NULL with equals always lands on that third “unknown” result rather than a clean true or false, because NULL stands in for an unknown quantity, and measuring something against an unknown quantity can’t produce a definite yes-or-no answer.
The correct, purpose-built syntax for checking NULL is IS NULL (to test for the explicit absence of a value) and IS NOT NULL (to test for the presence of any real value) — not equals or not-equals.
How NULL Behaves in Calculations
Any arithmetic operation touching a NULL value — addition, subtraction, multiplication, division — returns NULL, no matter what the other values in that calculation happen to be. Add any number you like to NULL and you get NULL back, not that number, since NULL stands for an unknown quantity, and combining an unknown with a known through arithmetic leaves you with something still fundamentally unknown.
This has a specific consequence worth flagging for aggregate functions, covered in the GROUP BY tutorial. SUM, AVG, MIN, and MAX all quietly skip NULL values rather than letting one stray NULL poison the entire result into an overall NULL. Sum a column that mixes real numbers with some NULLs, and the function totals up only the non-NULL numbers, treating the NULL rows as though they weren’t part of the calculation at all.
COUNT behaves differently depending on exactly how you use it, and this trips people up constantly. COUNT applied to an asterisk counts every row, period, including ones where the relevant column is NULL, because it’s counting rows themselves rather than inspecting any particular column’s contents. COUNT applied to one named column instead counts only the rows where that column is NOT NULL, leaving out anything with no value there. That means COUNT(*) and COUNT(some_column) can return different numbers from the very same table, whenever that column has any NULLs in it at all — a result that catches people off guard, since it’s easy to assume both versions of COUNT should always agree.
Using COALESCE to Provide a Default Value
COALESCE takes a list of values and hands back the first one that isn’t NULL, scanning through your list in the order you provide it. It’s the go-to tool for showing a sensible fallback in your results instead of a blank or an unhelpful NULL.
Say you have a phone number column that’s sometimes NULL, and you’d rather display “Not provided” than a blank whenever that value is missing. You’d write COALESCE, then the phone number column, then a comma, then the text “Not provided” — the two candidates COALESCE picks between. When the phone number column holds a real value, COALESCE returns that number. When it’s NULL, COALESCE moves past it and returns your fallback text instead.
COALESCE isn’t limited to two values — it can work through several fallback options in sequence, returning the first non-NULL one it hits as it scans your list from left to right.
NULLIF: The Reverse Operation
NULLIF runs in the opposite direction from COALESCE. Instead of swapping a NULL for some fallback, it turns a specific value into NULL whenever that value matches a comparison you give it.
A typical use case: keeping a division from throwing an error when the divisor turns out to be zero (dividing by zero is a math error most database systems will flatly reject, stopping your query cold). Wrap your divisor column in NULLIF, compare it against zero, and any actual zeros in that column get converted to NULL before the division ever runs. Since dividing anything by NULL just produces NULL — following the calculation rule above — rather than triggering a divide-by-zero error, this trick lets your query keep running, returning NULL for the problem rows instead of halting entirely.
NULL Behavior in JOIN Operations
As the JOIN tutorials cover in detail, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN all fill in NULL for the columns belonging to whichever table had no matching row for that particular result. That’s exactly how you spot, after running an outer JOIN, which rows had no counterpart on the other side: check for IS NULL on a column you know belongs to the table that might be missing a match.
NULL Behavior With IN and NOT IN
The WHERE clause tutorial mentioned this in passing, but it’s worth restating in full given how much trouble it causes: if a list you hand to IN or NOT IN happens to contain a NULL anywhere (easy to end up with if that list comes from a subquery instead of values you typed by hand), NOT IN can behave in a surprising way — potentially returning zero rows for the whole query, even for rows you’d expect to match your condition without question.
The cause is the same three-valued logic from earlier: comparing a value against NULL with NOT IN’s underlying not-equals logic produces “unknown” rather than a clean true, and SQL’s rules for combining multiple comparisons (which is effectively what NOT IN does under the hood, checking against every item in your list) require every single comparison to come back definitively true for the overall condition to hold. One unknown result buried anywhere in that list can quietly sink the whole condition for every row, rather than simply being ignored the way intuition might suggest.
The defensive habit worth building: whenever your IN or NOT IN list comes from a subquery instead of values you’ve typed and confirmed yourself, check that subquery for NULLs and filter them out before trusting NOT IN against the result.
A Quick Reference for NULL-Related Functions and Behaviors
| Situation | Correct Approach |
|---|---|
| Checking if a column has no value | Use IS NULL, never equals NULL |
| Checking if a column has any value | Use IS NOT NULL, never not-equals NULL |
| Providing a fallback for a NULL value | Use COALESCE with your desired default |
| Converting a specific value into NULL | Use NULLIF, often to prevent divide-by-zero errors |
| SUM, AVG, MIN, MAX with NULLs present | These automatically ignore NULL, no special handling needed |
| COUNT of asterisk vs COUNT of a column | Asterisk counts all rows; a named column excludes NULLs in that column |
| NOT IN with a subquery-derived list | Check for NULLs in that list first, since they can silently break the entire condition |
Why This Single Concept Deserves Its Own Dedicated Tutorial
NULL isn’t some rare edge case that only matters in unusual situations — it’s a foundational behavior woven through nearly every other SQL concept in this series, from basic filtering, through JOINs, through aggregate functions, through sorting. Getting a firm handle on NULL specifically heads off a meaningful share of the confusing, hard-to-trace bugs that beginners run into, and honestly, experienced practitioners too, the first time they hit a NULL edge case they hadn’t seen before.
If you take away just one thing from this tutorial, make it this: whenever a query surprises you — too few rows, a stray NULL where you didn’t expect one, a calculation returning NULL when you wanted an actual number — checking for NULL involvement somewhere in the logic is one of the fastest, most reliable diagnostic steps you have, precisely because NULL is consistently the piece that doesn’t behave the way ordinary, non-database intuition would predict.
What specific NULL-related behavior are you running into — an unexpected result, a calculation producing NULL, or a NOT IN condition behaving strangely? Describe your situation and I can help pinpoint exactly which NULL behavior covered here applies.