NULL represents a missing or unknown value — not zero, not an empty string, but the absence of a value. NULL has special, sometimes surprising behavior in comparisons, aggregates, and logic — making it a common source of bugs.
email
email
email
email
Because NULL means "unknown," any comparison with NULL yields "unknown" (not true), so = NULL never matches. You must use IS NULL/IS NOT NULL — the single most common NULL mistake.
SQL uses THREE-valued logic: TRUE, FALSE, and UNKNOWN (NULL).
NULL = NULL → UNKNOWN (not true!)
NULL AND true → UNKNOWN
NULL OR true → TRUE
NOT NULL → UNKNOWN
→ Conditions evaluating to UNKNOWN are treated as not-matching by WHERE.
-- aggregates IGNORE NULLs (except COUNT(*))
SELECT AVG(salary) FROM employees; -- averages only non-NULL salaries
SELECT COUNT(salary) FROM employees; -- counts only non-NULL (vs COUNT(*) = all rows)
-- arithmetic with NULL → NULL
SELECT price + NULL; -- NULL (any operation with NULL is NULL)
Aggregates skip NULLs, and any arithmetic involving NULL produces NULL.
-- COALESCE — return the first non-NULL value (provide a default)
SELECT COALESCE(phone, 'No phone') FROM users; -- 'No phone' if phone is NULL
SELECT COALESCE(nickname, name, 'Unknown') FROM users; -- first non-NULL
-- NULLIF — return NULL if two values are equal
SELECT NULLIF(value, 0); -- NULL if value is 0 (e.g. avoid divide-by-zero)
COALESCE provides fallbacks for NULL (very common for defaults), and NULLIF converts a value to NULL conditionally.
Understanding NULL behavior is essential for writing correct SQL, because NULL's special semantics are a frequent source of subtle bugs — and NULLs appear constantly in real data (optional fields, missing values, unmatched joins).
The most critical and commonly-mistaken point is that NULL means "unknown," so you can't compare to it with = (a notorious bug where WHERE col = NULL silently returns nothing); you must use IS NULL/IS NOT NULL.
Understanding SQL's three-valued logic (TRUE/FALSE/UNKNOWN, where comparisons with NULL yield UNKNOWN and are treated as not-matching) explains a range of confusing behaviors and is important for writing conditions correctly.
Knowing that aggregates ignore NULLs (affecting AVG, COUNT, etc. — sometimes surprisingly), that arithmetic with NULL yields NULL, and how to handle NULLs with COALESCE (providing defaults/fallbacks — a very common need) and NULLIF is practical, frequently-applied knowledge.
Since NULLs are pervasive in real databases (any nullable column, any LEFT JOIN), and since NULL's unintuitive behavior (especially the comparison issue and three-valued logic) causes real, hard-to-debug bugs, understanding how NULL behaves — comparisons, logic, aggregates, and handling — is core, must-know knowledge for writing correct SQL and a common interview topic that reveals understanding of a frequently-mishandled aspect of databases.