NULL and three-valued logic

NULL doesn't mean zero, empty string, or false — it means "unknown," and that single fact makes ordinary comparisons behave in ways that trip up nearly everyone the first time they hit it.

Beginner

4 min read

NULL means "unknown," not "empty" or "zero"

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    middle_name TEXT       -- nullable: not everyone has one
);

NULL represents the absence of a value — a middle name that was never provided, a shipped date for an order that hasn't shipped yet. It's not the same as an empty string (''), not the same as 0, and not the same as false — those are all real, known values. NULL specifically means "this value is unknown or doesn't apply," which is a genuinely different concept than any of Python's None-adjacent equivalents in other languages behave exactly the same way.

Why `= NULL` doesn't work the way it looks like it should

SELECT * FROM users WHERE middle_name = NULL;    -- returns ZERO rows, always, even
                                                    -- for rows where middle_name really is NULL

= compares two known values — but NULL isn't a known value to compare against, it's the absence of one. "Is this unknown value equal to NULL?" doesn't have a yes/no answer any more than "is an unknown number equal to 5" does — so SQL's actual answer to NULL = NULL isn't true, it's also NULL (unknown), and a WHERE clause only keeps rows where the condition evaluates to exactly true — NULL doesn't count, so the row is silently excluded, even from a comparison that "should" match.

The correct way to check for NULL: `IS NULL` / `IS NOT NULL`

SELECT * FROM users WHERE middle_name IS NULL;      -- correctly finds rows with no middle name
SELECT * FROM users WHERE middle_name IS NOT NULL;  -- correctly finds rows that have one

IS NULL and IS NOT NULL are special-cased operators specifically for this — they're not regular value comparisons, they directly ask "is this NULL" or "is this not NULL," which is exactly the question =/!= can't actually answer. This is one of the most common real SQL bugs: writing WHERE column != 'value' and being surprised that rows where column is NULL don't show up in the results — they don't match != any more than they'd match =, for the identical reason.

Three-valued logic: TRUE, FALSE, and UNKNOWN

-- age > 18 for a row where age is NULL evaluates to UNKNOWN, not TRUE or FALSE
SELECT * FROM users WHERE age > 18;   -- rows with NULL age are excluded (UNKNOWN != TRUE)
SELECT * FROM users WHERE NOT (age > 18);  -- rows with NULL age are ALSO excluded (NOT UNKNOWN is still UNKNOWN)

Ordinary boolean logic has two values, TRUE and FALSE. SQL's WHERE clause logic actually has three: TRUE, FALSE, and UNKNOWN — any comparison involving NULL produces UNKNOWN, and UNKNOWN propagates through AND/OR/NOT the same way NULL does through arithmetic. The genuinely surprising part: NOT (age > 18) on a NULL age doesn't flip to TRUE — NOT UNKNOWN is still UNKNOWN, so that row is excluded from both the original condition and its negation. A row with NULL age is invisible to age > 18 and equally invisible to NOT (age > 18).

NULL and aggregate functions: quietly ignored, not counted as zero

-- If 3 of 10 orders have a NULL discount:
SELECT AVG(discount) FROM orders;   -- averages only the 7 non-NULL values, not all 10
SELECT COUNT(discount) FROM orders; -- counts only the 7 non-NULL values
SELECT COUNT(*) FROM orders;         -- counts all 10 rows, NULL or not — this one's different

Aggregate functions like AVG, SUM, and COUNT(column) skip NULL values entirely rather than treating them as 0 — this matters because it silently changes the denominator: AVG(discount) over 10 rows where 3 are NULL divides by 7, not 10, which is usually what's actually wanted (the average of the discounts that exist) but is easy to get wrong if you expect it to average across every row. COUNT(*) is the one exception worth remembering separately — it counts rows regardless of any column being NULL, which is why COUNT(*) and COUNT(some_column) can return genuinely different numbers on the same table.

Handling NULL explicitly: COALESCE

SELECT name, COALESCE(middle_name, '(none)') AS middle_name FROM users;

COALESCE(value, fallback) returns value if it's not NULL, or fallback otherwise — the SQL equivalent of Python's value if value is not None else fallback, or a dict's .get(key, default) from the Python collections lesson. This is the standard way to give a NULL a sensible display value or default without changing what's actually stored in the table.