The SQL CASE statement: simple vs searched syntax, with examples

Simple CASE — one value, several matches

A simple CASE compares one expression against a list of values. It reads like a switch statement: the first WHEN that matches wins, and ELSE is the fallback. Use it when every branch tests the same column for equality.

SELECT name,
       CASE status
         WHEN 'paid'     THEN 'Complete'
         WHEN 'pending'  THEN 'Waiting for payment'
         WHEN 'refunded' THEN 'Money returned'
         ELSE 'Unknown status'
       END AS status_label
FROM orders;

Searched CASE — any condition per branch

A searched CASE evaluates a full condition on each WHEN, so different branches can test different columns, ranges and comparisons. This is the form you reach for most often — anything a simple CASE cannot express goes here.

Order matters: the first true condition wins, so put the most specific ranges first. If status >= 100 came before status >= 500, every big order would be labelled 'medium'.

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

Conditional grouping and counting

The most powerful CASE trick: put it inside an aggregate to count or sum only the rows that match. COUNT(CASE WHEN ... THEN 1 END) skips the non-matching rows because CASE returns NULL for them, and COUNT ignores NULLs.

You can also group by a CASE expression to build your own buckets — revenue by price band, users by age range — without creating extra tables. Repeat the same CASE in GROUP BY (or group by the column alias where your database allows it).

SELECT
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
  SUM(CASE WHEN status = 'paid' THEN total END) AS paid_revenue
FROM orders;

CASE in WHERE and ORDER BY

In ORDER BY, CASE lets you define custom sort orders that ASC and DESC cannot express — for example, pinning urgent tickets first, then sorting everything else by date.

In WHERE you rarely need CASE at all: plain AND/OR conditions are clearer and use indexes better. A WHERE full of CASE is usually a sign to rewrite it as ordinary boolean logic.

SELECT id, priority, created_at
FROM tickets
ORDER BY
  CASE priority
    WHEN 'urgent' THEN 0
    WHEN 'high'   THEN 1
    WHEN 'normal' THEN 2
    ELSE 3
  END,
  created_at DESC;

Avoiding division by zero

Databases throw an error (or return NULL) when you divide by zero. Guard the denominator with CASE or NULLIF before dividing — a classic use case in reporting queries.

-- PostgreSQL / MySQL / SQL Server
SELECT customer_id,
       SUM(total) / NULLIF(COUNT(*), 0) AS avg_order
FROM orders
GROUP BY customer_id;

Dialect notes: PostgreSQL, MySQL, SQL Server

CASE is standard SQL and the two forms above work identically in PostgreSQL, MySQL, SQL Server, Snowflake and BigQuery. The differences are in the extras: MySQL also offers IF(condition, a, b) for two-way choices, and SQL Server has IIF() — both are shortcuts, not replacements, and CASE stays the portable option.

Two traps to remember: every THEN must return compatible types (a number in one branch and text in another causes errors on strict databases), and omitting ELSE means unmatched rows silently become NULL.