NULL Semantics

Definition

NULL represents an unknown or absent value — it is not zero, not an empty string, and not "false." NULL follows three-valued logic: any comparison involving NULL evaluates to UNKNOWN, not TRUE or FALSE, which is why NULL cannot be tested with the ordinary = operator.

How It Works

Consider a table employees(id, name, manager_id) where a top-level employee (no manager) has manager_id = NULL:

sql
-- WRONG — this NEVER matches, even for rows where manager_id is NULL
SELECT * FROM employees WHERE manager_id = NULL;

-- CORRECT — IS NULL / IS NOT NULL are the only way to test for NULL
SELECT * FROM employees WHERE manager_id IS NULL;

manager_id = NULL evaluates to UNKNOWN for every row — including rows where manager_id really is NULL — because "is this unknown value equal to this other unknown value?" is itself unknown. A WHERE clause only keeps rows where the condition is TRUE; UNKNOWN rows are discarded exactly like FALSE rows, so the query silently returns zero rows instead of erroring, which makes this an easy bug to miss.

NULL also propagates through arithmetic and most functions: 5 + NULL is NULL, and COUNT(column) skips NULLs in that column (while COUNT(*) counts rows regardless of NULLs). NULL values also sort in an engine-defined position (commonly last, or first — this differs by dialect) with ORDER BY.

Edge Cases and Pitfalls

• WHERE column = NULL is a very common beginner bug — it silently returns no rows rather than throwing an error, which makes it hard to notice.
• NOT IN (subquery) where the subquery can return a NULL behaves surprisingly: if even one row in the subquery's result is NULL, the entire NOT IN comparison can evaluate to UNKNOWN for every row, making the whole query return zero rows unexpectedly.
• COUNT(column) and COUNT(*) differ specifically because of NULLs — COUNT(column) only counts non-NULL values in that column.

Key Takeaways

• Test for NULL only with IS NULL / IS NOT NULL; = NULL and <> NULL never match, by design.
• NULL represents "unknown," not zero/empty/false — arithmetic and equality involving NULL propagate to NULL/UNKNOWN rather than a concrete value.
• NOT IN with a NULL-containing subquery is a classic real-world footgun; prefer NOT EXISTS when NULLs might be present.