CASE Expressions

CASE WHEN is SQL's conditional expression. It works like an if-then-else, letting you compute different values based on the data in each row. It can appear anywhere in a query where an expression is valid: in SELECT, WHERE, ORDER BY, inside aggregate functions, and in join conditions.

You will see it constantly in real SQL - reports, data pipelines, conditional aggregations. The syntax feels a little verbose the first time, but the pattern becomes second nature fast.

The searched form

The most flexible form evaluates a full boolean condition in each WHEN branch:

SELECT
  name,
  salary,
  CASE
    WHEN salary >= 120000 THEN 'Senior'
    WHEN salary >= 80000  THEN 'Mid-level'
    WHEN salary >= 50000  THEN 'Junior'
    ELSE 'Entry-level'
  END AS salary_band
FROM employees;

The database evaluates branches top to bottom and stops at the first one that is true. The ELSE catches anything that did not match. Omit ELSE entirely and you might get NULL back for some rows - that surprises people the first time, so keep it in mind.

The simple form

When you are comparing a single expression against multiple fixed values, the simple form is cleaner:

SELECT
  name,
  CASE status
    WHEN 'active'   THEN 'Active User'
    WHEN 'inactive' THEN 'Churned'
    WHEN 'trial'    THEN 'On Trial'
    ELSE 'Unknown'
  END AS status_label
FROM customers;

The simple form is exactly equivalent to CASE WHEN status = 'active' THEN ... WHEN status = 'inactive' THEN .... It is not more powerful, just more readable when the comparisons are all equality checks on the same column.

CASE inside aggregate functions

This is where CASE WHEN earns its keep. Embed it inside an aggregate and you get conditional counts and sums - multiple metrics from a single grouped query, no self-joins needed:

SELECT
  department,
  COUNT(*)                                                  AS total_employees,
  COUNT(CASE WHEN salary > 100000 THEN 1 END)               AS high_earners,
  COUNT(CASE WHEN hire_date >= '2023-01-01' THEN 1 END)     AS recent_hires,
  AVG(CASE WHEN title LIKE '%Senior%' THEN salary END)      AS avg_senior_salary
FROM employees
GROUP BY department;

The trick is that COUNT ignores NULLs. When the condition is false, the CASE returns NULL because there is no ELSE - and COUNT skips it. When the condition is true, it returns 1 - and COUNT includes it. You can do the same thing with SUM using 1 and 0 instead.

Pivoting rows into columns

CASE WHEN is also the portable way to pivot rows into columns. No special syntax, no database-specific features - just this:

SELECT
  product_id,
  SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS north_revenue,
  SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS south_revenue,
  SUM(CASE WHEN region = 'East'  THEN revenue ELSE 0 END) AS east_revenue,
  SUM(CASE WHEN region = 'West'  THEN revenue ELSE 0 END) AS west_revenue
FROM sales
GROUP BY product_id;

Each region becomes a column. It gets verbose when you have many distinct values, but the upside is that it runs anywhere - no PIVOT keyword, no database-specific extension required.

CASE in ORDER BY

One last place CASE shows up that catches people off guard: ORDER BY. You can sort by a computed expression, which means you can define any custom ordering you want:

ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'medium'   THEN 3
    WHEN 'low'      THEN 4
    ELSE 5
  END ASC

This ensures critical items appear first regardless of how the text sorts alphabetically. Once you reach for CASE in ORDER BY, you will wonder how you sorted things without it.