Why SQL NULL Can't Use Equals: Three-Valued Logic & Hidden Pitfalls

NULL Is Not a Value — It's a State

In most programming languages, null or None is a special value you can compare directly: if (obj == null) in Java or if x is None in Python. In SQL's relational model, however, NULL is not a value but a state meaning UNKNOWN — not empty, not zero, not an empty string, but "we don't know what this value is." For example, an employee's quit_date being NULL doesn't mean the date is blank; it means we don't know when (or if) they left.

Equality Comparison Returns UNKNOWN

The SQL = operator is a comparison that returns a boolean: TRUE or FALSE. When NULL participates, the result is a third state: UNKNOWN .

SELECT 1 = 1;   -- TRUE
SELECT 1 = 2;   -- FALSE
SELECT NULL = NULL;  -- UNKNOWN (not TRUE)
SELECT NULL = 1;     -- UNKNOWN (not FALSE)
SELECT NULL <> 1;    -- UNKNOWN (also not TRUE)
SELECT NULL > 0;     -- UNKNOWN

Why isn't NULL = NULL true? Because NULL means unknown . Two unknown things cannot be asserted equal — just as two employees with unknown quit dates cannot be said to have left on the same day.

WHERE Keeps Only TRUE Rows

The WHERE clause filters rows by a simple rule: only rows where the condition evaluates to TRUE are kept . Both FALSE and UNKNOWN are discarded.

-- employee table:
-- id=1, name='张三', quit_date='2024-06-01'
-- id=2, name='李四', quit_date=NULL

SELECT * FROM employee WHERE quit_date = NULL;
-- id=1: '2024-06-01' = NULL → UNKNOWN → filtered out
-- id=2: NULL = NULL → UNKNOWN → filtered out
-- Result: empty set

The correct predicate is IS NULL, which is not a comparison operator but a dedicated predicate that returns only TRUE or FALSE, never UNKNOWN:

SELECT * FROM employee WHERE quit_date IS NULL;  -- returns id=2

Hidden Pitfall: NOT IN with NULL

NOT IN

expands to a chain of <> comparisons joined by AND. If the list contains a NULL, one comparison becomes UNKNOWN, and TRUE AND UNKNOWN = UNKNOWN, causing the entire WHERE to evaluate to UNKNOWN for every row — silently returning zero rows.

SELECT * FROM employee WHERE dept_id NOT IN (10, 20, NULL);
-- Equivalent to:
WHERE dept_id <> 10 AND dept_id <> 20 AND dept_id <> NULL
-- dept_id <> NULL → UNKNOWN → whole condition UNKNOWN → no rows returned

This is especially dangerous with subqueries:

-- Risky: if department.dept_id has a NULL, result is empty
SELECT * FROM employee WHERE dept_id NOT IN (SELECT dept_id FROM department);

-- Safe: NOT EXISTS checks row existence, not value comparison
SELECT * FROM employee e
WHERE NOT EXISTS (
  SELECT 1 FROM department d WHERE d.dept_id = e.dept_id
);

Aggregate Functions Skip NULL

Aggregate functions (except COUNT(*)) ignore NULL values:

-- score column: 80, NULL, 90, NULL, 70
SELECT COUNT(score) FROM exam;  -- 3 (NULLs skipped)
SELECT COUNT(*)   FROM exam;    -- 5 (counts rows)
SELECT AVG(score) FROM exam;    -- 80 = (80+90+70)/3, not /5
SELECT SUM(score) FROM exam;    -- 240

If business logic requires NULL to be treated as zero, use COALESCE explicitly:

SELECT AVG(COALESCE(score, 0)) FROM exam;  -- (80+0+90+0+70)/5 = 48

MySQL's NULL-Safe Operator: <=>

MySQL provides a non-standard operator <=> (NULL-safe equal):

SELECT NULL <=> NULL;  -- 1 (TRUE)
SELECT NULL <=> 1;     -- 0 (FALSE)
SELECT 1 <=> 1;        -- 1 (TRUE)

Standard SQL uses IS NOT DISTINCT FROM for the same semantics. In daily practice, IS NULL / IS NOT NULL remains clearer and more portable.

Key Takeaway

SQL's NULL differs fundamentally from programming-language null: it denotes unknown , not absence of value . This three-valued logic causes equality comparisons to yield UNKNOWN, which WHERE discards. Always use IS NULL / IS NOT NULL for null checks, avoid NOT IN with nullable columns (prefer NOT EXISTS), and remember aggregates skip NULL unless you explicitly coalesce them.