Historical Context & Motivation
Classical Boolean logic, formalized by George Boole in the nineteenth century, operates on exactly two truth values: TRUE and FALSE. This binary system underpins virtually all of digital computing, from CPU instruction sets to programming language conditionals. However, when E. F. Codd proposed the relational model of data in 1970, he recognized that real-world data frequently contains gaps — a patient's blood type may not have been recorded, a sensor reading may have been lost, or a value may simply not apply. To represent this absence without corrupting the relational algebra, Codd introduced the concept of NULL, a marker that is explicitly not a value but rather a signal that a value is missing or unknown.
The introduction of NULL immediately raised a profound question: what should the result of a comparison like 5 = NULL be? It cannot be TRUE because we do not know what the NULL represents — it might represent 5, but it equally might represent 42. It also cannot be FALSE for the same reason. Codd's solution was to extend Boolean logic with a third truth value, UNKNOWN, giving rise to what we now call three-valued logic (3VL). This extension is codified in the SQL standard and affects every query you write that touches nullable columns.
The central question this lesson addresses is deceptively simple: how does the presence of NULL transform the behavior of logical operators and predicates in SQL? Understanding three-valued logic is not an abstract exercise — it is essential for writing correct queries, debugging silent data loss in WHERE and JOIN clauses, and building robust data pipelines.
Core Principles of Three-Valued Logic
Before diving into truth tables and SQL-specific behavior, it is important to establish the foundational principles that govern how NULL interacts with the rest of the language. These principles are not arbitrary design choices; they follow logically from the semantic meaning of NULL as unknown. If a value is unknown, then any computation involving that value must propagate the uncertainty, much like how an undefined variable in algebra prevents you from simplifying an expression to a concrete number.
NULL Is Not a Value
= or != — you must use IS NULL or IS NOT NULL.Comparisons Yield UNKNOWN
NULL = NULL — evaluates to UNKNOWN, not TRUE or FALSE. This is because you cannot determine equality between two unknowns.WHERE Filters on TRUE Only
WHERE clause includes a row only when the predicate evaluates to TRUE. Rows producing FALSE or UNKNOWN are silently excluded — a common source of bugs.Logical Operators Extend to 3VL
TRUE OR UNKNOWN is TRUE (the OR is satisfied regardless), but TRUE AND UNKNOWN is UNKNOWN (the AND needs both sides to be TRUE).Aggregates Ignore NULLs
SUM, AVG, and COUNT(column) skip NULL values entirely. Only COUNT(*) counts all rows regardless of NULLs, which can cause subtle discrepancies in aggregate results.Visual Explanation — Three-Valued Truth Tables
The following diagram presents the complete truth tables for the three logical operators — AND, OR, and NOT — extended to three truth values. Study the cells highlighted in amber: these are the entries that differ from classical two-valued logic and are the source of most NULL-related bugs in SQL queries.
The intuition behind the UNKNOWN entries follows a simple principle that can be summarized as follows. For AND: if either operand is FALSE, the result is FALSE regardless of the other operand (even if it is UNKNOWN), because AND requires both to be TRUE. If one operand is TRUE and the other is UNKNOWN, the result depends entirely on the unknown value — hence UNKNOWN. For OR: if either operand is TRUE, the result is TRUE regardless, because OR needs only one TRUE. If one is FALSE and the other is UNKNOWN, the result hinges on the unknown — hence UNKNOWN. For NOT: negating something unknown produces something equally unknown.
The Formal Framework — Kleene's Strong Logic
SQL's three-valued logic is formally equivalent to Kleene's strong three-valued logic (K₃), named after the mathematician Stephen Cole Kleene. In K₃, the truth values form a total order: FALSE < UNKNOWN < TRUE. The logical connectives can then be defined concisely using min and max operations over this ordering.
min(T, U) = U and min(F, U) = F.max(T, U) = T and max(F, U) = U.This min/max characterization has an important consequence: three-valued logic breaks several tautologies that hold in classical two-valued logic. Consider the law of the excluded middle: in 2VL, A OR NOT A is always TRUE. In 3VL, if A is UNKNOWN, then NOT A is also UNKNOWN, and UNKNOWN OR UNKNOWN = max(U, U) = U. The law fails. Similarly, A AND NOT A is not guaranteed to be FALSE; when A is UNKNOWN, the result is UNKNOWN rather than the expected FALSE. These breakdowns are not theoretical curiosities — they manifest in real queries and can lead to incorrect results if not anticipated.
WHERE x = x does NOT always return all rows. If x is NULL, this evaluates to UNKNOWN, and the row is excluded. To truly match all rows, you would need WHERE x IS NOT DISTINCT FROM x (SQL:1999 syntax) or WHERE x = x OR x IS NULL.NULL Propagation in SQL Expressions
Beyond logical operators, NULL propagates through arithmetic, string, and comparison expressions following a consistent rule: any expression involving NULL yields NULL (which in Boolean context becomes UNKNOWN). This means 5 + NULL is NULL, 'hello' || NULL is NULL, and NULL > 100 is UNKNOWN. The propagation behavior can be thought of as a form of infectious uncertainty: once a NULL enters a computation, the result becomes unknown, and that unknown cascades through any larger expression that depends on it.
salary propagates through the multiplication, then the comparison, producing UNKNOWN. Even though the department condition is TRUE, the AND with UNKNOWN yields UNKNOWN, and the row is silently dropped.| Expression | Result | Explanation |
|---|---|---|
NULL = NULL | UNKNOWN | Two unknowns may or may not be equal; cannot determine. |
NULL <> NULL | UNKNOWN | Same reasoning: inequality is also indeterminate. |
NULL + 10 | NULL | Arithmetic with NULL produces NULL. |
NULL IN (1, 2, 3) | UNKNOWN | IN is shorthand for OR'd equalities, each returning UNKNOWN. |
NULL NOT IN (1, 2, 3) | UNKNOWN | NOT UNKNOWN = UNKNOWN. This is a notorious bug source. |
NULL BETWEEN 1 AND 10 | UNKNOWN | BETWEEN expands to AND of two comparisons, both UNKNOWN. |
COALESCE(NULL, 0) | 0 | COALESCE returns the first non-NULL argument. |
Worked Example — Debugging a Missing-Rows Bug
Consider a table employees with columns id, name, department, and bonus (nullable). A developer writes a query to find all employees who did not receive a $500 bonus, but the result set is mysteriously smaller than expected. Let's trace the logic.
SELECT * FROM employees WHERE bonus <> 500; They expect this to return everyone whose bonus is not $500, including employees with no bonus at all. However, this query excludes employees where bonus IS NULL because NULL <> 500 evaluates to UNKNOWN, not TRUE.bonus = NULL. The WHERE predicate becomes NULL <> 500. Since NULL represents an unknown value, the database cannot determine whether the unknown value differs from 500, so the comparison yields UNKNOWN. The WHERE clause filters it out.SELECT * FROM employees WHERE bonus <> 500 OR bonus IS NULL; Now for Alice: UNKNOWN OR TRUE evaluates to TRUE (because max(U, T) = T), and the row is included.SELECT * FROM employees WHERE COALESCE(bonus, -1) <> 500; Here, if bonus is NULL, COALESCE substitutes −1, and -1 <> 500 is unambiguously TRUE. Choose a sentinel that cannot be a legitimate value.IS NOT DISTINCT FROM, which treats NULL as equal to NULL — a NULL-safe equality operator. The inverse query becomes: SELECT * FROM employees WHERE NOT (bonus IS NOT DISTINCT FROM 500); This elegantly handles NULLs without OR or COALESCE and always produces a two-valued (TRUE/FALSE) result.Common Pitfalls and Defensive Patterns
Three-valued logic creates a family of well-known traps that ensnare even experienced SQL developers. The following table catalogs the most dangerous pitfalls alongside the defensive patterns that prevent them. The common thread across all of these issues is that developers unconsciously reason in two-valued logic while writing queries that the database engine evaluates in three-valued logic.
| Pitfall | Why It Fails | Defensive Pattern |
|---|---|---|
WHERE x <> value misses NULLs | NULL <> value → UNKNOWN → row excluded | Add OR x IS NULL or use COALESCE |
NOT IN with nullable subquery | If any subquery result is NULL, the entire NOT IN becomes UNKNOWN for every row | Use NOT EXISTS instead, which returns TRUE/FALSE |
Aggregate skew: AVG ignores NULLs | AVG(column) divides by count of non-NULL rows, potentially inflating or deflating the average | Use AVG(COALESCE(col, 0)) if NULLs should be treated as zero |
| UNIQUE constraint allows multiple NULLs | NULL ≠ NULL, so each NULL is considered distinct by the constraint (in most RDBMS) | Add a partial index: WHERE col IS NOT NULL (PostgreSQL) or use a generated column |
| OUTER JOIN + WHERE filters undo the join | A WHERE clause on the outer table's columns eliminates the NULL-padded rows from the LEFT JOIN | Move the filter into the JOIN's ON clause, or use COALESCE in WHERE |
NOT IN pitfall is analogous to a logical trapdoor: imagine you ask, "Is my key not in this box of items?" If one slot in the box is empty (NULL), you cannot be certain — maybe your key is in that empty slot. So the answer for every key becomes "I don't know." A single NULL in a NOT IN subquery can silently zero out your entire result set. Use NOT EXISTS as the structurally safe alternative because EXISTS checks for row existence rather than value equality.Connections to Advanced Theory and Alternatives
The three-valued logic embedded in SQL has been a subject of intense debate in the database research community. C. J. Date and Hugh Darwen, in their Third Manifesto, argue that NULLs should be entirely eliminated from the relational model, replaced by explicit representation of missing information — for example, using separate tables for known and unknown attributes, or employing typed sentinel values. Others, including Codd himself in later work, proposed a four-valued logic distinguishing between "applicable but unknown" and "not applicable" — a distinction that SQL's single NULL conflates.
| Aspect | SQL's 3VL (Kleene K₃) | Proposed Alternatives |
|---|---|---|
| Truth values | TRUE, FALSE, UNKNOWN | 4VL: TRUE, FALSE, Missing, Inapplicable (Codd); 2VL with default values (Date) |
| Law of excluded middle | Violated: A OR NOT A can be UNKNOWN | Preserved in 2VL approaches; still violated in 4VL |
| Practical adoption | Universal: every SQL-compliant RDBMS | Limited: some NoSQL systems avoid NULLs; academic proposals |
| Query complexity | Requires defensive IS NULL checks throughout queries | 2VL eliminates UNKNOWN but requires schema redesign; 4VL adds complexity |
| Semantic fidelity | Cannot distinguish "unknown" from "not applicable" | 4VL captures this distinction; 2VL uses explicit columns (e.g., has_bonus BOOLEAN) |
In practice, SQL's 3VL is here to stay. Modern developments like NULL-safe operators (IS NOT DISTINCT FROM in SQL:1999, <=> in MySQL) and column-level NOT NULL constraints offer pragmatic mitigations. Looking forward, understanding three-valued logic connects directly to topics in database theory such as certain and possible answers in incomplete databases, query containment under open-world assumptions, and the formal semantics of outer joins in relational algebra.
Practice Problems
NULL = NULL evaluates to UNKNOWN rather than TRUE. How does this differ from most programming languages' treatment of null equality?(TRUE AND UNKNOWN) OR (FALSE AND UNKNOWN). What is the final result?orders(id INT, discount INT) with the following rows: (1, 10), (2, NULL), (3, 20), (4, NULL), (5, 10), write a query that correctly returns all orders where the discount is NOT equal to 10, including orders with no discount (NULL). Then explain why WHERE discount <> 10 alone is insufficient.SELECT COUNT(*) - COUNT(rating) AS null_count FROM reviews; to count NULL ratings. Explain how this works using the rules of NULL handling in aggregates. Then describe a scenario where the engineer should be careful about using AVG(rating) from the same table and what the implications are for data quality reporting.SELECT * FROM students WHERE gpa NOT IN (SELECT gpa FROM expelled_students); If the expelled_students table contains even one row where gpa is NULL, what happens to the entire result set? Prove your answer by expanding NOT IN into its equivalent logical form, and propose a robust alternative query.Lesson Summary
SQL operates under three-valued logic (3VL), extending classical Boolean algebra with a third truth value, UNKNOWN, which arises whenever a NULL marker participates in a comparison or expression. The logical connectives AND, OR, and NOT follow Kleene's strong logic (K₃), where AND behaves as min and OR as max over the ordering FALSE < UNKNOWN < TRUE. Critically, SQL's WHERE clause includes only rows that evaluate to TRUE, silently discarding both FALSE and UNKNOWN results — the most common source of NULL-related bugs.
Defensive programming patterns include using IS NULL / IS NOT NULL for explicit NULL checks, COALESCE for replacing NULLs with defaults, NOT EXISTS over NOT IN when subqueries may contain NULLs, and IS NOT DISTINCT FROM for NULL-safe equality. Understanding that 3VL breaks classical tautologies like the law of the excluded middle empowers you to anticipate, diagnose, and fix the subtle data quality issues that NULL introduces into every SQL codebase.