NULL & Missingness — Understand NULL and missingness in relational data (conceptual) - SQL

SECTION 1

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.

1970 • Codd's Relational Model

E. F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," introducing the idea that relations can contain missing information, though the formal treatment of NULL comes later.

1979 • Codd Introduces NULL Formally

Codd proposes a distinguished marker — NULL — to represent missing or inapplicable information, arguing that it must be distinct from every value in every domain.

1986 • SQL-86 Standard Adopts NULL

The first ANSI SQL standard codifies NULL and introduces three-valued logic (TRUE, FALSE, UNKNOWN) for predicates involving NULL, along with the IS NULL predicate.

1990 • Codd's Four-Valued Logic Proposal

Codd refines his theory, distinguishing "missing but applicable" (A-mark) from "missing and inapplicable" (I-mark). SQL never adopts this distinction, keeping a single NULL marker.

2003–present • Modern Extensions

SQL:2003 and subsequent standards add COALESCE, NULLIF, and refined NULL handling in window functions. Debate continues in database theory about whether NULL is a necessary evil or an elegant solution.

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.

SECTION 2

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

NULL is a marker that signals the absence of data. It belongs to no domain — it is not zero, not an empty string, and not false. It exists outside the type system.

Three-Valued Logic (3VL)

Predicates involving NULL evaluate to UNKNOWN, a third truth value alongside TRUE and FALSE. WHERE clauses pass only rows whose predicate is TRUE, silently discarding UNKNOWN.

NULL Propagation

Any arithmetic or string operation with a NULL operand yields NULL. For example, 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

Because 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

Functions like SUM, AVG, MIN, and MAX ignore NULL inputs. COUNT(*) counts rows, but COUNT(column) counts only non-NULL entries.

✦

KEY TAKEAWAY

Think of NULL as a blank sticky note on a form. The note is not the number zero, nor the word "none" — it conveys "this field was never filled in." You cannot add a blank note to a number and get a meaningful result, and two blank notes are not "equal" because you have no idea what either one was supposed to say. Every SQL operation must decide: do I skip the blank note, or does its blankness taint my answer?

SECTION 3

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.

The truth tables for 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.

SECTION 4

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

NULL ARITHMETIC RULE

x ⊕ NULL → NULL (for any operator ⊕ ∈ {+, −, ×, /, ||, …})

If x is any value or NULL, and ⊕ is any arithmetic or string operator, the result is NULL. This models the principle: a computation involving an unknown input yields an unknown output.

Comparison Propagation

NULL COMPARISON RULE

x θ NULL → UNKNOWN (for any comparison θ ∈ {=, <>, <, >, ≤, ≥})

Comparisons with NULL never yield TRUE or FALSE. Crucially, NULL = NULL is UNKNOWN, not TRUE. This is because two unknown values cannot be asserted to be equal.

Aggregate Functions

AGGREGATE NULL RULE

AGG({v₁, v₂, NULL, v₃}) = AGG({v₁, v₂, v₃})

For 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 Pitfall

Because 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.

SECTION 5

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 taxonomy of missingness spans two perspectives: Codd's relational-model distinction between applicable (A-mark) and inapplicable (I-mark) missing data, and Rubin's statistical classification (MCAR, MAR, MNAR). SQL's single NULL marker conflates all categories, placing the burden of interpretation on the developer.

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.

💡

Design Tip

If the distinction between "unknown" and "not applicable" matters for your business logic, consider using separate columns or a companion status column rather than overloading NULL. For example, a phone_number column paired with a phone_status enum of ('known', 'unknown', 'not_applicable') can capture the semantics that NULL alone cannot.

SECTION 6

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.

Computing Average Bonus with NULLs

1
Step 1 — Examine the Data

The bonus column contains values: 1000, NULL, 500, NULL, 2000. Two out of five employees have NULL bonuses.
2
Step 2 — Compute AVG(bonus)

SQL's 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.

AVG(bonus) ≈ 1166.67
3
Step 3 — Compare with SUM(bonus) / COUNT(*)

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."

SUM / COUNT(*) = 700 ≠ AVG(bonus)
4
Step 4 — Find Employees with No Bonus (Wrong Way)

The query 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.
5
Step 5 — Find Employees with No Bonus (Correct Way)

The correct query uses SELECT name FROM employees WHERE bonus IS NULL. The IS NULL predicate evaluates to TRUE for Bob and Dave, so both are returned.

Result: Bob, Dave

SECTION 7

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.

Common NULL-handling strategies in SQL

✦

KEY TAKEAWAY

Think of NULL handling like wearing safety glasses in a chemistry lab. You can work with dangerous reagents (missing data) safely if you use the right protective equipment (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.

SECTION 8

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.

How foundational NULL concepts connect to advanced SQL features

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.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why NULL = NULL evaluates to UNKNOWN rather than TRUE in SQL. What design principle motivates this behavior?

Show answer

NULL represents an unknown value. Two unknown values cannot be asserted as equal because we do not know what either one represents — they could be the same or different. If SQL treated NULL = NULL as TRUE, it would imply that all missing data is identical, which is logically unsound. This behavior follows directly from Codd's principle that NULL is a marker outside any domain, not a value within a domain. The practical consequence is that equality comparisons with NULL always yield UNKNOWN, requiring the IS NULL predicate for explicit NULL testing.

Question 2: BASIC CALCULATION

Given the values {10, NULL, 20, NULL, 30}, what does each of the following return: (a) COUNT(*), (b) COUNT(val), (c) SUM(val), (d) AVG(val)?

Show answer

(a) COUNT() = 5, because it counts rows regardless of NULLs. (b) COUNT(val) = 3, because it counts only non-NULL entries. (c) SUM(val) = 10 + 20 + 30 = 60, since NULLs are skipped. (d) AVG(val) = 60 / 3 = 20, because AVG divides the sum of non-NULL values by the count of non-NULL values. Note that SUM(val)/COUNT() would yield 60/5 = 12, a different result. The choice between these two calculations depends on whether NULL means "zero" or "unknown" in your domain.

Question 3: INTERMEDIATE

Consider the predicate 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.

Show answer

Step 1: Evaluate the inner comparison department = 'Sales'. Since department is NULL, this yields UNKNOWN (any comparison with NULL is UNKNOWN). Step 2: Apply NOT to UNKNOWN. From the NOT truth table, NOT UNKNOWN = UNKNOWN. Step 3: The WHERE clause filters for TRUE only. Since the result is UNKNOWN (not TRUE), the row is excluded. This is a common trap: developers intend to find all non-Sales employees but inadvertently exclude employees with NULL departments. The fix is to add an explicit OR: WHERE department <> 'Sales' OR department IS NULL, or use IS DISTINCT FROM 'Sales' if the RDBMS supports it.

Question 4: APPLIED

A web application stores user profiles in a table with a nullable 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.

Show answer

Issue 1: Multiple NULL emails are allowed because most RDBMS (following the SQL standard) do not treat NULLs as equal, so the UNIQUE constraint does not prevent duplicate NULLs. Fix: Add a partial index or filtered unique constraint (e.g., in PostgreSQL: CREATE UNIQUE INDEX ON users (email) WHERE email IS NOT NULL), or make the column NOT NULL and use a sentinel like a generated unique placeholder. Issue 2: If the blocklist table contains even one NULL email, the NOT IN subquery causes every comparison to evaluate to UNKNOWN, returning an empty result set. Fix: Rewrite using NOT EXISTS (SELECT 1 FROM blocklist b WHERE b.email = u.email) or add WHERE email IS NOT NULL inside the subquery. The NOT EXISTS approach is generally preferred because it handles NULLs correctly and often performs better with proper indexing.

Question 5: CRITICAL THINKING

Codd proposed distinguishing two types of NULL — the A-mark (missing but applicable) and the I-mark (missing and inapplicable) — requiring four-valued logic. The SQL standard rejected this proposal. Present arguments for and against Codd's four-valued logic approach. Consider practical implementation, query complexity, and semantic expressiveness.

Show answer

Arguments for four-valued logic: It captures real semantic distinctions — a patient whose blood type is unknown (A-mark) differs fundamentally from asking for the maiden name of someone never married (I-mark). With two markers, aggregate functions could handle each case differently (e.g., include I-marks as zeros in COUNT but exclude A-marks). It would reduce the need for auxiliary status columns. Arguments against: Four-valued logic dramatically increases complexity. Every truth table doubles in size (4×4 = 16 entries for each binary operator), making it harder for developers to reason about query behavior. Optimizer complexity grows substantially. The combinatorial explosion worsens if future needs suggest even more markers (e.g., "redacted," "embargoed"). Practically, the same semantic richness can be achieved through schema design — companion columns, enums, or domain-specific conventions — without burdening the core logic engine. The SQL standard's choice reflects a pragmatic trade-off: a simpler core with extensibility through schema design rather than a more expressive but unwieldy logical foundation.

SUMMARY

Summary — NULL & Missingness in Relational Data

Summary

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.