Historical Context & Motivation
Every real-world dataset has gaps. A patient's blood-pressure reading was never recorded; an e-commerce customer left the phone-number field blank; a sensor went offline for three hours. Before relational databases formalized a solution, programmers resorted to sentinel values — magic numbers like −1, 9999, or empty strings — to represent "no data here." These ad-hoc markers polluted calculations, broke comparisons, and forced every application to remember which sentinel meant what. The relational model needed a principled way to represent the absence of a value without contaminating the domain of legitimate data.
The core question that NULL answers is deceptively simple: how should a formally typed system represent the fact that a piece of information does not exist without confusing that absence with any actual value in the column's domain? The answer — a special marker that is not equal to anything, not even itself — has profound consequences for comparison operators, aggregation, joins, and constraint enforcement. Understanding these consequences is a prerequisite for writing correct SQL.
Core Principles & Definitions
Before examining how NULL propagates through expressions and predicates, it is essential to internalize several foundational ideas. These principles are not mere conventions — they follow logically from Codd's decision that NULL is a marker, not a value.
NULL Is Not a Value
Three-Valued Logic (3VL)
NULL Propagation
5 + NULL is NULL, not 5. This "infectious" behavior reflects the principle that a result derived from unknown data is itself unknown.IS NULL / IS NOT NULL
x = NULL always evaluates to UNKNOWN (even when x is NULL), the language provides the special predicates IS NULL and IS NOT NULL for explicit NULL checks.Aggregates Skip NULLs
SUM, AVG, MIN, and MAX ignore NULL inputs. COUNT(*) counts rows, but COUNT(column) counts only non-NULL entries.Visual Explanation — Three-Valued Logic
In classical Boolean logic, every predicate is either TRUE or FALSE. SQL's introduction of NULL forces a third truth value, UNKNOWN. The following diagram illustrates the truth tables for AND, OR, and NOT under three-valued logic. Notice how UNKNOWN "infects" conjunctions and disjunctions asymmetrically: AND with FALSE is always FALSE (because no matter what the unknown value is, the conjunction fails), while OR with TRUE is always TRUE.
AND, OR, and NOT under three-valued logic. The color coding — green (TRUE), amber (UNKNOWN), red (FALSE) — shows how UNKNOWN propagates. The lower panel illustrates that a WHERE clause only passes TRUE rows, treating UNKNOWN exactly like FALSE.The critical insight from this diagram is the asymmetry in how each operator treats UNKNOWN. In AND, FALSE dominates: if either operand is FALSE, the result is FALSE regardless of the other operand (even if it is UNKNOWN), because no possible substitution for the unknown value could make the conjunction true. In OR, TRUE dominates analogously. The NOT operator maps UNKNOWN to UNKNOWN, reinforcing that negating an unknown does not yield knowledge. This three-valued framework is the single most important conceptual shift that NULL introduces into relational reasoning.
How NULL Propagates Through Expressions
While SQL is not typically described via formal equations, the propagation rules for NULL can be stated precisely. Understanding these rules prevents the most common class of NULL-related bugs: queries that silently return wrong results because an expression collapsed to NULL or UNKNOWN without the developer noticing.
Arithmetic & String Propagation
Comparison Propagation
NULL = NULL is UNKNOWN, not TRUE. This is because two unknown values cannot be asserted to be equal.Aggregate Functions
SUM, AVG, MIN, MAX, and COUNT(column): NULLs are silently removed before computation. If all inputs are NULL, the result is NULL (except COUNT, which returns 0).AVG ignores NULLs, AVG(column) is not the same as SUM(column) / COUNT(*). The former divides by the count of non-NULL values; the latter divides by the total row count. These diverge whenever NULLs are present.Classifying Missingness in Relational Data
SQL uses a single NULL marker for all types of missing data, but the reason data is missing matters enormously for both query correctness and statistical validity. Database theory and data science distinguish several categories of missingness. Understanding this taxonomy helps you decide whether to filter, impute, or propagate NULLs in your queries.
The practical implication is that SQL forces you to encode semantic context outside the NULL marker itself — typically through documentation, naming conventions, or CHECK constraints. A column named middle_name might contain NULL to mean "the person has no middle name" (inapplicable) or "we don't know their middle name" (missing but applicable). These two cases call for different handling in reports. Recognizing which type of missingness your NULLs represent is the first step toward writing queries that produce meaningful rather than misleading results.
phone_number column paired with a phone_status enum of ('known', 'unknown', 'not_applicable') can capture the semantics that NULL alone cannot.Worked Example — NULL in a Real Query
Consider an employees table with columns id, name, department, and bonus (integer, nullable). We have five rows: Alice (bonus 1000), Bob (bonus NULL), Carol (bonus 500), Dave (bonus NULL), and Eve (bonus 2000). We want to compute the average bonus and find all employees who did not receive a bonus.
AVG function ignores NULLs. It sums the non-NULL values (1000 + 500 + 2000 = 3500) and divides by the count of non-NULL values (3). So AVG(bonus) = 3500 / 3 ≈ 1166.67.SUM(bonus) = 3500 (NULLs skipped), but COUNT(*) = 5 (all rows). So SUM(bonus) / COUNT(*) = 3500 / 5 = 700. This is a different — and arguably more representative — number if NULL means "bonus is zero."SELECT name FROM employees WHERE bonus = NULL returns zero rows. The comparison bonus = NULL evaluates to UNKNOWN for every row (including Bob and Dave), and UNKNOWN rows are excluded from the result.SELECT name FROM employees WHERE bonus IS NULL. The IS NULL predicate evaluates to TRUE for Bob and Dave, so both are returned.Strategies for Handling NULLs — Strengths & Limitations
SQL provides several functions and design patterns for managing NULLs. Each approach embodies a trade-off between expressiveness, correctness, and performance. The following table summarizes the most common strategies and when each is appropriate.
| Strategy | Syntax / Approach | When to Use | Pitfall |
|---|---|---|---|
| IS NULL / IS NOT NULL | WHERE col IS NULL | Explicit filtering for presence or absence of data. | None — this is the correct way to test for NULL. |
| COALESCE | COALESCE(col, default) | Replacing NULLs with a sensible default for display or computation. | Choosing a misleading default (e.g., 0 for salary) can silently skew aggregates. |
| NULLIF | NULLIF(expr1, expr2) | Converting sentinel values back to NULL for clean computation. | Only handles one sentinel at a time; may need nesting. |
| NOT NULL Constraint | col INT NOT NULL | Enforcing at the schema level that a column must always have a value. | Requires a valid default or application-level enforcement; overly strict constraints can cause insert failures. |
| CASE Expression | CASE WHEN col IS NULL THEN ... END | Complex conditional logic that branches differently for NULL vs. non-NULL. | Verbose; can obscure intent if overused. |
| Outer Joins | LEFT JOIN ... ON ... | Preserving rows with no match; unmatched columns are padded with NULLs. | Join-introduced NULLs can be confused with original NULLs in the source table. |
IS NULL, COALESCE). But ignoring the reagent's properties — treating NULL like a normal value — leads to invisible "explosions" where your query silently returns incorrect results rather than raising an error.NULL in Advanced SQL and Database Theory
The foundational NULL semantics you have learned propagate into every advanced SQL feature. Understanding these connections now will prevent confusion later when you encounter outer joins, subqueries, or constraint enforcement in production systems.
| Foundational Concept | Advanced Extension | NULL Implication |
|---|---|---|
| Three-valued logic in WHERE | CHECK constraints | A CHECK constraint passes if the predicate is TRUE or UNKNOWN. This means CHECK(salary > 0) allows NULL salaries — a common surprise. |
| NULL = NULL → UNKNOWN | UNIQUE constraints | Most RDBMS allow multiple NULLs in a UNIQUE column because NULLs are not considered equal. (SQL:2003 defines this behavior; some systems vary.) |
| NULL propagation in expressions | Subqueries with NOT IN | If the subquery returns any NULL, x NOT IN (subquery) can never be TRUE — it evaluates to UNKNOWN for all x. Use NOT EXISTS instead. |
| Aggregates skip NULLs | Window functions | Functions like LEAD() and LAG() return NULL when no adjacent row exists, overlapping with data-NULLs. Use the optional default argument to disambiguate. |
| IS NULL predicate | IS DISTINCT FROM (SQL:2003) | This operator treats two NULLs as equal and NULL vs. non-NULL as not equal, providing two-valued (TRUE/FALSE) comparisons — a safer alternative in many contexts. |
The NOT IN trap deserves particular emphasis. Consider SELECT * FROM A WHERE id NOT IN (SELECT parent_id FROM B). If parent_id contains even one NULL, the entire NOT IN predicate becomes UNKNOWN for every row in A, returning an empty result set. This is arguably the most notorious NULL-related bug in production SQL. The idiomatic solution is to rewrite the query using NOT EXISTS, which evaluates row-by-row and handles NULLs predictably, or to explicitly filter NULLs from the subquery.
Practice Problems
NULL = NULL evaluates to UNKNOWN rather than TRUE in SQL. What design principle motivates this behavior?COUNT(*), (b) COUNT(val), (c) SUM(val), (d) AVG(val)?WHERE NOT (department = 'Sales'). If a row has department = NULL, will this row appear in the result set? Trace the evaluation step by step using three-valued logic.email column (UNIQUE constraint). Users report that multiple accounts can be created without an email address. Furthermore, the query SELECT * FROM users WHERE email NOT IN (SELECT email FROM blocklist) returns no results even though most users are not blocked. Diagnose both issues and propose fixes.Summary — NULL & Missingness in Relational Data
SQL's NULL is a special marker — not a value — that represents the absence of data. It introduces three-valued logic (TRUE, FALSE, UNKNOWN) into every predicate evaluation. Any arithmetic or comparison involving NULL propagates NULL or UNKNOWN respectively. Aggregate functions silently skip NULLs, which means AVG(col) differs from SUM(col)/COUNT(*) whenever NULLs are present. The correct way to test for NULL is with IS NULL / IS NOT NULL, never with = NULL.
Missingness itself comes in multiple flavors — missing but applicable, missing and inapplicable, and not yet available — but SQL conflates all of these into a single marker. Managing NULLs effectively requires choosing the right tool: COALESCE for safe defaults, NOT EXISTS instead of NOT IN when NULLs are possible, and NOT NULL constraints at the schema level when missing data is genuinely unacceptable. Mastering NULL semantics is foundational to writing correct, reliable SQL.