NULL appears in nearly every tutorial in this series so far, in the WHERE clause guide, in the JOIN guides, in the ORDER BY guide, each time as a specific edge case that trips people up. This tutorial pulls all of that scattered NULL knowledge into one single, complete reference, since NULL genuinely deserves dedicated treatment rather than only appearing as a footnote within other topics.
What NULL Actually Represents
NULL represents the explicit absence of a value — not zero, not an empty string of text, but a genuine “we do not have any value here at all” state for that specific cell in that specific row.
This distinction matters more than it might initially seem. A numeric column containing zero genuinely has a value: zero. A text column containing an empty string genuinely has a value: an empty string, which is a real, specific piece of text data, just one with no characters in it. A column containing NULL has neither of these — it has no value of any kind recorded at all for that particular row.
Common genuine reasons NULL appears in real data: an optional field that a user simply did not fill in (a middle name field, for example, for someone without a middle name), a piece of information that genuinely is not yet known or applicable (a project’s completion date, before that project has actually been completed), or a value resulting from a JOIN where no matching row existed on the other side (as covered in the JOIN tutorials, where a LEFT JOIN produces NULL for any unmatched side).
Why You Cannot Use Equals to Check for NULL
This is the single most important NULL-related rule, already covered briefly in the WHERE clause tutorial, but worth restating clearly here as the foundation everything else in this tutorial builds on.
Writing a column equals NULL does not work the way it visually appears like it should. SQL uses what is called three-valued logic for comparisons: any comparison can result in true, false, or unknown, rather than just the two values (true or false) you might be used to from typical programming logic. Comparing anything against NULL using equals specifically produces this third “unknown” result, rather than a clean true or false, precisely because NULL represents an unknown quantity, and comparing against an unknown quantity cannot logically produce a definitively true or false answer.
The correct, dedicated syntax for checking NULL is IS NULL (to check for the explicit absence of a value) and IS NOT NULL (to check for the presence of any actual value), rather than equals or not-equals.
How NULL Behaves in Calculations
Any arithmetic calculation involving a NULL value — addition, subtraction, multiplication, division — produces NULL as the result, regardless of what the other values involved in that calculation actually are. Adding any specific number to NULL produces NULL, not that specific number, since NULL represents a genuinely unknown quantity, and an unknown quantity combined with a known quantity through arithmetic remains fundamentally unknown.
This has a specific, important practical implication for aggregate functions covered in the GROUP BY tutorial. SUM, AVG, MIN, and MAX all specifically ignore NULL values when calculating their result, rather than letting a single NULL value contaminate the entire calculation into producing an overall NULL result. If you SUM a column containing some NULL values mixed in with actual numbers, the SUM function calculates the total of just the actual non-NULL numbers present, simply skipping over any NULL values entirely as if those specific rows were not part of the calculation at all.
COUNT behaves differently depending on exactly how you use it, which is a genuinely common source of confusion. COUNT applied to an asterisk (counting every row regardless of content) counts every single row, including ones where the relevant column might be NULL, since it is counting rows themselves rather than checking any specific column’s content. COUNT applied to one specific named column instead only counts rows where that particular column is NOT NULL, specifically excluding any rows where that column has no value. This distinction means COUNT of asterisk and COUNT of a specific column can produce genuinely different numbers from the exact same table, if that specific column contains any NULL values at all, which surprises people who assume both versions of COUNT should always produce an identical result.
Using COALESCE to Provide a Default Value
COALESCE is a function that takes multiple values as input and returns the first one that is not NULL, moving through your provided list in order, which is genuinely useful for displaying a sensible fallback value rather than a blank or unhelpful NULL in your actual results.
For example, if you have a phone number column that is sometimes NULL, and you want to display “Not provided” instead of a blank result whenever that specific value is NULL, you would write COALESCE, followed by the phone number column, followed by a comma, followed by the text “Not provided”, as the two values COALESCE chooses between. If the phone number column genuinely has a value, COALESCE returns that actual phone number. If it is NULL, COALESCE returns your specified fallback text instead, since that NULL phone number is skipped over in favor of the next non-NULL value provided in the list.
COALESCE can accept more than just two values, checking through several potential fallbacks in sequence, returning the first genuinely non-NULL one it encounters as it moves through your provided list of candidate values from left to right.
NULLIF: The Reverse Operation
NULLIF works in the opposite conceptual direction from COALESCE. Rather than replacing a NULL with some specific fallback value, NULLIF specifically converts a particular value into NULL, if that value matches a comparison you specify.
A common use case: preventing a division calculation from producing an error when a divisor happens to be zero (dividing by zero is a mathematical error that most database systems will reject, halting your query with an error message). Wrapping your divisor column in NULLIF, comparing it against zero, converts any genuine zero values in that column into NULL instead, before the division actually happens. Since dividing any number by NULL simply produces NULL as a result (following the calculation rule covered above), rather than the explicit divide-by-zero error your database would otherwise raise, this specific technique lets your query continue running smoothly, producing a NULL result for those specific problematic rows rather than halting your entire query with an error.
NULL Behavior in JOIN Operations
As covered in detail in the JOIN tutorials, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN all specifically produce NULL values in the columns belonging to whichever table did not have a matching row for that particular result row. This is precisely how you can identify, after running an outer JOIN, which specific rows did not have a corresponding match on the other side: checking for IS NULL on a column you know belongs specifically to the table that might be missing a match for that row.
NULL Behavior With IN and NOT IN
As briefly mentioned in the WHERE clause tutorial, this specific NULL behavior deserves restating clearly given how much confusion it causes in practice: if a list of values you provide to an IN or NOT IN condition happens to contain a NULL value anywhere within that list (which can easily happen if that list comes from a subquery rather than values you have manually typed out yourself), NOT IN specifically can behave in a surprising, counter-intuitive way, potentially returning zero rows for your entire query, even for rows that you would intuitively expect should clearly match your stated condition.
This happens because of the same three-valued logic mentioned earlier: comparing any value against NULL using NOT IN’s underlying not-equals logic produces an “unknown” result rather than a definitive true, and SQL’s overall logic for combining multiple comparisons (which NOT IN effectively does internally, checking against every value in your provided list) requires every single individual comparison to definitively evaluate as true for the overall NOT IN condition to hold, meaning even just one single “unknown” comparison anywhere in that list can quietly cause the entire condition to fail for every row, rather than just being ignored as you might intuitively expect.
The practical, defensive habit: if your IN or NOT IN list comes from a subquery rather than values you have manually and confidently typed yourself, specifically check or filter that subquery to exclude any potential NULL values from appearing within its returned list, before relying on NOT IN against that potentially NULL-containing 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 is not an edge case that occasionally matters in unusual situations — it is a foundational behavior that touches nearly every other SQL concept covered elsewhere in this series, from basic filtering, through JOINs, through aggregate functions, through sorting. A solid, complete understanding of NULL specifically prevents a meaningful fraction of the confusing, hard-to-diagnose bugs that beginners (and, honestly, experienced practitioners who have not encountered a specific NULL edge case before) regularly run into.
If you only take one thing from this entire tutorial, it should be this: whenever a query produces a result that genuinely surprises you — too few rows, an unexpected NULL appearing somewhere, a calculation producing NULL when you expected an actual number — checking specifically for NULL involvement somewhere in that query’s logic is one of the fastest, most reliably productive diagnostic steps available, precisely because NULL’s behavior is consistently the specific thing that does not work the way intuition from ordinary, non-database logic would suggest.
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.