How does SQL's simple CASE form differ from the searched CASE form?
answer
- Two syntactic forms, same expression
- One of them compares against values
- The other takes a full predicate
- Equality versus arbitrary boolean condition
- Ranges and IS NULL need the second form
basics
~20 sSimple CASE compares one expression for equality against a list of values (CASE status WHEN 'N' THEN ...). Searched CASE takes an independent boolean predicate per WHEN, so it can test ranges, several columns, or IS NULL.
solid answer
~50 sBoth forms are **expressions** that return a single value, and both pick the first branch that matches. The **simple** form, `CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE d END`, evaluates `expr` once and compares it for **equality** against each value — it is compact for mapping a small set of codes to labels. The **searched** form, `CASE WHEN predicate THEN r ... ELSE d END`, has no controlling expression: each WHEN carries a full boolean predicate, so it can express ranges (`total >= 1000`), combine columns (`qty > 0 AND status <> 'X'`), or test `IS NULL`. The standard actually *defines* simple CASE as shorthand for a searched CASE whose predicates are `expr = v`, which is why `WHEN NULL` never matches. When in doubt, use searched CASE — it can express everything simple CASE can.
code
sql · 8 lines-- simple CASE: one expression, compared for equality
SELECT order_id,
CASE status
WHEN 'N' THEN 'new'
WHEN 'S' THEN 'shipped'
ELSE 'other'
END AS status_label
FROM orders;go deeper
Be ready to write both forms from memory on a whiteboard, including the closing END and an ELSE, and to say which form handles a range test.
Explain that the standard defines simple CASE as shorthand for searched CASE with equality predicates, and derive the WHEN NULL trap from that definition.
Show judgment about readability in real queries: when a simple CASE keeps a long expression out of every branch, and when a flat searched CASE beats nested CASEs.
Discuss where category logic should live at all — a CASE ladder duplicated across many queries is a lookup table or a view waiting to be created, and you should be able to argue the tradeoff.
## CASE is an expression, not a statement In standard SQL, `CASE` is a **value expression**: it evaluates to one value of one data type, and it can be written anywhere a value can appear — in the SELECT list, in `WHERE`, in `ORDER BY`, in `GROUP BY`, in a `JOIN ... ON` predicate, in an argument to a function. It is the language's conditional operator, closer to a ternary operator or an `if/else if` ladder in a programming language than to a control-flow statement. Every `CASE` ends with the keyword `END`. The standard gives it two syntactic forms. ## The searched form ```sql CASE WHEN total >= 1000 THEN 'large' WHEN total >= 100 THEN 'medium' WHEN total IS NULL THEN 'unknown' ELSE 'small' END ``` There is no expression between `CASE` and the first `WHEN`. Each `WHEN` carries a complete boolean predicate — anything you could put in a `WHERE` clause: comparisons, `BETWEEN`, `IN`, `LIKE`, `IS NULL`, predicates combined with `AND`/`OR`/`NOT`, even a subquery predicate such as `EXISTS (...)`. The branches are considered top to bottom and the **first** predicate that evaluates to TRUE supplies the result. ## The simple form ```sql CASE status WHEN 'N' THEN 'new' WHEN 'S' THEN 'shipped' ELSE 'other' END ``` Here `status` is the *controlling expression*. It is evaluated once, then compared for equality against `'N'`, then `'S'`, and so on. Each `WHEN` holds a **value**, not a predicate — you cannot write `WHEN > 100` or `WHEN IS NULL`, because those are not values. The form is purely a convenience for the common case of mapping a small, closed set of codes onto labels; it keeps the column name out of every branch. ## The standard defines one in terms of the other The SQL standard specifies simple CASE as **shorthand**: `CASE x WHEN v THEN r ... END` is defined to mean `CASE WHEN x = v THEN r ... END`. Everything that follows comes from that definition: - Matching uses the `=` comparison, with the ordinary type-comparison and collation rules of the engine. - `WHEN NULL` can never match, because `x = NULL` evaluates to UNKNOWN rather than TRUE, and only TRUE selects a branch. A NULL controlling expression falls through to `ELSE`. - The controlling expression is conceptually evaluated once, so a simple CASE is often the more readable choice when that expression is long. ## Which form to reach for Use **simple CASE** when you are testing one expression against literal values and nothing more: status codes, country codes, a small enum. Use **searched CASE** for everything else — ranges and thresholds, tests spanning more than one column, anything involving NULL, and any condition more complex than equality. Searched CASE is strictly more expressive: any simple CASE can be rewritten as a searched CASE by spelling out the `= value` predicates, but the reverse is not true. Mixing the forms is not allowed inside one `CASE`. You either supply a controlling expression and then write values after every `WHEN`, or you supply none and write predicates after every `WHEN`. ## Rules that apply to both forms - **First match wins.** Branches are considered in written order; later branches that would also match are never reached. - **ELSE is optional and defaults to NULL.** A CASE where nothing matches and no `ELSE` is present returns NULL. - **One result type.** All the `THEN` results and the `ELSE` result must be type-compatible; the engine derives a single result type for the whole expression, so a column produced by a CASE has one declared type, not a different type per row. - **Nesting is legal.** A `THEN` result may itself be a CASE, though a flat searched CASE with compound predicates is usually easier to read. ## Common mistakes Writing `CASE status WHEN 'A' OR status = 'B' THEN ...` mixes the two forms and is a syntax error; use `CASE WHEN status IN ('A','B') THEN ...`. Writing `CASE amount WHEN > 100 THEN ...` fails for the same reason — a comparison operator cannot follow `WHEN` in the simple form. And expecting `CASE x WHEN NULL THEN ...` to catch missing values is the classic trap: it silently never fires, because equality against NULL is never TRUE.
- Can you write a range test such as WHEN >= 100 in the simple CASE form?No. In the simple form every WHEN is followed by a *value*, and the engine compares the controlling expression to it with `=`. A comparison operator there is a syntax error. Rewrite it as a searched CASE: `CASE WHEN total >= 100 THEN 'big' ELSE 'small' END`. The only relational test the simple form can express is equality against a listed value.
- Where in a statement may a CASE expression appear?Anywhere a value expression is allowed: the SELECT list, WHERE, GROUP BY, HAVING, ORDER BY, a JOIN's ON predicate, the argument of a function, and the value list of an INSERT or the SET list of an UPDATE. It is not a control-flow statement, so it cannot choose between whole statements or switch clauses on and off.
- Can a single CASE mix a controlling expression with predicate-style WHEN branches?No. Once you write `CASE status`, every WHEN must be followed by a value to compare against; once you write `CASE WHEN`, every WHEN must carry a predicate. Mixing them is a syntax error. If you need both kinds of test, use the searched form throughout and spell out the equality comparisons explicitly.
Simple CASE is a lookup table keyed by one value, like a switch on a code; searched CASE is an if / else-if ladder where every rung can ask its own question.
saying these in an interview costs you the question
- Claiming CASE is a statement that executes branches like an if
- Writing WHEN > 100 after a controlling expression
- Believing simple CASE can test IS NULL
- Saying the two forms differ in speed rather than expressiveness
- Thinking CASE can return a different data type per row