CASE WHEN in SQL: The Complete Guide

CASE WHEN is SQL's if/then. It evaluates conditions in order, returns the value attached to the first condition that is true, and returns ELSE (or NULL) if none match. The basic form:

SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 1000 THEN 'large'
    WHEN amount >= 100  THEN 'medium'
    ELSE 'small'
  END AS size_band
FROM orders;

That is the whole answer for most uses. Three things matter beyond the syntax. First, CASE is an expression, not a statement, so it goes anywhere a value goes: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN conditions, UPDATE ... SET. Second, conditions are checked top to bottom and it stops at the first match, so order your branches from most specific to least. Third, END is mandatory and every branch must return a compatible data type. Omit ELSE and unmatched rows silently become NULL, which is the single most common source of confusing results.

The two forms: searched CASE and simple CASE

There are two syntaxes, and they are not equally useful.

Searched CASE puts a full boolean condition in every WHEN. This is the one you should reach for by default.

CASE
  WHEN status = 'refunded' THEN 0
  WHEN amount > credit_limit THEN amount - credit_limit
  ELSE amount
END

Simple CASE names the expression once and compares it for equality against each WHEN value.

CASE region
  WHEN 'EMEA' THEN 'Europe, Middle East, Africa'
  WHEN 'APAC' THEN 'Asia Pacific'
  ELSE 'Other'
END

Simple CASE is shorter for plain lookups but it can only do equality, and it has a trap: it cannot match NULL. CASE region WHEN NULL THEN 'unknown' never fires, because NULL = NULL is not true, it is unknown. Use searched form with WHEN region IS NULL instead.

Order matters more than people expect

Because evaluation stops at the first true branch, overlapping conditions are resolved by position, not by specificity. This query looks reasonable and is wrong:

CASE
  WHEN amount >= 100  THEN 'medium'
  WHEN amount >= 1000 THEN 'large'
  ELSE 'small'
END

An order of 5,000 returns 'medium', because amount >= 100 was true first and the second branch never runs. Nothing errors. You just get quietly wrong numbers in a report. When you write banded logic, write the boundaries in descending order and read them back to yourself as "the first one that catches it wins".

The same property is useful deliberately. You can write mutually exclusive tiers without repeating the lower bound:

CASE
  WHEN days_late > 90 THEN 'severe'
  WHEN days_late > 30 THEN 'moderate'   -- implicitly 31 to 90
  WHEN days_late > 0  THEN 'mild'       -- implicitly 1 to 30
  ELSE 'current'
END

Conditional aggregation: the technique worth learning

The highest-value use of CASE WHEN is not labelling rows, it is pivoting them. Put a CASE inside an aggregate and you turn rows into columns in a single pass over the table.

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded,
  SUM(CASE WHEN plan = 'team' THEN amount ELSE 0 END) AS team_revenue,
  SUM(CASE WHEN plan = 'solo' THEN amount ELSE 0 END) AS solo_revenue,
  AVG(CASE WHEN country = 'US' THEN amount END) AS avg_us_order
FROM orders
GROUP BY 1
ORDER BY 1;

Two details do real work here.

With COUNT, deliberately omit the ELSE. Unmatched rows become NULL and COUNT skips NULL, so you get a count of matching rows. If you write ELSE 0 inside a COUNT, you count every row, because zero is not null. This is a classic bug that inflates every conditional count in a dashboard.

With AVG, omitting ELSE is also usually what you want: AVG(CASE WHEN country = 'US' THEN amount END) averages US orders only. Adding ELSE 0 would drag the average down by including non-US rows as zeros. With SUM, ELSE 0 and no ELSE give the same total, though SUM over an all-null set returns NULL rather than 0, so COALESCE(SUM(...), 0) is worth adding when a group might have no matches.

You can also compute a rate in one expression:

SELECT
  region,
  AVG(CASE WHEN status = 'refunded' THEN 1.0 ELSE 0.0 END) AS refund_rate
FROM orders
GROUP BY region;

Note the 1.0 rather than 1. In PostgreSQL and SQL Server, integer division and integer averaging will truncate and hand you 0. Force one side to a decimal or cast explicitly.

CASE in ORDER BY, WHERE and UPDATE

Custom sort order. Sort by a business ordering that is not alphabetical:

ORDER BY
  CASE priority
    WHEN 'urgent' THEN 1
    WHEN 'high'   THEN 2
    WHEN 'normal' THEN 3
    ELSE 4
  END,
  created_at DESC;

Conditional NULL placement. Push nulls to the end regardless of the engine's default:

ORDER BY CASE WHEN closed_at IS NULL THEN 1 ELSE 0 END, closed_at;

In WHERE. CASE in a WHERE clause is legal but almost always the wrong tool. WHERE CASE WHEN a THEN b ELSE c END = 1 is usually clearer written as plain AND/OR logic, and the plain version is more likely to use an index. The one place it earns its keep is optional filters driven by a parameter:

WHERE CASE WHEN :filter_region IS NULL THEN TRUE ELSE region = :filter_region END

In UPDATE. Apply different values to different rows in one statement instead of running several updates:

UPDATE subscriptions
SET price = CASE
  WHEN plan = 'team' THEN 16
  WHEN plan = 'solo' THEN 5
  ELSE price
END
WHERE status = 'active';

The ELSE price matters. Without it, every row that matches the WHERE but no WHEN gets set to NULL.

Where the simple answer breaks

Type mismatches. Every THEN and the ELSE must resolve to one compatible type. CASE WHEN x THEN 'none' ELSE 0 END fails in PostgreSQL and silently coerces in some other engines. Cast explicitly when branches mix text and numbers.

NULL comparisons. WHEN amount > 100 is neither true nor false when amount is NULL, so the row falls through to ELSE. If null means something specific in your data, give it its own branch first: WHEN amount IS NULL THEN 'unknown'.

Short-circuiting is not guaranteed. The standard says branches evaluate in order, and in practice engines respect that for the condition tests. But a guard like CASE WHEN divisor <> 0 THEN total / divisor ELSE 0 END is not a bulletproof shield against division by zero in every engine and every plan, particularly when the expression sits in an aggregate or gets pushed around by the optimizer. NULLIF(divisor, 0) is the safer idiom: total / NULLIF(divisor, 0) returns NULL instead of erroring.

Repeating the same CASE. If you write the same CASE in SELECT, GROUP BY and ORDER BY, define it once in a CTE and reference the alias. PostgreSQL and MySQL let you GROUP BY a select alias; SQL Server does not, so the CTE is the portable fix.

Simpler alternatives exist. For a two-branch null check, COALESCE(a, b) beats CASE WHEN a IS NULL THEN b ELSE a END. For "return null if equal", NULLIF(a, b). For a boolean flag, many engines let you select the condition directly: SELECT amount > 100 AS is_large. Reach for CASE when there are genuinely three or more outcomes.

Dialect differences worth knowing

Nesting is legal in every engine (CASE WHEN a THEN CASE WHEN b THEN ... END END) but past two levels it becomes unreadable. Flatten it with AND in the conditions instead: WHEN a AND b THEN ....

A checklist before you ship a CASE expression

• Are the branches in the right order, most specific first?
• Is there an ELSE, and if not, is NULL genuinely what you want for the leftovers?
• Inside a COUNT, did you correctly leave ELSE off?
• Do all branches return the same type?
• Does any input column allow NULL, and does that fall through correctly?
• In an UPDATE, does ELSE preserve the existing value?

Run the query once with a raw column alongside the CASE output and spot-check ten rows across the boundaries. Banded logic is almost always wrong at the boundary the first time.