CASE WHEN — conditional expressions

CASE WHEN evaluates conditions top-to-bottom and returns the result for the first match. It is SQL's if/else.

Searched CASE (most common)

SELECT
  first_name,
  salary,
  CASE
    WHEN salary `<` 5000  THEN 'Junior'
    WHEN salary `<` 10000 THEN 'Mid'
    ELSE 'Senior'
  END AS tier
FROM employees;

• Conditions are tested in order; the first match wins.
• ELSE is optional. If omitted and no condition matches, the result is NULL.
• END closes the expression.

Simple CASE (equality checks only)

SELECT
  id,
  CASE status
    WHEN 'shipped'   THEN 'On the way'
    WHEN 'delivered' THEN 'Complete'
    WHEN 'cancelled' THEN 'Cancelled'
    ELSE 'Processing'
  END AS status_label
FROM orders;

CASE in ORDER BY

Control sort order programmatically:

ORDER BY
  CASE status
    WHEN 'cancelled' THEN 1
    WHEN 'pending'   THEN 2
    ELSE 3
  END

CASE in aggregates (conditional counting)

SELECT
  COUNT(*) FILTER (WHERE salary `<` 5000)  AS junior_count,
  COUNT(*) FILTER (WHERE salary `>=` 5000
                     AND salary `<` 10000) AS mid_count,
  COUNT(*) FILTER (WHERE salary `>=` 10000) AS senior_count
FROM employees;

(The FILTER approach is cleaner; CASE inside COUNT works too.)

Tips

• CASE can be used in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING.
• Nest CASE expressions for multi-dimensional logic, but prefer FILTER or a lookup table for readability.
• All branches must return compatible data types.