skip to content

How do AND, OR and NOT behave when an operand is UNKNOWN in SQL's three-valued logic?

level: middleimportance: must knowfreq 60%

answer

  1. One operand can decide the outcome alone
  2. FALSE wins one connective, TRUE the other
  3. Negating uncertainty does not resolve it
  4. A predicate and its negation are not exhaustive
  5. De Morgan survives; excluded middle does not

basics

~10 s

UNKNOWN propagates unless the other operand already decides the result: FALSE AND UNKNOWN is FALSE, TRUE OR UNKNOWN is TRUE, and every other combination with UNKNOWN — including NOT UNKNOWN — stays UNKNOWN.

solid answer

~40 s

SQL's connectives are defined over three truth values, so the truth tables extend rather than replace boolean logic. For `AND`, the result is FALSE if either side is FALSE (even when the other is UNKNOWN), TRUE only if both are TRUE, and UNKNOWN otherwise. For `OR`, it is TRUE if either side is TRUE (even against UNKNOWN), FALSE only if both are FALSE, and UNKNOWN otherwise. `NOT UNKNOWN` is UNKNOWN — negation cannot turn "cannot tell" into a decision. Two consequences matter in practice: an UNKNOWN operand can still be absorbed by a decisive partner, so a NULL does not automatically poison a compound predicate; and familiar boolean identities fail. `p OR NOT p` is not a tautology in SQL, because it evaluates to UNKNOWN when `p` is UNKNOWN, which `WHERE` then discards.

code

sql · 6 lines
sql
-- discount is NULL for this row
-- FALSE absorbs UNKNOWN: whole condition is FALSE
SELECT * FROM orders WHERE region = 'EU' AND discount > 10;

-- TRUE absorbs UNKNOWN: whole condition is TRUE for EU rows
SELECT * FROM orders WHERE region = 'EU' OR discount > 10;

go deeper

for a junior

Know that a third truth value exists and that NULL comparisons produce it. Being able to say FALSE AND UNKNOWN is FALSE and TRUE OR UNKNOWN is TRUE already puts you ahead at this stage.

for a middle

Reproduce all three truth tables and explain the absorption rules: FALSE dominates AND, TRUE dominates OR, and NOT leaves UNKNOWN untouched. Show why that makes OR col IS NULL the standard escape hatch.

for a senior

Demonstrate the consequences in real code — filters that lose rows, predicate/negation splits that do not add up, and refactors that look logically equivalent but change the result set over nullable columns.

for a principal

Frame it as a design cost: every nullable column forces three-valued reasoning on every consumer and invalidates rewrites people assume are safe, which is a real argument when deciding what may be absent in a schema.

## The three truth values A SQL search condition evaluates to TRUE, FALSE, or UNKNOWN. UNKNOWN arises whenever a comparison touches NULL: `x = 1` is UNKNOWN for every row where `x` is NULL. The connectives `AND`, `OR` and `NOT` then have to say what happens when one of their inputs is UNKNOWN, and the standard defines them by extending the ordinary boolean tables. ## AND | AND | TRUE | FALSE | UNKNOWN | |---|---|---|---| | **TRUE** | TRUE | FALSE | UNKNOWN | | **FALSE** | FALSE | FALSE | FALSE | | **UNKNOWN** | UNKNOWN | FALSE | UNKNOWN | The rule in words: **FALSE dominates**. If either operand is FALSE the conjunction is FALSE, even if the other side is unknowable — knowing one conjunct fails is enough to know the whole fails. Otherwise, an UNKNOWN operand leaves the result UNKNOWN. ## OR | OR | TRUE | FALSE | UNKNOWN | |---|---|---|---| | **TRUE** | TRUE | TRUE | TRUE | | **FALSE** | TRUE | FALSE | UNKNOWN | | **UNKNOWN** | TRUE | UNKNOWN | UNKNOWN | Here **TRUE dominates**: one satisfied disjunct settles the question regardless of what the other side would have been. This is the property that makes the standard NULL escape hatch work — `WHERE status = 'active' OR status IS NULL` is TRUE for a NULL row because `IS NULL` supplies a decisive TRUE. ## NOT | p | NOT p | |---|---| | TRUE | FALSE | | FALSE | TRUE | | UNKNOWN | UNKNOWN | Negation is the one people get wrong. `NOT UNKNOWN` is UNKNOWN, not TRUE. Wrapping a failing predicate in `NOT` therefore never recovers the rows it lost: ```sql -- both of these drop rows where status is NULL SELECT * FROM orders WHERE status = 'shipped'; SELECT * FROM orders WHERE NOT (status = 'shipped'); ``` ## Absorption: NULL does not always poison the predicate A useful nuance, and a good interview differentiator: a NULL in one operand does **not** automatically make the whole condition unknowable. Consider a row where `discount` is NULL: ```sql WHERE region = 'EU' AND discount > 10 -- FALSE if region <> 'EU' WHERE region = 'EU' OR discount > 10 -- TRUE if region = 'EU' ``` In the first case a FALSE from the region test absorbs the UNKNOWN and the whole condition is definitively FALSE. In the second a TRUE from the region test absorbs it. Only when the decisive partner is missing does UNKNOWN survive to the top of the expression — where `WHERE` discards it. Note that this is about *logical* results, not about evaluation order. SQL does not guarantee left-to-right short-circuiting the way many procedural languages do; the truth tables define the answer regardless of which side the engine evaluates first. ## Identities that stop holding Because of the third value, several laws you rely on in boolean algebra are no longer safe rewrites over NULLable columns: - **Excluded middle**: `p OR NOT p` is UNKNOWN when `p` is UNKNOWN, so it is not a tautology and `WHERE p OR NOT p` does not return every row. - **Non-contradiction**: `p AND NOT p` is UNKNOWN rather than FALSE in the same case — harmless in `WHERE`, but relevant if you reason about the expression's value. - **Complementary filters**: splitting a table with `WHERE p` and `WHERE NOT p` does not partition it; rows where `p` is UNKNOWN fall out of both halves. De Morgan's laws, by contrast, *do* still hold in three-valued logic: `NOT (a AND b)` is equivalent to `NOT a OR NOT b`, and vice versa. So refactoring predicates with De Morgan is safe; assuming a predicate and its negation are exhaustive is not. ## Testing the third value directly The standard provides boolean test predicates — `IS TRUE`, `IS FALSE`, `IS UNKNOWN`, and their `IS NOT` forms — which convert a three-valued expression into a two-valued answer. `x = 1 IS NOT TRUE` is TRUE both when `x` is 2 and when `x` is NULL, which is occasionally exactly what you want. Engine support for these varies, so check before relying on them; the always-portable alternative is to add an explicit `OR x IS NULL` branch. ## How to answer Give the two absorption rules first — FALSE dominates `AND`, TRUE dominates `OR` — then `NOT UNKNOWN = UNKNOWN`, then the practical payoff: a NULL only sinks a predicate when nothing else in it is decisive, and predicate/negation pairs are not exhaustive.

  • Is p OR NOT p a tautology in SQL?
    No. When `p` evaluates to UNKNOWN — for instance `salary > 100` on a row with a NULL salary — both `p` and `NOT p` are UNKNOWN, and `UNKNOWN OR UNKNOWN` is UNKNOWN. `WHERE p OR NOT p` therefore silently drops those rows instead of returning the whole table, which is a favourite interview demonstration that SQL logic is not boolean logic.
  • Do De Morgan's laws still hold with three-valued logic?
    Yes. `NOT (a AND b)` is equivalent to `NOT a OR NOT b`, and `NOT (a OR b)` to `NOT a AND NOT b`, under all nine combinations of TRUE, FALSE and UNKNOWN. Refactoring predicates that way is safe; what fails is the assumption that a predicate and its negation together cover every row.
  • How can you turn a three-valued expression into a definite TRUE or FALSE?
    Use the standard boolean test predicates — `IS TRUE`, `IS FALSE`, `IS UNKNOWN` and their `IS NOT` forms — which always return a two-valued result: `col = 1 IS NOT TRUE` is TRUE both for non-matching values and for NULLs. Engine support varies, so the portable fallback is an explicit `OR col IS NULL` branch.

Think of UNKNOWN as an abstaining voter. In an AND vote a single "no" defeats the motion whatever the abstainer would have said; in an OR vote a single "yes" carries it. Only when every other voter is indecisive does the abstention determine the outcome.

saying these in an interview costs you the question

  • Saying NOT UNKNOWN evaluates to TRUE
  • Claiming any NULL operand makes the whole predicate UNKNOWN
  • Believing WHERE p and WHERE NOT p partition the table
  • Treating UNKNOWN as a synonym for FALSE inside expressions
  • Assuming SQL short-circuits AND/OR left to right like C or Java

context

resolving links…