Conditional Expressions (CASE)

CASE brings if/else-style branching logic directly into a SQL expression — usable anywhere a value is expected: the SELECT list, WHERE, ORDER BY, inside an aggregate, and more.

Simple CASE — comparing one value

Simple CASE syntax

SQL

SELECT
  name,
  CASE status
    WHEN 'pending'   THEN 'Awaiting confirmation'
    WHEN 'shipped'    THEN 'On the way'
    WHEN 'delivered'  THEN 'Complete'
    ELSE 'Unknown status'
  END AS status_label
FROM orders;

Simple CASE compares one expression (status here) against a list of exact values.

Searched CASE — arbitrary conditions

Searched CASE is more flexible: each branch is a full boolean condition, so you can compare ranges, combine multiple columns, or use any expression at all.

Bucketing order totals into labeled tiers

SQL

SELECT
  order_id,
  total,
  CASE
    WHEN total >= 500 THEN 'large'
    WHEN total >= 100 THEN 'medium'
    ELSE 'small'
  END AS order_tier
FROM orders;

order_id | total  | order_tier
---------+--------+-----------
    1001 | 620.00 | large
    1002 |  85.00 | small
    1003 | 210.00 | medium

WHEN conditions are evaluated top to bottom and the first one that's true wins — order matters, which is why the largest threshold is checked first above. If no branch matches and there's no ELSE, the result is NULL.

Conditional aggregation

A genuinely common real-world reporting pattern: counting how many rows meet several different conditions, all within a single GROUP BY query, by wrapping a CASE inside an aggregate function.

Order status breakdown per customer, in one pass

SQL

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END)  AS delivered_count,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END)  AS cancelled_count,
  SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) AS delivered_revenue
FROM orders
GROUP BY customer_id;

customer_id | total_orders | delivered_count | cancelled_count | delivered_revenue
------------+--------------+-----------------+------------------+-------------------
         42 |           12 |               9 |                1 |            980.00
        108 |            5 |               4 |                0 |            410.00

COUNT(CASE WHEN condition THEN 1 END) works because COUNT() ignores NULL — rows where the condition is false produce NULL (no ELSE needed) and are simply not counted, while matching rows contribute a 1. This lets a single query compute several different conditional totals side by side, instead of running one query per condition.

COALESCE() and NULLIF() — related conditional functions

COALESCE() and NULLIF() are shorthand for common patterns that could otherwise be written as CASE expressions, and are worth knowing alongside it.

Function	Equivalent CASE	Purpose
`COALESCE(a, b, c)`	`CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END`	Returns the first non-NULL argument
`NULLIF(a, b)`	`CASE WHEN a = b THEN NULL ELSE a END`	Returns NULL if `a` equals `b`, otherwise returns `a`

SQL

-- Fall back to a default when a column is NULL
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

-- Avoid a division-by-zero error by turning 0 into NULL first
SELECT total / NULLIF(quantity, 0) AS unit_price
FROM order_items;

Note

a / NULLIF(quantity, 0) is a very common defensive pattern: dividing by zero raises an error, but dividing by NULL simply produces NULL — which usually renders as a harmless blank in a report instead of failing the whole query.

Tip

Reach for CASE when you need multi-branch logic or conditional aggregation; reach for COALESCE() / NULLIF() when the situation is specifically about NULL-handling — they read more clearly than an equivalent CASE for that narrower job.

Simple CASE value WHEN ... THEN ... compares one expression against exact values.
Searched CASE WHEN condition THEN ... supports arbitrary boolean conditions, checked top to bottom.
Wrapping CASE inside an aggregate (e.g. COUNT(CASE WHEN ... THEN 1 END)) computes several conditional totals in one grouped query.
COALESCE() returns the first non-NULL value; NULLIF() turns a matching value into NULL.