How to Use CASE Statements in MySQL
CASE statements are MySQL's primary tool for conditional logic within SQL queries. Unlike procedural IF statements in stored procedures, CASE expressions work directly in SELECT, UPDATE, and ORDER BY...
Key Insights
- MySQL offers two CASE statement forms: simple CASE for comparing one expression against multiple values, and searched CASE for evaluating independent conditions—choose searched CASE when you need complex boolean logic across different columns
- CASE statements excel at conditional aggregation, letting you calculate multiple metrics in a single query pass instead of running separate queries for each condition, which dramatically improves performance on large datasets
- Always include an ELSE clause in production code to handle unexpected values explicitly; without it, MySQL returns NULL for unmatched cases, which can silently corrupt calculations and aggregations
Introduction to CASE Statements
CASE statements are MySQL’s primary tool for conditional logic within SQL queries. Unlike procedural IF statements in stored procedures, CASE expressions work directly in SELECT, UPDATE, and ORDER BY clauses, making them essential for data transformation and reporting.
MySQL provides two CASE forms. The simple CASE compares a single expression against multiple possible values—think of it as a SQL switch statement. The searched CASE evaluates independent boolean conditions, giving you full control over complex logic. Most real-world scenarios demand searched CASE, but simple CASE offers cleaner syntax when applicable.
You’ll reach for CASE statements when transforming raw data into business categories, performing conditional aggregations without multiple queries, implementing dynamic sorting logic, or handling NULL values in joins. Master these patterns and you’ll write more efficient, readable queries.
Simple CASE Syntax and Basic Examples
Simple CASE compares one expression against multiple values. The syntax is straightforward:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END
Here’s a practical example categorizing products by price:
SELECT
product_name,
price,
CASE price
WHEN price < 20 THEN 'Budget'
WHEN price < 100 THEN 'Mid-Range'
ELSE 'Premium'
END AS price_category
FROM products;
Wait—that won’t work. Simple CASE only tests equality, not ranges. This is why you’ll use searched CASE more often. But simple CASE shines for exact matches:
SELECT
product_name,
category,
CASE category
WHEN 'electronics' THEN 'Tech'
WHEN 'clothing' THEN 'Apparel'
WHEN 'food' THEN 'Grocery'
ELSE 'Other'
END AS department
FROM products;
UPDATE statements benefit from CASE when applying different logic to different rows:
UPDATE products
SET discount_percent = CASE category
WHEN 'electronics' THEN 15
WHEN 'clothing' THEN 25
WHEN 'clearance' THEN 50
ELSE 10
END
WHERE active = 1;
This updates all active products in one pass, applying category-specific discounts without multiple UPDATE queries.
Searched CASE for Complex Conditions
Searched CASE evaluates independent boolean expressions, making it far more flexible:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
Customer segmentation demonstrates searched CASE’s power:
SELECT
customer_id,
age,
total_purchases,
country,
CASE
WHEN age < 25 AND total_purchases > 1000 THEN 'Young High-Value'
WHEN age >= 25 AND age < 45 AND total_purchases > 5000 THEN 'Prime High-Value'
WHEN age >= 45 AND total_purchases > 3000 THEN 'Mature High-Value'
WHEN total_purchases < 100 THEN 'Low-Value'
ELSE 'Standard'
END AS customer_segment
FROM customers;
The conditions evaluate in order—first match wins. Position your most specific conditions first, then fall back to broader categories.
Nested business logic becomes manageable with searched CASE:
SELECT
order_id,
order_total,
customer_tier,
shipping_country,
CASE
WHEN customer_tier = 'platinum' THEN 0
WHEN customer_tier = 'gold' AND order_total > 100 THEN 0
WHEN shipping_country = 'US' AND order_total > 50 THEN 5.99
WHEN shipping_country = 'US' THEN 9.99
WHEN order_total > 100 THEN 15.99
ELSE 24.99
END AS shipping_cost
FROM orders;
This replaces what would otherwise require application-level code or multiple database calls.
CASE in Aggregate Functions
CASE statements inside aggregate functions enable conditional calculations in a single query. Instead of running separate queries for each metric, you calculate everything in one pass.
Conditional counting:
SELECT
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
COUNT(CASE WHEN total > 1000 THEN 1 END) AS high_value_orders
FROM orders
WHERE order_date >= '2024-01-01';
The CASE expression returns 1 for matching rows and NULL otherwise. COUNT ignores NULLs, so you get accurate totals. Some developers write COUNT(CASE WHEN condition THEN 1 ELSE NULL END), but the ELSE NULL is implicit.
Conditional SUM calculates multiple totals simultaneously:
SELECT
DATE(order_date) AS order_day,
SUM(CASE WHEN region = 'North' THEN total ELSE 0 END) AS north_revenue,
SUM(CASE WHEN region = 'South' THEN total ELSE 0 END) AS south_revenue,
SUM(CASE WHEN region = 'East' THEN total ELSE 0 END) AS east_revenue,
SUM(CASE WHEN region = 'West' THEN total ELSE 0 END) AS west_revenue,
SUM(total) AS total_revenue
FROM orders
GROUP BY DATE(order_date);
This pivot-style query transforms rows into columns, creating a daily revenue report by region. The alternative—separate queries per region—would scan the table four times instead of once.
Advanced Patterns and ORDER BY
CASE statements in ORDER BY enable dynamic sorting logic:
SELECT
product_name,
category,
stock_level,
price
FROM products
ORDER BY
CASE
WHEN stock_level = 0 THEN 1
WHEN stock_level < 10 THEN 2
ELSE 3
END,
category,
price DESC;
This sorts out-of-stock items first, low-stock items second, then everything else—all while maintaining secondary sorts by category and price.
Parameterized sorting for user-facing applications:
-- Assume @sort_by is a parameter: 'name', 'price', or 'rating'
SELECT product_name, price, rating
FROM products
ORDER BY
CASE
WHEN @sort_by = 'name' THEN product_name
WHEN @sort_by = 'price' THEN CAST(price AS CHAR)
WHEN @sort_by = 'rating' THEN CAST(rating AS CHAR)
END;
Note the type casting—CASE expressions must return the same data type for all branches. When mixing types, cast everything to a common type (usually CHAR for ORDER BY).
CASE with JOINs handles missing relationships gracefully:
SELECT
o.order_id,
o.customer_id,
CASE
WHEN c.customer_id IS NULL THEN 'Deleted Customer'
ELSE c.customer_name
END AS customer_name,
o.total
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
This prevents NULL customer names from appearing in reports when customer records have been deleted.
Performance Considerations and Best Practices
CASE statements execute efficiently—they’re expressions, not control flow. However, they can’t leverage indexes the same way simple WHERE clauses can.
Consider this comparison:
-- Using CASE (scans entire table)
SELECT customer_id, status
FROM customers
WHERE CASE
WHEN age < 25 THEN 'young'
WHEN age >= 65 THEN 'senior'
ELSE 'adult'
END = 'young';
-- Better: direct condition (can use index on age)
SELECT customer_id, status
FROM customers
WHERE age < 25;
Check execution plans with EXPLAIN:
EXPLAIN SELECT * FROM customers
WHERE CASE WHEN age < 25 THEN 1 ELSE 0 END = 1;
EXPLAIN SELECT * FROM customers
WHERE age < 25;
The second query will show index usage; the first typically won’t.
Use CASE in SELECT clauses for data transformation, but avoid it in WHERE clauses when simple conditions suffice. For conditional filtering with multiple criteria, use OR:
-- Avoid
WHERE CASE WHEN condition1 THEN 1 WHEN condition2 THEN 1 ELSE 0 END = 1
-- Prefer
WHERE condition1 OR condition2
Keep CASE expressions readable. Extract complex logic into views or refactor into multiple simpler CASE statements:
-- Hard to maintain
SELECT CASE WHEN ... THEN CASE WHEN ... THEN ... END END
-- Better: break into steps
SELECT
CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS age_group,
CASE WHEN purchases > 1000 THEN 'high' ELSE 'low' END AS value_tier
FROM customers;
Common Pitfalls and Troubleshooting
The most common error is forgetting the ELSE clause. Without it, unmatched cases return NULL:
-- Dangerous: returns NULL for prices >= 100
SELECT
product_name,
CASE
WHEN price < 20 THEN 'Budget'
WHEN price < 100 THEN 'Mid-Range'
END AS category
FROM products;
-- Safe: explicit handling of all cases
SELECT
product_name,
CASE
WHEN price < 20 THEN 'Budget'
WHEN price < 100 THEN 'Mid-Range'
ELSE 'Premium'
END AS category
FROM products;
NULL handling requires explicit checks:
SELECT
customer_name,
CASE
WHEN last_purchase_date IS NULL THEN 'Never purchased'
WHEN last_purchase_date < DATE_SUB(NOW(), INTERVAL 1 YEAR) THEN 'Inactive'
ELSE 'Active'
END AS status
FROM customers;
Remember: NULL = NULL evaluates to NULL (unknown), not TRUE. Always use IS NULL for NULL checks.
Data type mismatches cause errors:
-- Error: mixing strings and numbers
CASE
WHEN condition1 THEN 'text'
WHEN condition2 THEN 42
END
-- Fixed: consistent types
CASE
WHEN condition1 THEN 'text'
WHEN condition2 THEN '42'
END
MySQL will attempt implicit conversion, but explicit casting prevents surprises:
CASE
WHEN status = 'active' THEN CAST(days_active AS CHAR)
ELSE 'N/A'
END
CASE statements are SQL’s conditional workhorse. Master simple and searched forms, leverage them in aggregations, but stay aware of indexing implications. With proper ELSE clauses and type handling, they’ll make your queries more efficient and maintainable.