SQL Semantics

WHERE Keeps Only TRUE

WHERE keeps TRUE rows and drops FALSE or UNKNOWN rows.

highlighted = computed this step

WHERE keeps only TRUE

A WHERE clause keeps rows whose predicate is TRUE. Rows whose predicate is FALSE or UNKNOWN are both dropped. Note: UNKNOWN is visible in the truth annotation.

where true only\text{where true only}

Filtering grades

The grade threshold is 85 and the recomputed kept row count is 2. Note: the missing-grade row is dropped because its predicate is UNKNOWN, not TRUE.

threshold=85,rows=2\text{threshold}=85,\quad \text{rows}=2

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

WHERE keeps only TRUEwhere grade gt 85arity 3 rows 2Gradesarity 3 rows 5
wheresidcoursegrades1DB90s1DB90
WHERE truth annotationsTrutharity 2 rows 5
Truthcasetruth1TRUE2TRUE3UNKNOWN4FALSE5FALSE

Only TRUE survives

The compiled predicate annotations contain TRUE 2, FALSE 2, and UNKNOWN 1. The kept row count matches the TRUE count.

TRUE=2,FALSE=2,UNKNOWN=1\text{TRUE}=2,\quad \text{FALSE}=2,\quad \text{UNKNOWN}=1

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

WHERE truth countsgroup_byarity 2 rows 3Trutharity 2 rows 5
group_bytruthnFALSE2TRUE2UNKNOWN1

NULL does not vanish by itself

Filtering on course instead of grade recomputes to row count 3. Note: the row with NULL grade survives when the predicate is TRUE for its course.

course filter rows=3\text{course filter rows}=3

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

WHERE on a non-NULL columnwhere course eq DBarity 3 rows 3Gradesarity 3 rows 5
wheresidcoursegrades1DB90s1DB90s2DBNULL

Summary

WHERE keeps TRUE and drops both FALSE and UNKNOWN. Note: bag multiplicity and NULL behavior are here; set algebra was Book One.

where summary\text{where summary}