Understanding NULL

NULL is one of the most misunderstood ideas in SQL, and getting it wrong causes real bugs — rows silently missing from results, conditions that never match when you expect them to. Before working with NULL in comparisons and aggregates, it’s worth slowing down and understanding exactly what it represents.

NULL means “unknown” or “missing”

NULL is not a value in the normal sense — it is a marker that says “there is no data here.” Critically:

NULL is not zero — a numeric column holding NULL has no known number at all, not the number 0
NULL is not an empty string — a text column holding NULL has no known text, not a zero-length string ''
NULL is not false — a boolean column holding NULL is not known to be true or false

Value	Meaning
0	A known number: zero
''	A known, empty piece of text
FALSE	A known boolean: false
NULL	Unknown / not applicable / missing

For example, a middle_name column holding NULL doesn’t mean the person has no middle name and it doesn’t mean an empty string was recorded — it means the database simply doesn’t know it.

NULL breaks normal equality

NULL = NULL is not TRUE

You might expect comparing NULL to NULL to be true, since they “look the same.” They are not — the result is NULL (unknown), not TRUE:

Comparisons involving NULL

SQL

SELECT NULL = NULL;      -- result: NULL (unknown)
SELECT NULL = 5;         -- result: NULL (unknown)
SELECT 5 = 5;             -- result: TRUE
SELECT NULL IS NULL;      -- result: TRUE
SELECT NULL IS NOT NULL;  -- result: FALSE

The logic is: if you don’t know what a value is, you can’t say whether it equals another unknown value, or any specific value — the honest answer is “unknown,” not true or false.

Three-valued logic

Standard programming languages use two-valued (boolean) logic — every condition is TRUE or FALSE. SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any expression that involves a NULL typically evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition evaluates to TRUE — rows that evaluate to UNKNOWN are filtered out, just like FALSE ones.

Expression	Result
TRUE AND NULL	NULL (unknown)
FALSE AND NULL	FALSE
TRUE OR NULL	TRUE
FALSE OR NULL	NULL (unknown)
NOT NULL	NULL (unknown)

Testing for NULL correctly

Because = NULL never works, SQL provides dedicated operators for checking whether a value is or isn’t NULL:

IS NULL and IS NOT NULL

SQL

-- Find customers with no phone number on file
SELECT name
FROM customers
WHERE phone IS NULL;

-- Find customers who DO have a phone number on file
SELECT name
FROM customers
WHERE phone IS NOT NULL;

A common mistake

Writing WHERE phone != NULL or WHERE phone = NULL is a frequent bug — both silently return zero rows, because the comparison always evaluates to UNKNOWN rather than TRUE. Always use IS NULL / IS NOT NULL instead.

NULL represents missing or unknown information, never zero, empty string, or false
Any direct comparison with NULL (=, !=, <, >) evaluates to UNKNOWN, not TRUE or FALSE
Use IS NULL / IS NOT NULL to test for it explicitly
The next page covers how this behavior carries into filtering, aggregates, and sorting