Loading learning content...
In conventional programming, boolean logic is straightforward: expressions evaluate to either true or false. This binary logic—called two-valued logic (2VL)—is intuitive because it matches how we typically think about truth and falsehood.
SQL, however, uses three-valued logic (3VL).
In SQL, boolean expressions can evaluate to three possible states: TRUE, FALSE, or UNKNOWN. The third value, UNKNOWN, arises whenever NULL is involved in a comparison or logical operation.
Understanding 3VL is not optional—it's essential. Every WHERE clause, every JOIN condition, every CASE expression operates under 3VL rules. If you don't understand these rules, you'll write queries that silently produce incorrect results, missing rows or including rows you didn't intend.
By the end of this page, you will understand the complete truth tables for three-valued logic, how UNKNOWN propagates through AND, OR, and NOT operations, why WHERE clauses only pass TRUE rows, and how to reason about complex boolean expressions that may involve NULL.
UNKNOWN is not just a quirky SQL feature—it's a logical necessity. When you compare a value against NULL, you're comparing a known value against an unknown value. The result of such a comparison cannot be definitively TRUE or FALSE; it must be unknown.
How UNKNOWN arises:
Any comparison involving NULL produces UNKNOWN:
| Expression | Result | Why |
|---|---|---|
5 = NULL | UNKNOWN | Is 5 equal to (unknown)? We don't know. |
5 <> NULL | UNKNOWN | Is 5 not equal to (unknown)? We don't know. |
NULL = NULL | UNKNOWN | Is (unknown) equal to (unknown)? We don't know. |
NULL > 10 | UNKNOWN | Is (unknown) greater than 10? We don't know. |
'hello' LIKE NULL | UNKNOWN | Does 'hello' match pattern (unknown)? We don't know. |
A helpful mental model: treat UNKNOWN as 'maybe'. When you ask 'Is 5 equal to NULL?', think of it as 'Is 5 equal to some unknown value?' The answer is 'maybe—it could be 5, or it could be something else.' SQL represents this 'maybe' as UNKNOWN.
The fundamental principle:
NULL represents a missing or unknown value. Any comparison or operation involving an unknown value cannot produce a definite TRUE or FALSE—it must remain uncertain. This uncertainty is captured by the UNKNOWN truth value.
This principle extends beyond simple comparisons:
-- Arithmetic with NULL produces NULL
SELECT 5 + NULL; -- Returns NULL (unknown number)
SELECT NULL * 10; -- Returns NULL (unknown number)
-- String operations with NULL produce NULL
SELECT 'Hello' || NULL; -- Returns NULL in most databases
-- Any comparison with these NULL results produces UNKNOWN
SELECT (5 + NULL) > 10; -- Returns UNKNOWN (NULL > 10 = UNKNOWN)
To work correctly with 3VL, you need to internalize the truth tables for AND, OR, and NOT. These extend the familiar 2VL tables with a third value.
| A AND B | B = TRUE | B = FALSE | B = UNKNOWN |
|---|---|---|---|
| A = TRUE | TRUE | FALSE | UNKNOWN |
| A = FALSE | FALSE | FALSE | FALSE |
| A = UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
AND returns TRUE only when both operands are TRUE. AND returns FALSE if either operand is FALSE. AND returns UNKNOWN only when neither operand is FALSE and at least one is UNKNOWN. Key insight: FALSE 'wins' over UNKNOWN in AND.
| A OR B | B = TRUE | B = FALSE | B = UNKNOWN |
|---|---|---|---|
| A = TRUE | TRUE | TRUE | TRUE |
| A = FALSE | TRUE | FALSE | UNKNOWN |
| A = UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
OR returns TRUE if either operand is TRUE. OR returns FALSE only when both operands are FALSE. OR returns UNKNOWN when neither is TRUE and at least one is UNKNOWN. Key insight: TRUE 'wins' over UNKNOWN in OR.
| A | NOT A |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
NOT UNKNOWN = UNKNOWN, not TRUE! This is counter-intuitive for programmers used to 2VL where NOT false = true. In 3VL, negating an unknown value doesn't make it known—it remains unknown. This is why WHERE NOT (column = NULL) doesn't return all rows.
The WHERE clause is the gatekeeper of query results. It evaluates a condition for each row and decides whether to include that row in the output.
Critical rule: WHERE only passes TRUE rows.
Rows where the condition is FALSE are excluded. Rows where the condition is UNKNOWN are also excluded. This is the key to understanding many NULL-related surprises.
123456789101112131415161718192021222324252627282930313233343536373839404142
-- Sample dataCREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(10,2)); INSERT INTO products VALUES(1, 'Widget', 10.00),(2, 'Gadget', NULL),(3, 'Thing', 20.00),(4, 'Stuff', NULL); -- Query 1: Find products with price > 15SELECT * FROM products WHERE price > 15;-- Result: Only row 3 (Thing, 20.00)-- -- Evaluation per row:-- Row 1: 10.00 > 15 = FALSE → excluded-- Row 2: NULL > 15 = UNKNOWN → excluded-- Row 3: 20.00 > 15 = TRUE → INCLUDED-- Row 4: NULL > 15 = UNKNOWN → excluded -- Query 2: Find products with price <= 15SELECT * FROM products WHERE price <= 15;-- Result: Only row 1 (Widget, 10.00)---- Note: Rows 2 and 4 are STILL excluded!-- They have UNKNOWN price, so both > and <= comparisons are UNKNOWN -- Query 3: What about NOT (price > 15)?SELECT * FROM products WHERE NOT (price > 15);-- Result: Only row 1 (Widget, 10.00)---- Evaluation per row:-- Row 1: NOT FALSE = TRUE → INCLUDED-- Row 2: NOT UNKNOWN = UNKNOWN → excluded-- Row 3: NOT TRUE = FALSE → excluded-- Row 4: NOT UNKNOWN = UNKNOWN → excluded -- The NULL rows are excluded from BOTH positive and negative searches!-- This is the "disappearing rows" phenomenon.Rows with NULL in the compared column disappear from BOTH WHERE column > value AND WHERE column <= value. They're not in either result set! This is because UNKNOWN is excluded from WHERE regardless of the comparison operator. To include these rows, you must explicitly handle NULL with OR column IS NULL.
123456789101112131415161718192021
-- To include NULL rows, handle them explicitly: -- Find products with price > 15 OR unknown priceSELECT * FROM products WHERE price > 15 OR price IS NULL;-- Returns rows 2, 3, 4 -- Find products with price <= 15 OR unknown priceSELECT * FROM products WHERE price <= 15 OR price IS NULL;-- Returns rows 1, 2, 4 -- Now the union of these two queries covers ALL rows-- (which is what we'd expect mathematically) -- Practical example: Find products NOT in a specific price range-- Include products with unknown price (they might be in range)SELECT * FROM productsWHERE price < 5 OR price > 25 OR price IS NULL;When multiple conditions combine with AND, OR, and NOT, the 3VL rules apply at each step. Understanding propagation through complex expressions is essential for debugging query results.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
-- Complex expression evaluation example-- Given: A = TRUE, B = UNKNOWN, C = FALSE -- Expression: A AND B OR C-- Step 1: A AND B = TRUE AND UNKNOWN = UNKNOWN-- Step 2: UNKNOWN OR C = UNKNOWN OR FALSE = UNKNOWN-- Result: UNKNOWN -- Expression: A OR B AND C -- Step 1: B AND C = UNKNOWN AND FALSE = FALSE (FALSE wins)-- Step 2: A OR FALSE = TRUE OR FALSE = TRUE-- Result: TRUE -- Practical SQL exampleCREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10,2)); INSERT INTO employees VALUES(1, 'Alice', 'Engineering', 80000),(2, 'Bob', 'Sales', NULL),(3, 'Carol', NULL, 90000),(4, 'Dave', NULL, NULL); -- Find: Engineering employees OR employees with salary > 85000SELECT * FROM employeesWHERE department = 'Engineering' OR salary > 85000; -- Row-by-row evaluation:-- Row 1: 'Engineering' = 'Engineering' OR 80000 > 85000-- = TRUE OR FALSE = TRUE → included---- Row 2: 'Sales' = 'Engineering' OR NULL > 85000-- = FALSE OR UNKNOWN = UNKNOWN → excluded---- Row 3: NULL = 'Engineering' OR 90000 > 85000 -- = UNKNOWN OR TRUE = TRUE → included---- Row 4: NULL = 'Engineering' OR NULL > 85000-- = UNKNOWN OR UNKNOWN = UNKNOWN → excluded -- Result: Rows 1 and 3 (Alice and Carol) -- Now with AND:SELECT * FROM employeesWHERE department = 'Engineering' AND salary > 70000; -- Row-by-row evaluation:-- Row 1: 'Engineering' = 'Engineering' AND 80000 > 70000-- = TRUE AND TRUE = TRUE → included---- Row 2: 'Sales' = 'Engineering' AND NULL > 70000-- = FALSE AND UNKNOWN = FALSE → excluded---- Row 3: NULL = 'Engineering' AND 90000 > 70000-- = UNKNOWN AND TRUE = UNKNOWN → excluded---- Row 4: NULL = 'Engineering' AND NULL > 70000-- = UNKNOWN AND UNKNOWN = UNKNOWN → excluded -- Result: Only Row 1 (Alice)When debugging complex WHERE conditions with NULL, evaluate step by step: identify which comparisons produce UNKNOWN, then apply AND/OR rules to combine them. Remember: FALSE dominates in AND, TRUE dominates in OR, and NOT UNKNOWN = UNKNOWN.
In many programming languages, boolean expressions short-circuit: if the first operand determines the result, the second operand isn't evaluated. SQL's handling of short-circuit evaluation is not guaranteed by the standard and varies by implementation.
Why this matters for NULL:
You might be tempted to write conditions like:
WHERE column IS NOT NULL AND column > 0
Assuming the second part won't be evaluated if column IS NULL. While this often works, the SQL standard doesn't guarantee it. The optimizer might reorder conditions for efficiency.
1234567891011121314151617181920212223242526272829303132
-- Short-circuit example (behavior varies by DBMS) -- Potentially safe because IS NOT NULL comes firstSELECT * FROM productsWHERE quantity IS NOT NULL AND (total_value / quantity) > 100; -- But the optimizer might evaluate conditions in any order!-- If quantity IS NULL and division is evaluated first,-- you could get a division-by-zero or NULL error. -- Safer approach: Use CASE to guarantee evaluation orderSELECT * FROM productsWHERE CASE WHEN quantity IS NULL THEN FALSE WHEN quantity = 0 THEN FALSE ELSE (total_value / quantity) > 100END = TRUE; -- Or NULLIF / COALESCE to protect against the issueSELECT * FROM productsWHERE total_value / NULLIF(quantity, 0) > 100;-- NULLIF returns NULL if quantity = 0, and NULL/0 = NULL-- Then NULL > 100 = UNKNOWN, row excluded safely -- Some databases document short-circuit behavior:-- PostgreSQL: Generally left-to-right in AND/OR, but not guaranteed-- Oracle: No guaranteed order of evaluation-- SQL Server: May evaluate in any order for optimization -- Best practice: Don't rely on short-circuit evaluation.-- Write conditions that are safe regardless of evaluation order.Unlike most programming languages, SQL doesn't guarantee left-to-right evaluation of AND/OR conditions. The query optimizer may reorder conditions based on estimated cost. Write conditions that are safe regardless of evaluation order, or use CASE expressions when order matters.
3VL affects not just WHERE clauses, but many SQL contexts. Understanding where and how it applies helps predict query behavior.
| Context | UNKNOWN Behavior | Notes |
|---|---|---|
| WHERE clause | Excludes row | Only TRUE passes |
| HAVING clause | Excludes group | Same as WHERE for groups |
| JOIN ON condition | No match | UNKNOWN treated as non-match |
| CASE WHEN | Falls through | UNKNOWN doesn't trigger the WHEN |
| CHECK constraint | Passes | UNKNOWN is not a violation! |
| UNIQUE constraint | Special handling | NULLs often allowed as duplicates |
| IN list | May return UNKNOWN | Depends on NULL in list |
| NOT IN list | Often returns UNKNOWN | The infamous NOT IN trap |
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
-- CASE WHEN with UNKNOWNSELECT product_name, price, CASE WHEN price > 100 THEN 'Premium' WHEN price > 50 THEN 'Standard' WHEN price > 0 THEN 'Budget' -- What about NULL prices? -- All comparisons are UNKNOWN, so no WHEN matches -- Falls through to ELSE (or NULL if no ELSE) ELSE 'Unknown' END AS tierFROM products; -- CHECK constraint with UNKNOWNCREATE TABLE salaries ( id INT PRIMARY KEY, amount DECIMAL(10,2), CONSTRAINT chk_positive CHECK (amount > 0)); INSERT INTO salaries VALUES (1, 50000); -- OK: 50000 > 0 = TRUEINSERT INTO salaries VALUES (2, NULL); -- OK! NULL > 0 = UNKNOWN, passesINSERT INTO salaries VALUES (3, -100); -- FAILS: -100 > 0 = FALSE -- This is why CHECK constraints use "NOT FALSE" semantics,-- not "IS TRUE" semantics. To reject NULLs, add explicit check:CREATE TABLE salaries_strict ( id INT PRIMARY KEY, amount DECIMAL(10,2), CONSTRAINT chk_positive CHECK (amount > 0 AND amount IS NOT NULL) -- Or simply: amount DECIMAL(10,2) NOT NULL CHECK (amount > 0)); -- JOIN ON with UNKNOWNSELECT o.*, c.nameFROM orders oJOIN customers c ON o.customer_id = c.id;-- If o.customer_id is NULL:-- NULL = c.id → UNKNOWN for all customers → no match -- IN with NULL in the listSELECT * FROM products WHERE category_id IN (1, 2, NULL);-- For category_id = 1: TRUE (matches 1)-- For category_id = 3: FALSE OR FALSE OR UNKNOWN = UNKNOWN-- But UNKNOWN doesn't affect matching; only TRUE matches are returned-- So this works like IN (1, 2) for practical purposes -- NOT IN with NULL in the list (THE TRAP)SELECT * FROM products WHERE category_id NOT IN (1, 2, NULL);-- For category_id = 3:-- NOT (FALSE OR FALSE OR UNKNOWN) = NOT UNKNOWN = UNKNOWN-- Row is excluded! This returns ZERO rows (or very few).CHECK constraints use 'NOT FALSE' semantics: they reject only FALSE, allowing both TRUE and UNKNOWN. This means NULL values pass CHECK constraints unless you explicitly add an IS NOT NULL check. This surprises many developers expecting CHECK to reject NULLs.
When queries return unexpected results involving NULL, systematic debugging helps identify where 3VL causes the problem.
SELECT * FROM table WHERE column IS NULL to see how many NULLs existcolumn = @variable fail silently if @variable is NULL12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061
-- Debugging technique: Expose the boolean evaluation -- Original query returning unexpected resultsSELECT * FROM employeesWHERE department = 'Sales' AND salary > 50000; -- Debug query: Show what each condition evaluates toSELECT id, name, department, salary, -- Expose individual condition results CASE WHEN department = 'Sales' THEN 'TRUE' WHEN department <> 'Sales' THEN 'FALSE' ELSE 'UNKNOWN' END AS dept_check, CASE WHEN salary > 50000 THEN 'TRUE' WHEN salary <= 50000 THEN 'FALSE' ELSE 'UNKNOWN' END AS salary_check, -- Expose combined result using 3VL AND rules CASE WHEN department = 'Sales' AND salary > 50000 THEN 'TRUE' WHEN department <> 'Sales' OR salary <= 50000 THEN 'FALSE' ELSE 'UNKNOWN' END AS combinedFROM employees; -- Quick NULL detection in all columnsSELECT 'department' AS column_name, COUNT(*) AS total_rows, COUNT(department) AS non_null_count, COUNT(*) - COUNT(department) AS null_countFROM employeesUNION ALLSELECT 'salary', COUNT(*), COUNT(salary), COUNT(*) - COUNT(salary)FROM employees; -- Debugging NOT IN issue-- Instead of:SELECT * FROM products WHERE category_id NOT IN (SELECT id FROM excluded); -- Debug by seeing what the subquery returns:SELECT id, CASE WHEN id IS NULL THEN 'NULL FOUND!' ELSE 'OK' ENDFROM excluded; -- If NULLs exist, that's your problem. Fix with:SELECT * FROM products WHERE category_id NOT IN ( SELECT id FROM excluded WHERE id IS NOT NULL);-- OR better:SELECT * FROM products pWHERE NOT EXISTS ( SELECT 1 FROM excluded e WHERE e.id = p.category_id);The key to debugging 3VL issues is making the boolean evaluation visible. Use CASE expressions to expose TRUE/FALSE/UNKNOWN for each condition, then apply 3VL rules manually. This quickly reveals where UNKNOWN is causing rows to disappear.
We've thoroughly explored three-valued logic—the foundation of all NULL behavior in SQL. Let's consolidate the key insights:
What's next:
Now that we understand the logic behind NULL, we'll learn practical tools for handling it: COALESCE and NVL. These functions provide default values when NULL appears, allowing you to transform UNKNOWN situations into definite results.
You now understand three-valued logic thoroughly—the mathematical foundation that explains all NULL behavior in SQL. When queries produce unexpected results involving NULL, you can now trace through the 3VL rules to understand why. Next, we'll learn COALESCE and NVL to handle NULL values gracefully.