Part 2 of 5 · 1 min read
NULL is not a value
Why NULL means "unknown," and why that is different from zero or an empty string.
Think of a form field left blank because the answer truly is not known yet, not because the answer is zero, or because the answer is an empty string.
In plain words: NULL represents an unknown or missing value. It is not the number zero, and not an empty string. Comparing anything to NULL using = or <> does not return true or false. It returns a third result, unknown, because the database cannot say whether an unknown value equals anything.
A worked example. A book with no recorded discount, stored as NULL, is not the same as a book with a discount of exactly zero. discount = 0 matches the second book but not the first, since NULL = 0 is unknown, not true.
The key lines.
SELECT * FROM books WHERE discount = NULL; -- returns zero rows, always, for every row
NULL, three-valued logic
NULL represents an unknown or missing value, distinct from zero or an empty string. Three-valued logic is SQL's use of three possible results for a condition: true, false, and unknown, with unknown arising whenever NULL is compared.
Example: discount = NULL is always unknown, for every row, which is why it never appears in a WHERE clause's results.