Three-Valued Logic & NULL — Understand three-valued logic with NULL in comparisons (conceptual) - SQL

SECTION 1

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.

1847 • Boole's Two-Valued Logic

George Boole publishes The Mathematical Analysis of Logic, establishing the formal algebra of TRUE and FALSE that becomes the foundation of digital computing.

1920 • Łukasiewicz's Multi-Valued Logic

Polish logician Jan Łukasiewicz proposes three-valued and many-valued logics, introducing a third truth value to handle propositions whose truth status is indeterminate — a direct intellectual precursor to SQL's UNKNOWN.

1970 • Codd's Relational Model

E. F. Codd publishes his landmark paper introducing the relational model and the concept of NULL to represent missing information in database relations.

1986 • SQL-86 Standard

The first ANSI/ISO SQL standard formally adopts three-valued logic, mandating that comparisons involving NULL evaluate to UNKNOWN and that WHERE clauses filter rows based on TRUE outcomes only.

1999 • SQL:1999 & Ongoing Debate

SQL:1999 refines NULL handling with COALESCE, NULLIF, and enhanced CASE expressions. Prominent database theorists like C. J. Date argue that NULLs introduce logical inconsistencies that undermine relational purity — a debate that continues today.

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.

SECTION 2

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

NULL is a marker indicating the absence of a value. It is not zero, not an empty string, and not false. You cannot compare it using = or != — you must use IS NULL or IS NOT NULL.

Comparisons Yield UNKNOWN

Any comparison involving NULL — including NULL = NULL — evaluates to UNKNOWN, not TRUE or FALSE. This is because you cannot determine equality between two unknowns.

WHERE Filters on TRUE Only

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

AND, OR, and NOT follow extended truth tables. Key surprises: 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

Functions like 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.

✦

KEY TAKEAWAY

Think of NULL as a sealed envelope. There is a value inside, but you cannot see it. If someone asks, "Is the number in your envelope equal to 7?" you cannot honestly answer yes or no — you must answer "I don't know." That answer is UNKNOWN. Now imagine a WHERE clause that says "show me all envelopes containing 7." Your sealed envelope will be excluded, even if it actually does contain 7, because the database cannot verify the condition. This is precisely how SQL treats NULLs: uncertainty propagates through every logical operation, and only rows that demonstrably satisfy the predicate are returned.

SECTION 3

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 three truth tables show how AND, OR, and NOT behave with the addition of UNKNOWN. Cells highlighted in amber are the entries that have no counterpart in classical two-valued logic. The bottom panel illustrates that WHERE treats UNKNOWN identically to FALSE — both result in row exclusion.

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.

SECTION 4

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.

AND OPERATOR

A AND B = min(A, B)

Where the ordering is F < U < T. For example, min(T, U) = U and min(F, U) = F.

OR OPERATOR

A OR B = max(A, B)

For example, max(T, U) = T and max(F, U) = U.

NOT OPERATOR

NOT A = 1 − A (mapping F=0, U=½, T=1)

NOT TRUE = 1 − 1 = 0 = FALSE; NOT UNKNOWN = 1 − ½ = ½ = UNKNOWN. This numeric mapping demonstrates why NOT UNKNOWN yields UNKNOWN: it is the fixed point of the negation function.

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.

⚠

Classical Tautology Violations in SQL

The expression 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.

SECTION 5

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.

This flowchart traces NULL propagation through a compound expression. A NULL 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.

Common NULL expressions and their results

SECTION 6

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.

Finding Employees Without a $500 Bonus

1
Step 1 — The Buggy Query

The developer writes: 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.

Employees with NULL bonus are silently excluded.
2
Step 2 — Trace the Logic for a NULL Row

Suppose employee Alice has 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.

NULL <> 500 → UNKNOWN → row excluded
3
Step 3 — Apply the Fix Using IS NULL

To include employees with no bonus, we must explicitly handle the NULL case: 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.

UNKNOWN OR TRUE → TRUE → row included ✓
4
Step 4 — Alternative Fix Using COALESCE

An alternative approach replaces NULL with a sentinel value before comparison: 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.

COALESCE(NULL, -1) = -1; -1 <> 500 → TRUE → row included ✓
5
Step 5 — Verify with IS NOT DISTINCT FROM

SQL:1999 introduced 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.

IS NOT DISTINCT FROM provides NULL-safe comparison — all three fixes yield the correct result.

SECTION 7

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

Common 3VL pitfalls and their defensive counterparts

✦

KEY TAKEAWAY

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

SECTION 8

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)

SQL's three-valued logic versus proposed alternatives

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.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why NULL = NULL evaluates to UNKNOWN rather than TRUE. How does this differ from most programming languages' treatment of null equality?

Show answer

In SQL, NULL represents an unknown value, not a specific sentinel. Since two unknowns could represent entirely different underlying values, it is logically impossible to conclude they are equal — hence the result is UNKNOWN. This contrasts with languages like Python or Java, where None == None or null == null evaluates to true because null is treated as a concrete singleton object. SQL's approach preserves logical consistency with the semantics of missing data, whereas programming languages treat null as a value for practical convenience.

Question 2: BASIC CALCULATION

Evaluate the following expression step by step: (TRUE AND UNKNOWN) OR (FALSE AND UNKNOWN). What is the final result?

Show answer

First, evaluate the left sub-expression: TRUE AND UNKNOWN = min(T, U) = UNKNOWN. Next, evaluate the right sub-expression: FALSE AND UNKNOWN = min(F, U) = FALSE (because FALSE dominates AND). Finally, combine: UNKNOWN OR FALSE = max(U, F) = UNKNOWN. The final result is UNKNOWN. This illustrates that once UNKNOWN enters an expression, it tends to persist unless an operator can short-circuit it (e.g., OR with TRUE, or AND with FALSE).

Question 3: INTERMEDIATE

Given a table 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.

Show answer

The correct query is: SELECT * FROM orders WHERE discount <> 10 OR discount IS NULL; This returns orders 2, 3, and 4. Using only WHERE discount <> 10 returns only order 3, because for orders 2 and 4, NULL <> 10 evaluates to UNKNOWN, and WHERE excludes UNKNOWN rows. Adding OR discount IS NULL ensures the NULL-discount rows are explicitly included. An alternative is WHERE COALESCE(discount, -1) <> 10 using a sentinel value.

Question 4: APPLIED

A data engineer writes 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.

Show answer

This works because COUNT(*) counts all rows regardless of NULL values, while COUNT(rating) counts only rows where rating is not NULL. The difference gives the number of NULL ratings. For AVG(rating), NULLs are excluded from both the numerator and denominator. If 30% of ratings are NULL, the average reflects only the 70% who responded, which could introduce selection bias — users who leave ratings may skew higher or lower than non-responders. For accurate data quality reporting, the engineer should report both the average and the NULL percentage, and consider whether AVG(COALESCE(rating, default)) is more appropriate for the business context.

Question 5: CRITICAL THINKING

Consider a subquery: 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.

Show answer

The result set becomes empty. To see why, expand NOT IN: gpa NOT IN (3.5, NULL) is equivalent to gpa <> 3.5 AND gpa <> NULL. The second conjunct gpa <> NULL is always UNKNOWN, regardless of the student's actual GPA. Since X AND UNKNOWN ≤ UNKNOWN (by the min rule), the entire WHERE expression can never evaluate to TRUE. Every row is excluded. This is perhaps the most dangerous NULL pitfall in SQL. The robust alternative is: SELECT * FROM students s WHERE NOT EXISTS (SELECT 1 FROM expelled_students e WHERE e.gpa = s.gpa); NOT EXISTS returns TRUE when the subquery finds no matching rows and FALSE otherwise — it never produces UNKNOWN, sidestepping the 3VL trap entirely.

SUMMARY

Lesson Summary

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.