CSE Roadmap

Core tier · about 3 hours · 5 parts

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.