MySQL CASE

The CASE expression adds conditional logic directly inside a SQL query. It works like an if-else chain — it evaluates conditions in order and returns the result for the first condition that is true. If no condition matches, the optional ELSE value is returned. If ELSE is omitted and nothing matches, the expression returns NULL.

A CASE expression can appear anywhere a value is valid — in SELECT, ORDER BY, WHERE, or inside an aggregate function.

Simple CASE

A simple CASE compares one expression to a list of specific values:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Example — label each order by its status code:

SELECT id, amount,
    CASE status
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Processing'
        WHEN 3 THEN 'Shipped'
        WHEN 4 THEN 'Delivered'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

Searched CASE

A searched CASE evaluates a boolean condition in each WHEN clause — more flexible since each branch can test a different expression:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

Example — categorise products by price range:

SELECT name, price,
    CASE
        WHEN price < 1000 THEN 'Budget'
        WHEN price BETWEEN 1000 AND 20000 THEN 'Mid-range'
        WHEN price > 20000 THEN 'Premium'
        ELSE 'Uncategorised'
    END AS price_tier
FROM products
ORDER BY price;

CASE in ORDER BY — Custom Sort

Use CASE in ORDER BY to sort by a custom priority rather than the raw column value:

-- Sort orders: Pending first, then Processing, then the rest
SELECT id, amount, status
FROM orders
ORDER BY
    CASE status
        WHEN 'Pending'    THEN 1
        WHEN 'Processing' THEN 2
        ELSE 3
    END;

CASE Inside Aggregate Functions — Conditional Count

One of the most powerful uses: embed CASE inside SUM or COUNT to pivot or conditionally aggregate data:

-- Count products per price tier in a single query
SELECT
    SUM(CASE WHEN price < 1000 THEN 1 ELSE 0 END) AS budget,
    SUM(CASE WHEN price BETWEEN 1000 AND 20000 THEN 1 ELSE 0 END) AS mid_range,
    SUM(CASE WHEN price > 20000 THEN 1 ELSE 0 END) AS premium
FROM products;

Key Points to Remember

• Simple CASE compares one expression to fixed values. Searched CASE evaluates separate boolean conditions.
• Conditions are checked in order — the first matching WHEN wins and the rest are skipped.
• If no condition matches and there is no ELSE, the expression returns NULL.
• CASE can be used in SELECT, ORDER BY, WHERE, and inside aggregate functions.
• Embedding CASE inside SUM() is a clean way to count or total values conditionally without multiple queries.