Why can a query still fail with division by zero when WHERE excludes the zero rows?
answer
- the filter promises rows, not evaluations
- no short-circuit guarantee for AND
- the guard and the risky expression drift apart
- make the divisor unknown instead of avoiding it
- think NULLIF, not a stricter WHERE
basics
~20 sThe logical clause order defines which rows come back, not which expressions get evaluated. An engine may compute a select-list expression for rows a filter would have removed, so guard the arithmetic itself with NULLIF or CASE.
solid answer
~50 sThe logical pipeline says `WHERE` is evaluated before the select list, and people read that as a promise that `total / qty` is only computed for rows where `qty <> 0`. It is not. The as-if rule guarantees the *rows returned*, not the set of expressions evaluated or the order operands are evaluated in — and SQL specifies no short-circuit order for `AND`/`OR` either. The failure shows up most often when the filter and the expression are in different query levels: a view, a `WITH` clause, or a subquery whose predicate the engine may move relative to the projection. The fix is to make the expression safe rather than to rely on the filter: `total / NULLIF(qty, 0)` turns the divisor into NULL, so the division yields NULL instead of raising, and `CASE WHEN qty = 0 THEN NULL ELSE total / qty END` reads more explicitly. Decide separately, in `WHERE` or `COALESCE`, what those rows should show.
code
sql · 8 lines-- Fragile: relies on the filter to keep the division from being evaluated
CREATE VIEW line_prices AS
SELECT id, total / qty AS unit_price FROM order_lines;
SELECT * FROM line_prices WHERE unit_price > 10;
-- Safe: the divisor can never be zero, so the expression cannot raise
CREATE VIEW line_prices AS
SELECT id, total / NULLIF(qty, 0) AS unit_price FROM order_lines;go deeper
Know the safe idiom: divide by NULLIF(divisor, 0) so a zero divisor yields NULL instead of an error, and decide separately whether to show NULL or a fallback value.
Explain why the filter is not a guarantee — the clause order defines the returned rows, not which expressions are evaluated — and know that SQL promises no short-circuit order for AND.
Generalise it: any expression that can raise on some input must be made total at the point of use, especially once views, CTEs and subqueries put the guard and the expression at different query levels.
Make it policy rather than folklore: risky expressions are guarded at the point of computation, reporting layers state what an undefined value renders as, and no query is signed off on the argument that an outer filter keeps the bad rows away.
## The symptom ```sql SELECT total / qty AS unit_price FROM order_lines WHERE qty <> 0; ``` This looks airtight — and usually works. Then the same logic is wrapped in a view or a `WITH` clause, the filter ends up one level away from the division, and production starts throwing a division-by-zero error on a query that "clearly" excludes zero. ## What the logical order actually promises The clause pipeline (`FROM`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `DISTINCT`, `ORDER BY`, row limit) is a definition of the *result*. Engines implement it under an as-if rule: any strategy is legal provided the rows returned are the rows the pipeline defines. Errors are not rows. Nothing in that contract says an expression in the select list is evaluated only for rows that survived the filter, and nothing says an expression is evaluated at most once per surviving row, or at all if its value is unused. The same gap has a sibling in boolean logic. SQL defines the truth tables for `AND` and `OR`, but it does not require left-to-right short-circuit evaluation the way most programming languages do. So this is not a guard either: ```sql WHERE qty <> 0 AND total / qty > 10 -- may still raise ``` Both operands may be evaluated, in either order. ## Where it bites - **Views and `WITH` clauses.** You filter in the outer query; the division lives in the inner select list. Whether the two ever meet at the same level is not something the language defines for you. - **Derived tables and subqueries.** A predicate written outside may be applied before or after the projection inside. - **`ORDER BY` and window expressions computed over pre-limit rows**, when the row limit was supposed to keep the bad rows out. - **Unused expressions.** An expression a wrapper query never selects may or may not be evaluated; do not draw conclusions from a version that happened to work. The common thread: the moment the guard and the risky expression are not the same expression, you are relying on evaluation order that no one promised you. ## The fixes that actually hold **`NULLIF` on the divisor** is the tightest, because it removes the error condition from the arithmetic rather than trying to avoid reaching it. `NULLIF(qty, 0)` returns NULL when `qty` is zero, and division by NULL is NULL, not an error: ```sql SELECT total / NULLIF(qty, 0) AS unit_price FROM order_lines; ``` **`CASE`** says the same thing more explicitly, and reads better when the fallback is a value rather than NULL: ```sql SELECT CASE WHEN qty = 0 THEN NULL ELSE total / qty END AS unit_price FROM order_lines; ``` `CASE` chooses the first branch whose condition holds, which is the standard-blessed way to express a conditional value. Even so, prefer `NULLIF` when the only thing you need is "do not divide by that": it makes the guard part of the operand, so there is no branch that could be evaluated speculatively. **Then decide the display separately.** `COALESCE(total / NULLIF(qty, 0), 0)` renders zero-quantity lines as 0; leaving the NULL renders them as unknown, which is often more honest. Keep that decision distinct from the safety fix — the NULL is what makes the query survive, and the `COALESCE` is a presentation choice. ## Fixes that do not hold - Adding the same `WHERE` predicate again in the outer query. It changes which rows are returned, not which expressions get evaluated. - Reordering the `AND` operands so the guard comes first. No short-circuit guarantee exists. - Adding `LIMIT` so "the bad rows never get reached". The limit is applied to the finished result. - Relying on the query having worked yesterday. Nothing in the language pinned that behaviour, so a data change or a different plan can unpin it. ## The wider lesson This is the sharpest practical demonstration that the logical clause order is a semantic model rather than a runtime schedule. Treat any expression that can raise on some inputs — division, `CAST` of free-text to a number, array or substring indexing, a strict date parse — as unsafe on its own terms, and make it total for every input the row set can contain, instead of arranging for it never to see the bad ones.
- Does writing WHERE qty <> 0 AND total / qty > 10 protect the division?No. SQL does not define a left-to-right short-circuit order for `AND` operands, so both may be evaluated in either order. Guard the arithmetic itself — `total / NULLIF(qty, 0) > 10` — rather than depending on one conjunct running before another.
- Why prefer NULLIF over CASE for this?`NULLIF(qty, 0)` folds the guard into the operand, so there is no separate branch that could be evaluated speculatively and no way for the guard to drift away from the division. `CASE` is fine and more readable when you want a non-NULL fallback, but the safety comes from the divisor never being zero.
- Once you use NULLIF, how do you decide what those rows should show?That is a separate, deliberate choice. Leaving NULL says 'unit price is unknown for a zero-quantity line', which is usually accurate. Wrapping the whole expression in `COALESCE(..., 0)` shows zero, which is fine for a display but can silently pollute downstream averages, so make it explicit rather than reflexive.
saying these in an interview costs you the question
- Insists WHERE guarantees the expression is never evaluated
- Says AND short-circuits left to right in SQL
- Adds the same filter in an outer query as the fix
- Blames the engine for a bug rather than the assumption
- Wraps everything in COALESCE without deciding what the value means