MySQL Case

The CASE Expression

CASE evaluates a series of conditions in order and returns the result associated with the first one that's true, similar to an if/elseif/else chain in a programming language. It's an expression, not a statement, so it can be used almost anywhere a value is expected: in SELECT, WHERE, ORDER BY, and GROUP BY.

Simple vs Searched CASE

There are two forms. The simple form compares one expression against a list of exact values, similar to a switch statement: CASE status WHEN 'P' THEN ... . The searched form evaluates independent boolean conditions instead, similar to a chain of if statements: CASE WHEN salary < 30000 THEN ... . The searched form is more flexible because each branch can test something completely different, like ranges or combinations of columns.

• Categorizing numeric values into readable ranges or bands
• Translating short codes or enum values into human-readable labels
• Performing conditional aggregation, such as counting rows that meet different conditions in one pass
• Producing a custom sort order that doesn't match the natural ordering of a column

SELECT name, salary,
  CASE
    WHEN salary < 30000 THEN 'Low'
    WHEN salary < 70000 THEN 'Medium'
    ELSE 'High'
  END AS salary_band
FROM employees;

The ELSE branch is optional. If you omit it and no WHEN condition matches, the CASE expression simply returns NULL for that row instead of raising an error — so it's good practice to include ELSE whenever a row falling through unmatched would be a real possibility.

SELECT order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

SELECT
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count
FROM employees;