NULL in SQL: Why = NULL Finds Nothing and What to Write Instead
Explore Michael Nocito's data analysis guide on SQL NULL values, explaining why comparisons return zero rows and how to correctly use IS NULL and NOT EXISTS.
Stock photo for illustration only, not from the actual event
- NULL in SQL means an unknown value, not zero and not empty text.
- Using the equals sign (= NULL) always returns zero rows because it evaluates to unknown.
- The WHERE clause only retains rows that explicitly evaluate to yes.
- Using NOT IN with subqueries containing NULL can silently return an empty set.
An article published on August 7, 2026, by data analyst Michael Nocito breaks down a common SQL pitfall: why queries return unexpected empty results due to missing values. Understanding how NULL behaves separates guessing from quick debugging.
Using a sample support-tickets table with 10 rows—where 3 tickets lack a close time, 2 lack a region, and 2 have no assignee—running a query like SELECT * FROM tickets WHERE assignee = NULL; returns zero rows on every database, rather than the two unassigned tickets.
Stock photo for illustration only, not from the actual event
The underlying mechanism stems from three-valued logic in SQL, where comparisons yield yes, no, or unknown. Because NULL represents missing information, comparing anything to NULL results in unknown, and the WHERE gate drops unknown rows just like no rows without throwing any errors.
To properly check for missing data, developers must use the dedicated IS NULL operator, which queries the status of the data slot itself rather than comparing values.
"NULL does not mean zero, and it does not mean empty text. NULL means unknown: this row has an assignee slot, and nobody has said what goes in it."
Michael Nocito
Another severe trap is using NOT IN with a subquery containing NULLs, which can silently yield zero results. The fixes involve filtering out NULLs from the subquery or switching to NOT EXISTS, which evaluates row by row and avoids the trap.
Source: Dev.to
Found something wrong in this article? Report an issue with this article
Comments
Leave a Comment