MySQL CASE Expression

MySQL CASE Expression: Conditional Logic in SQL

The MySQL CASE expression adds if / else if / else branching directly inside SQL queries. You can use CASE to bucket numbers into tiers, build pivot-style counts, or customize sort order without post-processing rows in application code.

Two Forms of CASE Syntax

Searched CASE evaluates general boolean conditions (WHEN price >= 100), while Simple CASE compares one expression against fixed values (CASE status WHEN 'paid'). Searched CASE is more flexible because it supports ranges and IS NULL:

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 200 THEN 'Premium'
        WHEN price >= 50  THEN 'Standard'
        ELSE 'Budget'
    END AS price_tier
FROM demo_products;

Practical SQL Examples

This query groups demo_products into price tiers and counts how many products fall into each bucket:

SELECT
    CASE
        WHEN price >= 200 THEN 'Premium'
        WHEN price >= 50  THEN 'Standard'
        ELSE 'Budget'
    END AS price_tier,
    COUNT(*) AS product_count
FROM demo_products
GROUP BY price_tier
ORDER BY product_count DESC;

You can also use CASE inside ORDER BY to pin urgent rows at the top of a queue:

SELECT id, status, total_amount
FROM demo_orders
ORDER BY
    CASE status
        WHEN 'on_hold' THEN 1
        WHEN 'pending' THEN 2
        ELSE 3
    END,
    id DESC;

Common Mistakes and Best Practices

• Always Include ELSE: Without an ELSE branch, unmatched rows evaluate to NULL, which can break downstream math or API formatting. Supply a clear default string or zero fallback.
• Order Conditions Most Specific First: CASE stops at the first true WHEN branch. Put WHEN score >= 90 before WHEN score >= 70, otherwise higher numbers match the looser branch early.
• Repeat CASE Expressions or Use a CTE: To reuse a computed CASE label in WHERE or multiple calculations, wrap it inside a CTE first.

Frequently Asked Questions

Can I use CASE inside UPDATE statements?

Yes, UPDATE demo_products SET price = CASE WHEN category = 'Books' THEN price * 1.05 ELSE price END; updates multiple tiers in one query.

Is CASE portable outside MySQL?

Yes, CASE ... WHEN ... THEN ... ELSE ... END is ANSI SQL standard and works identically in PostgreSQL, SQLite, and SQL Server.

What happens if CASE status WHEN NULL runs?

In simple CASE, status = NULL is always unknown and never matches! Use searched CASE WHEN status IS NULL THEN ... instead.