🔗AND, OR, NOT
Boolean algebra meets three-valued logic
AND needs every operand TRUE; OR needs one TRUE; NOT flips TRUE/FALSE but leaves UNKNOWN as UNKNOWN. When any operand is NULL, many compound expressions become UNKNOWN, so WHERE quietly drops rows you might have expected to see.
Mixing AND / OR without parentheses
Readers assume AND binds tighter, and it does, but business rules rarely match default grouping once you add a third clause.
Parentheses mirror the spec you actually signed
Write (country = 'IN' AND (arm = 'A' OR arm = 'B')) explicitly even if precedence already does the right thing today.
Compound predicate (clinical trial sites)
SELECT study_id, arm_code, enrolled
FROM study_sites
WHERE country = 'IN'
AND (arm_code = 'A' OR arm_code = 'B')
AND NOT paused;| study_id | arm_code | enrolled |
|---|---|---|
| ST-77 | A | 120 |
| ST-77 | B | 118 |
| ST-81 | A | 64 |
🧪Truth table corner (A is NULL, B is TRUE)
UNKNOWN is neither true nor false, it fails WHERE.
| Expression | Result |
|---|---|
| A AND B | UNKNOWN |
| A OR B | TRUE |
| NOT A | UNKNOWN |
Compliance queries
For eligibility rules encoded in SQL, add fixtures where flags are NULL, regulators care about “unknown means excluded,” not just happy-path TRUE rows.
TL;DR
AND/OR/NOT combine predicates using three-valued logic (TRUE, FALSE, UNKNOWN). AND is only true if both sides are true; OR is true if either side is true; NOT UNKNOWN stays UNKNOWN. Mixing OR with nullable columns often surprises people, test with NULL fixtures and consider COALESCE or explicit IS [NOT] DISTINCT FROM where supported.