CASE Expressions

SQL does not have an if statement the way a general purpose programming language does, but it has something that covers almost every situation an if/else would: the CASE expression. CASE evaluates one or more conditions and returns a single value depending on which condition matched — and because it is an expression rather than a statement, you can drop it anywhere a value is expected: in SELECT, in WHERE, in ORDER BY, even inside another function call.

Two forms of CASE

There are two flavors of CASE: the simple form, which compares one expression against a list of possible values, and the searched form, which evaluates an independent boolean condition for each branch. The searched form is more flexible and is what you will reach for most often.

Form	Syntax	When to use it
Simple CASE	CASE column WHEN val1 THEN result1 WHEN val2 THEN result2 ELSE default END	Comparing one column/expression against a fixed set of exact values (like a switch statement)
Searched CASE	CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default END	Arbitrary boolean conditions — ranges, multiple columns, comparisons, `AND`/`OR` logic

Simple CASE

The simple form is compact when every branch is just testing the same expression for equality against a different literal value:

Simple CASE

SQL

SELECT
  order_id,
  status,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Searched CASE

The searched form lets each branch have its own independent condition, which is what you need whenever you are testing ranges or combining multiple columns. A very common use is bucketing a numeric value into a labeled category — for example, turning a person's age into an age-group label:

Searched CASE — bucketing values

SQL

SELECT
  customer_id,
  age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 29 THEN '18-29'
    WHEN age BETWEEN 30 AND 49 THEN '30-49'
    WHEN age >= 50 THEN '50+'
    ELSE 'Unknown'
  END AS age_group
FROM customers;

Conditions are evaluated top to bottom, and the result of the first matching WHEN is returned — later conditions are never checked once one has matched. If no condition matches and there is no ELSE, the expression returns NULL rather than raising an error.

Conditional aggregation

Because CASE is just an expression, you can nest it inside an aggregate function like SUM() or COUNT(). This is the classic trick for turning several rows into several labeled counts within a single query, instead of running one query per label:

Conditional aggregation — counting order sizes

SQL

SELECT
  COUNT(CASE WHEN total_amount >= 100 THEN 1 END) AS large_orders,
  COUNT(CASE WHEN total_amount < 100 THEN 1 END)  AS small_orders,
  SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders;

Here, COUNT() only counts non-NULL values, so a CASE that returns 1 for a matching row and implicitly NULL otherwise (no ELSE) is an easy way to count how many rows satisfy a condition, all in the same pass over the table.

CASE anywhere an expression is valid

SELECT — compute a derived/labeled column, as shown above
WHERE — filter using a condition that itself depends on a branch of logic
ORDER BY — apply custom sort orders, e.g. sorting a status column into a specific business-meaningful sequence rather than alphabetically
GROUP BY — group rows by a computed bucket (many databases let you group by the same CASE expression used in SELECT)

CASE in ORDER BY — custom sort order

SQL

SELECT order_id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'Pending'   THEN 1
    WHEN 'Shipped'   THEN 2
    WHEN 'Delivered' THEN 3
    WHEN 'Cancelled' THEN 4
  END;

No fall-through

Unlike a C-style switch, there is no fall-through between WHEN branches — each branch is fully independent and only one result is ever returned per row.

Tip

Always include an ELSE unless a NULL result for unmatched rows is genuinely what you want. It makes the intent of the expression explicit and avoids silently-NULL output that is easy to miss while debugging.