CASE Expressions in SQL

CASE is SQL's conditional expression — the closest thing to an if/else statement you can embed directly inside a query, usable anywhere a value is expected: SELECT, WHERE, ORDER BY, even inside an aggregate function.

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

Conditions are checked top to bottom, and the result of the first one that matches is used — order matters, the same as a chain of if/else if in any programming language. ELSE is optional; without it, a row matching no condition gets NULL.

Simple form: comparing one expression against fixed values

When every branch compares the same column against a fixed value, there's a shorter form:

SELECT
  name,
  status,
  CASE status
    WHEN 'pending' THEN 'Awaiting payment'
    WHEN 'shipped' THEN 'On the way'
    WHEN 'delivered' THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

This is equivalent to WHEN status = 'pending' THEN ... written out for each case, but reads more directly when every branch is just an equality check.

Conditional aggregation

Combining CASE with an aggregate function is a common technique for computing multiple conditional counts or sums in a single pass over the data, instead of running several separate queries:

SELECT
  customer_id,
  SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) AS delivered_total,
  SUM(CASE WHEN status = 'returned' THEN total ELSE 0 END) AS returned_total
FROM orders
GROUP BY customer_id;

For each row, the CASE resolves to either the order's total or 0 depending on its status, and SUM then adds up only the values that matched — effectively a conditional sum, computed for every customer in one query instead of two.

CASE in ORDER BY

As mentioned in the ORDER BY lesson, CASE is how you express a custom sort order that isn't simply ascending or descending:

SELECT name, status FROM orders
ORDER BY
  CASE status
    WHEN 'pending' THEN 1
    WHEN 'shipped' THEN 2
    WHEN 'delivered' THEN 3
  END;

CASE returns a value that ORDER BY then sorts on normally — here, an arbitrary priority number that puts pending orders first regardless of what status alphabetizes to.

With querying and aggregating data covered, the next section turns to schema design — starting with normalization, the principles behind how the users/orders tables used throughout this course ended up split the way they are.