How does SQL evaluate CASE WHEN branches when two WHEN conditions both match a row?

## First match wins The semantics of `CASE` are ordered. Conceptually the engine walks the `WHEN` clauses in the order you wrote them and asks, for each, whether the condition is TRUE for this row. The first TRUE wins: its `THEN` result becomes the value of the expression and no later branch is considered. If no condition is TRUE, the `ELSE` result is used, and an omitted `ELSE` means NULL. Critically, SQL's three-valued logic means "not TRUE" covers two cases: FALSE and UNKNOWN. A condition that evaluates to UNKNOWN — typically because a column involved is NULL — does **not** select its branch. `CASE WHEN discount > 0 THEN 'discounted' ELSE 'full price' END` labels a row whose `discount` is NULL as `'full price'`, because `NULL > 0` is UNKNOWN rather than TRUE. That is a common silent misclassification, and the cure is an explicit NULL branch placed before the branch that would otherwise absorb it. ## The unreachable-branch bug ```sql SELECT score, CASE WHEN score >= 50 THEN 'pass' WHEN score >= 90 THEN 'top' END AS band FROM exam_results; ``` Every score of 90 or more is also 50 or more, so the first branch matches first and `'top'` is unreachable — the expression is *syntactically* fine, the query runs, and the data is quietly wrong. Nothing in SQL warns you: unlike a `switch` in some languages there is no exhaustiveness or overlap check, and unlike a set of `WHERE` predicates there is no result to eyeball that shows the conflict. Two repairs: ```sql -- 1. most specific first (idiomatic) CASE WHEN score >= 90 THEN 'top' WHEN score >= 50 THEN 'pass' ELSE 'fail' END -- 2. mutually exclusive conditions (order-independent) CASE WHEN score >= 90 THEN 'top' WHEN score >= 50 AND score < 90 THEN 'pass' WHEN score < 50 THEN 'fail' END ``` The first is shorter and the standard idiom: a descending ladder where each branch implicitly means "and none of the above". The second states every boundary explicitly, so reordering the branches cannot change the answer — useful when the ladder is long or generated, at the cost of repeating each cut point and giving you two places to update when a threshold moves. ## Ordering as documentation Because a later branch means "and everything above failed", a CASE ladder reads as a decision list and should be written to be read that way. Three habits help: - **Sort the branches monotonically** — descending thresholds, or grouping by the column each branch tests — so a reader can scan the cut points. - **Put NULL handling where it belongs.** If NULL is its own category, `WHEN x IS NULL THEN 'unknown'` goes first; if NULL should share the default, let it fall through to `ELSE` and say so in a comment. - **Always finish with `ELSE`** when every row must have a label, so the ladder is total and adding a row with unexpected data cannot produce a silent NULL. ## What ordering does *not* promise Order is about **which branch supplies the result**, not a guarantee about what the engine computes along the way. The result is defined as if conditions were tested in order and evaluation stopped at the first TRUE, but engines are free to fold constants and to hoist parts of expressions, and some documentation is explicit that a CASE cannot always shield a contained expression from being evaluated. Practically: relying on branch order to compute a value is sound (`CASE WHEN d = 0 THEN NULL ELSE n / d END` is the standard way to avoid dividing by zero and works on the majors), but do not treat a CASE as a guaranteed guard around an expression that raises errors, and verify on your engine if a branch contains something expensive or error-prone. If a branch must never be evaluated for certain rows, filter those rows out instead. ## Related shape: overlapping equality branches The same rule applies to the simple form. `CASE code WHEN 'A' THEN 1 WHEN 'A' THEN 2 END` is legal and returns 1 — duplicates are not rejected, just shadowed. That happens in practice when a long generated mapping list accumulates entries; only the first occurrence of a value ever fires, so a "corrected" mapping appended at the bottom of the ladder does nothing at all. ## How to catch these Since the engine will not tell you, verify categorisation logic with data: group by the derived label and compare the per-band counts against what you expect, or run a query that returns the rows in the band you believe should be non-empty. A band that comes back with zero rows when it should have some is the signature of a shadowed branch.