SQL CASE WHEN: Conditional Logic in SELECT, WHERE, and UPDATE Statements
Searched versus simple CASE, the omitted ELSE that writes NULL in an UPDATE, branch type unification, and pivoting rows into columns.

Quick Summary
CASE is SQL's only conditional expression. It takes two forms: the searched form (CASE WHEN <condition> THEN <result>) which handles arbitrary predicates, and the simple form (CASE <expr> WHEN <value> THEN <result>) which is equality against one expression and is the source of the "why does this never match?" bug. It returns one value of one type, works anywhere an expression is allowed, and terminates at the first matching WHEN.
Three details decide whether it works. Branch result types must be compatible or the statement fails. An omitted ELSE returns NULL, which quietly overwrites data in an UPDATE. And short-circuiting is not guaranteed in every dialect's execution order, so a "guard" against division by zero can still raise.
Basic syntax and query signatures
SELECT
id,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM exams;-- Simple form: equality only, and NULL never equals anything
SELECT
CASE status
WHEN 'paid' THEN 'Settled'
WHEN 'refund' THEN 'Returned'
ELSE 'Open'
END AS bucket
FROM orders;CASE is an expression, so it belongs in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, and on the right side of SET in UPDATE. It can be nested, and it can wrap aggregates or sit inside one:
SELECT
COUNT(*) AS total,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid,
SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_revenue
FROM orders;That COUNT(CASE ...) idiom is the portable pivot and it depends on the omitted ELSE: COUNT ignores NULL, so only matching rows are counted. Writing ELSE 0 there returns the row count for every bucket — a classic silent wrong answer.
Key comparison table
| Feature | Simple CASE expr WHEN v | Searched CASE WHEN cond |
|---|---|---|
Supports NULL check | No — NULL = NULL is UNKNOWN | Yes — WHEN col IS NULL |
| Supports ranges | No | Yes — WHEN score >= 90 |
| Supports multiple predicates | No | Yes — WHEN a AND b |
| Can use aggregates | Only inside the compared expression | Yes, in the condition |
Evaluates expr | Once | Per WHEN condition |
| Result type mix | Outcome |
|---|---|
integer + numeric | numeric |
integer + text | Error: CASE types integer and text cannot be matched (PG 42804) |
varchar(10) + varchar(50) | Widest wins; may truncate on assignment |
date + timestamp | timestamp |
Any branch NULL | NULL is untyped; the other branches decide |
Practical edge cases and deep dives
1. The omitted ELSE in UPDATE writes NULL
-- Every row that does not match gets NULL, not its old value
UPDATE products
SET price = CASE WHEN discontinued THEN price * 0.5 END
WHERE category_id = 7;
-- Explicit and safe
UPDATE products
SET price = CASE WHEN discontinued THEN price * 0.5 ELSE price END
WHERE category_id = 7;This is the most expensive CASE mistake because it destroys data rather than returning a wrong report. A NOT NULL column turns it into an error, which is a favour; a nullable price column silently empties. The habit: every CASE in an UPDATE gets an explicit ELSE, even when the fallback is the column itself.
2. CASE in ORDER BY for custom ordering
SELECT id, priority, created_at
FROM tickets
ORDER BY
CASE priority
WHEN 'urgent' THEN 1
WHEN 'high' THEN 2
WHEN 'normal' THEN 3
ELSE 4
END,
created_at ASC;This puts a business order on a text column without a lookup table. The optimiser cannot use an index for the computed sort, so on a large table the sort is in memory or on disk. If the ordering matters at scale, model priority as an integer column (or an ENUM with the right order) and index (priority, created_at).
The same construct appears in GROUP BY to bucket ranges — for example grouping latency into '<100ms', '<1s', '>=1s' — and the buckets are correct but come out in alphabetical order unless you repeat the CASE in ORDER BY or join a bucket table.
3. Branch types must unify, and NULL is not a type
-- ERROR: CASE types integer and text cannot be matched
SELECT CASE WHEN flag THEN 1 ELSE 'none' END FROM t;
-- Cast the branches explicitly
SELECT CASE WHEN flag THEN 1::text ELSE 'none' END FROM t;The untyped NULL in a branch takes the type of the other branches, which is convenient (CASE WHEN ... THEN total END is typed as total), and dangerous when every branch is NULL: the expression resolves to text in PostgreSQL and then fails on insert into a numeric column.
A related trap is comparison across numeric families in the simple form: CASE numeric_col WHEN 1 THEN ... compares numeric to integer fine, but CASE text_col WHEN 1 errors. Cast the WHEN value, not the column.
4. CASE is not always short-circuited
-- Often works, but not guaranteed across engines and plans
SELECT CASE WHEN divisor <> 0 THEN numerator / divisor ELSE NULL END FROM t;PostgreSQL and SQL Server generally evaluate only the selected branch for scalar expressions, but the planner may pre-evaluate expressions that do not depend on the branch, and aggregate arguments are evaluated before the CASE sees them:
-- Still divides by zero for the rows you meant to skip
SELECT CASE WHEN divisor <> 0 THEN SUM(numerator / divisor) END FROM t;
-- Correct: filter first, then aggregate
SELECT SUM(numerator / divisor) FILTER (WHERE divisor <> 0) FROM t;FILTER (WHERE ...) (PostgreSQL, SQLite, and standard SQL) is the honest way to skip rows from an aggregate. NULLIF(divisor, 0) is the compact alternative that keeps division inside the aggregate: SUM(numerator / NULLIF(divisor, 0)) produces NULL for the zero rows, and SUM ignores NULL.
5. Pivoting rows into columns
SELECT
DATE_TRUNC('month', placed_at)::date AS month,
SUM(CASE WHEN channel = 'web' THEN total ELSE 0 END) AS web_revenue,
SUM(CASE WHEN channel = 'ios' THEN total ELSE 0 END) AS ios_revenue,
SUM(CASE WHEN channel = 'and' THEN total ELSE 0 END) AS android_revenue
FROM orders
GROUP BY 1
ORDER BY 1;This is the portable pivot: one pass, one GROUP BY, one SUM per output column. It costs O(columns × rows) additions, which is fine for a handful of channels and wrong for a dynamic list — a new channel means a schema change in the query. For the dynamic case, PostgreSQL's crosstab (from tablefunc) or jsonb_object_agg returns variable columns without editing the SQL.
Note ELSE 0 rather than an omitted ELSE here: SUM of NULL is NULL, and a month with no iOS sales would render as blank instead of 0. COUNT wants the omitted ELSE; SUM wants ELSE 0. Mixing them up is the second most common CASE bug after the UPDATE.
6. CASE cannot replace a constraint
-- Intent: a status may only be one of three values
ALTER TABLE tasks ADD CONSTRAINT tasks_status_chk
CHECK (status IN ('open', 'done', 'cancelled'));Using CASE in a CHECK constraint to enumerate values duplicates the domain in a place no one reads, and every new value becomes a migration. A lookup table or a CHECK ... IN (...) constraint states the rule once and fails the write that violates it, instead of letting the bad row in and mislabelling it later in a report. CASE belongs where values are transformed for output; validation belongs in constraints.
Common mistakes and runtime errors
| Error or symptom | Cause | Fix |
|---|---|---|
CASE types integer and text cannot be matched (PG 42804) | Branches return different types | Cast every branch to one type |
CASE never matches when comparing to NULL | WHEN NULL is UNKNOWN | WHEN col IS NULL in the searched form |
Column becomes empty after an UPDATE | Missing ELSE | Add ELSE <column> |
division by zero despite a CASE guard | Aggregate argument evaluated before CASE | FILTER (WHERE ...) or NULLIF |
Every COUNT(CASE ...) bucket equals the total | ELSE 0 inside COUNT | Omit the ELSE, so non-matching rows are NULL |
| Buckets appear in alphabetical order | CASE alias used in ORDER BY without the expression | Repeat the CASE in ORDER BY, or sort on a numeric bucket |
ELSE branch is silently truncated | Branch types have different lengths (varchar(10) vs varchar(50)) | Cast the narrow branch to the wide type |
syntax error at or near "CASE" in WHERE | CASE used as a statement rather than an expression | Wrap it in a comparison: WHERE CASE ... END = 'x' |
Query slower after adding CASE to WHERE | Predicate is no longer sargable | Move the CASE to the SELECT list, or split into UNION ALL branches |
Nested CASE becomes unreadable | More than three levels | Use a lookup table and a join |
The last row is the useful one. CASE past two or three branches is a decision table living in a query, invisible to everyone except the person who wrote it, and untestable except through the database. A small mapping table with a foreign key costs one join and gives the business a place to edit the rules without a migration.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.