Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#Control Flow#Syntax
3D illustration of branching conditional switches directing value transformations into custom calculated 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

sql
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;
sql
-- 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:

sql
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

FeatureSimple CASE expr WHEN vSearched CASE WHEN cond
Supports NULL checkNo — NULL = NULL is UNKNOWNYes — WHEN col IS NULL
Supports rangesNoYes — WHEN score >= 90
Supports multiple predicatesNoYes — WHEN a AND b
Can use aggregatesOnly inside the compared expressionYes, in the condition
Evaluates exprOncePer WHEN condition
Result type mixOutcome
integer + numericnumeric
integer + textError: CASE types integer and text cannot be matched (PG 42804)
varchar(10) + varchar(50)Widest wins; may truncate on assignment
date + timestamptimestamp
Any branch NULLNULL is untyped; the other branches decide

Practical edge cases and deep dives

1. The omitted ELSE in UPDATE writes NULL

sql
-- 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

sql
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

sql
-- 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

sql
-- 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:

sql
-- 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

sql
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

sql
-- 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 symptomCauseFix
CASE types integer and text cannot be matched (PG 42804)Branches return different typesCast every branch to one type
CASE never matches when comparing to NULLWHEN NULL is UNKNOWNWHEN col IS NULL in the searched form
Column becomes empty after an UPDATEMissing ELSEAdd ELSE <column>
division by zero despite a CASE guardAggregate argument evaluated before CASEFILTER (WHERE ...) or NULLIF
Every COUNT(CASE ...) bucket equals the totalELSE 0 inside COUNTOmit the ELSE, so non-matching rows are NULL
Buckets appear in alphabetical orderCASE alias used in ORDER BY without the expressionRepeat the CASE in ORDER BY, or sort on a numeric bucket
ELSE branch is silently truncatedBranch types have different lengths (varchar(10) vs varchar(50))Cast the narrow branch to the wide type
syntax error at or near "CASE" in WHERECASE used as a statement rather than an expressionWrap it in a comparison: WHERE CASE ... END = 'x'
Query slower after adding CASE to WHEREPredicate is no longer sargableMove the CASE to the SELECT list, or split into UNION ALL branches
Nested CASE becomes unreadableMore than three levelsUse 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.