Comparison and Boolean Predicates

## The precedence ladder A `WHERE` clause is one boolean expression built from predicates and the connectives `NOT`, `AND`, `OR`. SQL fixes how that expression is parsed: 1. Comparison predicates (`=`, `<>`, `<`, `>`, `<=`, `>=`) and the other predicates bind first — they produce the truth values. 2. `NOT` binds next, applying to the single predicate or parenthesized group immediately to its right. 3. `AND` binds next. 4. `OR` binds last (loosest). So `a OR b AND c` is `a OR (b AND c)`, and `NOT a AND b` is `(NOT a) AND b`. Parentheses override all of it, and they are the only thing that does. ## The bug this creates The overwhelmingly common mistake is an OR list of alternative values written next to an AND filter: ```sql SELECT * FROM subscriptions WHERE plan = 'free' OR plan = 'pro' AND status = 'active'; ``` The author read this left to right as "plan is free or pro, and the subscription is active". The parser read it as: ```sql WHERE plan = 'free' OR (plan = 'pro' AND status = 'active') ``` Every free-plan row is returned — cancelled, expired, anything — because the `status` test never applies to that branch. Nothing errors. The result set is a superset of the intended one, so it looks fine on a small dev dataset and quietly inflates a report, a billing run or a notification batch in production. The same shape appears with date filters (`WHERE region = 'EU' OR region = 'UK' AND created_at >= DATE '2026-01-01'`) and with tenant or ownership scoping, where the consequence is that rows belonging to other tenants leak into a scoped query. ## Reading a predicate the way the parser does A quick mental method: treat `AND` as multiplication and `OR` as addition, then insert the implied parentheses exactly where you would in arithmetic. `a + b * c` is `a + (b * c)`; `a OR b AND c` is `a OR (b AND c)`. If the reading you get differs from the sentence you had in your head, the clause needs parentheses. ## Two fixes Parenthesize the OR group: ```sql WHERE (plan = 'free' OR plan = 'pro') AND status = 'active'; ``` Or collapse the repeated equality tests on one column into an `IN` list, which removes the OR entirely and is usually easier to read: ```sql WHERE plan IN ('free', 'pro') AND status = 'active'; ``` Both mean the same thing. The second one cannot be broken by a later edit that appends another `AND`, which is why it is the better default when the OR branches are alternative values of a single column. ## NOT is part of the same trap `NOT` binds tighter than `AND` and `OR`, so `WHERE NOT status = 'active' OR priority = 1` negates only the first comparison; it is `(NOT status = 'active') OR priority = 1`. If you meant to negate the whole disjunction, you must write `NOT (status = 'active' OR priority = 1)`. ## Meaning versus evaluation Parentheses determine what the expression *means*. They say nothing about the order in which an engine actually tests the operands — a query engine is free to evaluate a cheap predicate before an expensive one, or to skip evaluation once the outcome is fixed, as long as the answer matches the defined meaning. Never write a predicate that depends on left-to-right short-circuiting for correctness (for example, a division guard placed "first" in an `AND`); use a `CASE` expression if you truly need ordered evaluation. ## Style rules that prevent it - Parenthesize an OR group the moment an `AND` appears anywhere in the same clause, even when precedence would have given you the right answer. It costs two characters and it survives the next edit. - Put each top-level `AND` condition on its own line, with the operator leading the line. A stray OR then stands out visually. - Prefer `IN (...)` over a chain of ORs on the same column. - When a query returns more rows than expected, check the connectives before you suspect the join or the data. ## What interviewers listen for They want the precedence order stated plainly (NOT, AND, OR), the rewritten parse of the buggy clause, and the observation that the failure mode is *silent wrong rows*, not an error. Candidates who say "SQL evaluates left to right" have the wrong model and will reproduce the bug.