Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL - Deep Dive

Overview - Three-valued logic (TRUE, FALSE, UNKNOWN)

What is it?

Three-valued logic in SQL is a way to handle conditions that can be true, false, or unknown. Unlike simple true/false logic, it adds a third state called UNKNOWN, which happens when data is missing or NULL. This helps databases make decisions even when some information is incomplete. It is essential for filtering and comparing data correctly.

Why it matters

Without three-valued logic, databases would treat missing or NULL data as either true or false, leading to wrong results or errors. This logic allows SQL to handle incomplete information safely and predictably. It ensures queries return accurate results even when some data is unknown, which is common in real-world databases.

Where it fits

Before learning three-valued logic, you should understand basic SQL queries and boolean logic (true/false). After this, you can learn about NULL handling, filtering with WHERE clauses, and advanced SQL functions that rely on this logic.

Mental Model

Core Idea

In SQL, conditions can be true, false, or unknown, and this third state changes how queries decide what data to include.

Think of it like...

Imagine a traffic light with three colors: green (go), red (stop), and yellow (wait). The yellow light means you don’t know if you should go or stop yet, just like UNKNOWN in SQL means the condition’s truth is unclear.

┌───────────────┐
│ Condition     │
├───────────────┤
│ TRUE          │
│ FALSE         │
│ UNKNOWN (NULL)│
└───────────────┘

Decision flow:
TRUE  → Include row
FALSE → Exclude row
UNKNOWN → Exclude row (unless handled explicitly)

Build-Up - 7 Steps

1

Foundation

Understanding Boolean Logic Basics

Concept: Learn how SQL uses TRUE and FALSE in conditions.

In SQL, conditions like 'age > 18' return TRUE if the age is over 18, and FALSE if not. This is simple yes/no logic that decides which rows to keep in a query.

Result

Queries filter rows based on TRUE or FALSE conditions.

Understanding basic true/false logic is essential before adding the complexity of UNKNOWN.

2

Foundation

Introducing NULL and Its Meaning

Concept: NULL represents missing or unknown data in SQL.

NULL means we don’t know the value of a field. For example, if a person's birthdate is unknown, that field is NULL. NULL is not zero or empty; it means 'no information'.

Result

NULL values appear in data and affect how conditions evaluate.

Recognizing NULL as unknown data is key to understanding why three-valued logic exists.

3

Intermediate

How NULL Affects Condition Results

🤔Before reading on: Do you think 'age > 18' returns TRUE, FALSE, or UNKNOWN if age is NULL? Commit to your answer.

Concept: Conditions involving NULL do not return TRUE or FALSE but UNKNOWN.

If you compare NULL to a number, like 'age > 18', the result is UNKNOWN because SQL cannot say if NULL is greater than 18 or not. This UNKNOWN is different from TRUE or FALSE.

Result

Conditions with NULL produce UNKNOWN, affecting query filtering.

Knowing that NULL causes UNKNOWN results explains why some rows are excluded unexpectedly.

4

Intermediate

Three-Valued Logic Truth Table

🤔Before reading on: Do you think 'TRUE AND UNKNOWN' is TRUE, FALSE, or UNKNOWN? Commit to your answer.

Concept: SQL uses a truth table with TRUE, FALSE, and UNKNOWN to evaluate logical expressions.

Here is the truth table for AND: TRUE AND TRUE = TRUE TRUE AND FALSE = FALSE TRUE AND UNKNOWN = UNKNOWN FALSE AND UNKNOWN = FALSE UNKNOWN AND UNKNOWN = UNKNOWN Similarly for OR: TRUE OR UNKNOWN = TRUE FALSE OR UNKNOWN = UNKNOWN UNKNOWN OR UNKNOWN = UNKNOWN NOT UNKNOWN = UNKNOWN

Result

Logical operations follow these rules, affecting query results.

Understanding this table helps predict how complex conditions behave with NULLs.

5

Intermediate

Filtering Rows with WHERE and UNKNOWN

🤔Before reading on: Will rows where condition is UNKNOWN appear in a WHERE filter? Yes or No? Commit to your answer.

Concept: WHERE filters only include rows where the condition is TRUE, excluding FALSE and UNKNOWN.

If a condition evaluates to UNKNOWN, the row is excluded from the result. For example, 'WHERE age > 18' excludes rows where age is NULL because the condition is UNKNOWN.

Result

Rows with NULLs in conditions are often filtered out unless handled explicitly.

Knowing that UNKNOWN excludes rows explains why NULL data can disappear from query results.

6

Advanced

Using IS NULL and IS NOT NULL to Handle UNKNOWN

🤔Before reading on: Can 'IS NULL' be used to detect UNKNOWN values? Yes or No? Commit to your answer.

Concept: Special operators detect NULL values explicitly, bypassing three-valued logic filtering.

SQL provides 'IS NULL' and 'IS NOT NULL' to check for NULL values directly. For example, 'WHERE age IS NULL' returns rows where age is unknown. This lets you include or exclude NULLs intentionally.

Result

You can control how NULLs affect query results.

Understanding these operators is crucial to managing UNKNOWN values in real queries.

7

Expert

Impact of Three-Valued Logic on JOINs and Indexes

🤔Before reading on: Do you think NULLs in JOIN conditions behave like TRUE, FALSE, or UNKNOWN? Commit to your answer.

Concept: Three-valued logic affects how JOINs match rows and how indexes optimize queries with NULLs.

In JOINs, conditions with NULL produce UNKNOWN, so rows with NULLs often don’t match. Also, indexes may treat NULLs specially, affecting performance. Some databases store NULLs differently in indexes, which can surprise developers.

Result

JOIN results and query speed can be affected by three-valued logic and NULL handling.

Knowing this prevents subtle bugs and performance issues in complex queries.

Under the Hood

SQL evaluates conditions using a logic system that includes TRUE, FALSE, and UNKNOWN. When a comparison involves NULL, the result is UNKNOWN because NULL means 'no value'. The database engine uses this logic to decide which rows satisfy conditions. Internally, UNKNOWN is treated as neither true nor false, so it excludes rows in WHERE filters unless explicitly handled.

Why designed this way?

Three-valued logic was introduced to handle incomplete data safely. Early databases struggled with NULLs causing errors or misleading results. By adding UNKNOWN, SQL can represent missing information without guessing. This design balances correctness and practicality, avoiding false assumptions about unknown data.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Condition     │──────▶│ TRUE          │──────▶│ Include Row   │
│ (e.g. age>18) │       │ FALSE         │──────▶│ Exclude Row   │
│               │       │ UNKNOWN (NULL)│──────▶│ Exclude Row   │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does 'NULL = NULL' return TRUE or FALSE? Commit to your answer.

Common Belief: Many think NULL equals NULL because they look the same.

Reality: In SQL, 'NULL = NULL' returns UNKNOWN, not TRUE, because NULL means unknown value, so two unknowns can’t be confirmed equal.

Why it matters: Assuming NULL equals NULL can cause wrong query results and logic errors, especially in joins and filters.

Quick: Does WHERE NULL return TRUE or FALSE? Commit to your answer.

Common Belief: Some believe 'WHERE NULL' acts like FALSE and excludes rows.

Reality: 'WHERE NULL' evaluates to UNKNOWN, which excludes rows, but it is not the same as FALSE logically.

Why it matters: Confusing UNKNOWN with FALSE can lead to unexpected filtering behavior.

Quick: Does 'NOT UNKNOWN' become TRUE or FALSE? Commit to your answer.

Common Belief: People often think NOT UNKNOWN flips to TRUE or FALSE.

Reality: NOT UNKNOWN remains UNKNOWN in SQL’s three-valued logic.

Why it matters: Misunderstanding NOT UNKNOWN can cause logical errors in complex conditions.

Quick: Can NULL values be indexed like normal values? Commit to your answer.

Common Belief: Many assume NULLs are always indexed the same as other values.

Reality: Some databases treat NULLs specially in indexes, sometimes excluding them or storing them differently.

Why it matters: Assuming NULLs are indexed normally can cause performance surprises and incorrect query plans.

Expert Zone

1

Three-valued logic affects not just WHERE but also CHECK constraints and triggers, influencing data integrity.

2

Some SQL dialects extend three-valued logic with additional functions like COALESCE and NULLIF to manage UNKNOWN values elegantly.

3

Query optimizers use knowledge of three-valued logic to rewrite queries, but subtle differences in NULL handling can cause different execution plans.

When NOT to use

Avoid relying solely on three-valued logic when you need strict true/false decisions; instead, use explicit NULL checks or default values. For example, in application logic, handle NULLs before querying to simplify conditions.

Production Patterns

In real systems, developers combine three-valued logic with IS NULL checks and COALESCE to handle missing data gracefully. They also design schemas to minimize NULLs where possible and use partial indexes or filtered queries to optimize performance.

Connections

Boolean Algebra

Three-valued logic extends classical Boolean algebra by adding an UNKNOWN state.

Understanding classical Boolean algebra helps grasp how SQL’s logic adds complexity to handle real-world unknowns.

Fault Tolerant Systems

Both handle uncertainty and incomplete information to avoid wrong decisions.

Knowing how fault tolerant systems manage unknown states clarifies why SQL uses three-valued logic to maintain data correctness.

Philosophy of Knowledge (Epistemology)

Three-valued logic reflects the idea that knowledge can be true, false, or unknown.

Recognizing this philosophical basis deepens appreciation for why databases model unknown data explicitly.

Common Pitfalls

#1 Filtering with 'WHERE column = NULL' expecting to find NULLs.

Wrong approach:

SELECT * FROM users WHERE age = NULL;

Correct approach:

SELECT * FROM users WHERE age IS NULL;

Root cause:

Misunderstanding that NULL is not equal to anything, even itself, so '=' does not work with NULL.

#2 Assuming 'NOT NULL' means the value is FALSE in conditions.

Wrong approach:

SELECT * FROM orders WHERE NOT shipped_date = NULL;

Correct approach:

SELECT * FROM orders WHERE shipped_date IS NOT NULL;

Root cause:

Confusing logical NOT with NULL checks; NOT does not convert UNKNOWN to TRUE or FALSE.

#3 Using 'WHERE condition OR NULL' to include NULL rows.

Wrong approach:

SELECT * FROM products WHERE price > 100 OR NULL;

Correct approach:

SELECT * FROM products WHERE price > 100 OR price IS NULL;

Root cause:

NULL in logical expressions does not act as a boolean value and does not include NULL rows automatically.

Key Takeaways

SQL uses three-valued logic to handle TRUE, FALSE, and UNKNOWN (NULL) conditions safely.

UNKNOWN results from comparisons involving NULL and causes rows to be excluded in WHERE filters unless handled explicitly.

Special operators like IS NULL and IS NOT NULL are needed to detect and work with NULL values correctly.

Understanding three-valued logic prevents common mistakes in filtering, joining, and indexing data with NULLs.

This logic reflects real-world uncertainty and helps databases manage incomplete information reliably.