MySQL CASE Expression

MySQL CASE Expression

The MySQL CASE expression evaluates conditions in top-to-bottom order and returns the result of the first matching WHEN branch. It works inside SELECT lists, ORDER BY, GROUP BY, UPDATE statements, and aggregate queries.

Syntax

MySQL supports two CASE forms:

-- 1. Searched CASE (supports arbitrary boolean conditions)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE fallback_result
END

-- 2. Simple CASE (compares one expression for equality)
CASE expr
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE fallback_result
END

CASE is an expression (not a CASE() function) in SQL statements, and always closes with END. (Inside stored programs, MySQL also has a CASE ... END CASE flow statement, which is separate from query expressions.)

Practical Example

In demo_orders, you can label orders by spending tier and handle missing totals safely with searched CASE.

SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount IS NULL THEN 'Pending Quote'
        WHEN total_amount >= 500 THEN 'Enterprise'
        WHEN total_amount >= 100 THEN 'Standard'
        ELSE 'Starter'
    END AS order_tier
FROM demo_orders
ORDER BY order_id;

Common Mistakes & NULL Behavior

• Omitting ELSE returns NULL: If no WHEN branch matches and you omit ELSE, CASE returns NULL. Always add ELSE when downstream code expects a non-null label or zero.
• Simple CASE cannot match NULL: Writing CASE status WHEN NULL THEN 'Unknown' fails silently because status = NULL evaluates to UNKNOWN, never TRUE. Always use searched CASE WHEN status IS NULL THEN 'Unknown'.
• Short-circuit evaluation: MySQL stops at the first true WHEN clause, so place more specific conditions (like >= 500) above broader ones (>= 100).
• Mixed return types: Returning integers in one branch and strings in another forces MySQL to coerce all branch results to a single compatible type (usually string).

Frequently Asked Questions

What is the difference between simple CASE and searched CASE in MySQL?

Simple CASE tests one expression for equality against literal values. Searched CASE evaluates full boolean expressions (>, IS NULL, AND), making it far more flexible.

Can I use CASE inside SUM() or COUNT()?

Yes. Conditional aggregation like SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) is a standard pattern for pivot-style summaries in MySQL.

Does MySQL CASE end with END or END CASE inside SELECT?

Inside SELECT, WHERE, and ORDER BY, always close CASE with END. END CASE is only valid for procedural blocks in stored procedures.