CASE WHEN

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.

Understanding CASE WHEN well is important because it shows up constantly in real SQL: in reports, in data transformations, in conditional aggregations, and anywhere you need to categorize or reclassify values on the fly.

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 conditions top to bottom and returns the result of the first branch that is true. The ELSE clause handles any row that did not match any WHEN. If you omit ELSE and no condition matches, the expression returns NULL.

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 becomes especially useful. By embedding a CASE inside an aggregate, you can count or sum only the rows that meet a certain condition, all within a single grouped query:

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 COUNT(CASE WHEN ... THEN 1 END) pattern works because COUNT ignores NULL values. When the condition is false, the CASE expression returns NULL (no ELSE clause), and COUNT skips that row. When the condition is true, it returns 1, and COUNT includes it. The result is a conditional count.

Pivoting rows into columns

CASE WHEN is the portable way to pivot: turning distinct row values into separate columns:

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 its own column. The aggregate fills in the value. This approach is verbose when there are many distinct values, but it works across all SQL databases and does not require any special syntax.

CASE in ORDER BY

CASE expressions in ORDER BY let you define a custom sort order that alphabetical or numeric sorting cannot express:

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.