How does CASE WHEN work in SQL?

CASE WHEN is SQL's if/else expression - it evaluates conditions and returns a value for the first one that matches, or a default ELSE value.

SQLPostgreSQL

Overview

`CASE WHEN` is an inline conditional expression in SQL, like an if/elseif/else chain. It can be used anywhere an expression is valid: `SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`, or inside aggregate functions.

Key Points

•Searched form: `CASE WHEN condition THEN result ... ELSE default END`.
•Simple form: `CASE column WHEN value THEN result ... END` (like a switch statement).
•Returns `NULL` if no condition matches and there is no `ELSE`.
•`ELSE` is optional but recommended to avoid unexpected `NULL` results.
•Can be used inside `SUM()`, `COUNT()`, `AVG()` for conditional aggregation.

Examples

Categorise rows with CASE WHEN

sql

SELECT
  name,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM students;

Simple CASE (switch-style)

sql

SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'   THEN 'Awaiting payment'
    WHEN 'paid'      THEN 'Processing'
    WHEN 'shipped'   THEN 'On its way'
    WHEN 'delivered' THEN 'Completed'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Conditional aggregation - pivot-style reporting

sql

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

One scan, multiple derived metrics - much more efficient than separate queries.

Custom sort order with CASE in ORDER BY

sql

SELECT * FROM tasks
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'medium'   THEN 3
    WHEN 'low'      THEN 4
    ELSE 5
  END,
  created_at DESC;

Pro Tip

The conditional aggregation pattern (`SUM(CASE WHEN ... THEN amount END)`) is one of the most useful SQL techniques for reports. It computes multiple breakdowns in a single pass over the data, avoiding the need for multiple queries or application-side grouping.