SQL - CASE WHEN Statement with Examples
SQL offers two CASE expression formats. The simple CASE compares a single expression against multiple possible values:
Key Insights
- CASE expressions provide conditional logic in SQL, functioning like if-else statements to transform data during query execution rather than in application code
- Simple CASE compares one expression against multiple values, while searched CASE evaluates independent boolean conditions for maximum flexibility
- Proper use of CASE in SELECT, WHERE, ORDER BY, and aggregate functions eliminates the need for multiple queries or complex application-layer data manipulation
Understanding CASE Syntax
SQL offers two CASE expression formats. The simple CASE compares a single expression against multiple possible values:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END
The searched CASE evaluates independent conditions, providing greater flexibility:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
Both formats evaluate conditions sequentially and return the first matching result. The ELSE clause is optional but recommended to handle unexpected values explicitly.
Basic Column Transformation
Transform raw data into readable formats directly in your queries. This example categorizes products by price range:
SELECT
product_name,
price,
CASE
WHEN price < 20 THEN 'Budget'
WHEN price BETWEEN 20 AND 100 THEN 'Standard'
WHEN price > 100 THEN 'Premium'
ELSE 'Unpriced'
END AS price_category
FROM products;
Results:
product_name | price | price_category
----------------|--------|---------------
Keyboard | 45.99 | Standard
Mouse | 12.50 | Budget
Monitor | 299.00 | Premium
Simple CASE works well for direct value matching:
SELECT
order_id,
status,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'S' THEN 'Shipped'
WHEN 'D' THEN 'Delivered'
WHEN 'C' THEN 'Cancelled'
ELSE 'Unknown'
END AS status_description
FROM orders;
Conditional Aggregation
Calculate multiple aggregates in a single query using CASE within aggregate functions. This eliminates multiple passes over the data:
SELECT
department,
COUNT(*) AS total_employees,
SUM(CASE WHEN salary > 75000 THEN 1 ELSE 0 END) AS high_earners,
SUM(CASE WHEN hire_date >= '2023-01-01' THEN 1 ELSE 0 END) AS recent_hires,
AVG(CASE WHEN performance_rating >= 4 THEN salary END) AS avg_top_performer_salary
FROM employees
GROUP BY department;
This pattern is particularly powerful for pivot-style reporting:
SELECT
product_category,
SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount ELSE 0 END) AS q1_sales,
SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN amount ELSE 0 END) AS q2_sales,
SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 3 THEN amount ELSE 0 END) AS q3_sales,
SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 4 THEN amount ELSE 0 END) AS q4_sales
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2024
GROUP BY product_category;
Dynamic Sorting with ORDER BY
Control sort order based on conditions. This example implements custom business logic for prioritizing orders:
SELECT
order_id,
customer_name,
order_date,
priority,
total_amount
FROM orders
ORDER BY
CASE
WHEN priority = 'URGENT' THEN 1
WHEN priority = 'HIGH' THEN 2
WHEN priority = 'NORMAL' THEN 3
ELSE 4
END,
order_date DESC;
Combine multiple CASE expressions for complex sorting requirements:
SELECT
employee_name,
department,
salary,
performance_rating
FROM employees
ORDER BY
CASE department
WHEN 'Executive' THEN 1
WHEN 'Engineering' THEN 2
WHEN 'Sales' THEN 3
ELSE 4
END,
CASE
WHEN performance_rating >= 4.5 THEN 1
WHEN performance_rating >= 3.5 THEN 2
ELSE 3
END,
salary DESC;
Conditional WHERE Clauses
Implement dynamic filtering logic. This pattern is useful for stored procedures or parameterized queries:
DECLARE @filter_type VARCHAR(20) = 'active';
DECLARE @min_amount DECIMAL(10,2) = 1000;
SELECT
customer_id,
customer_name,
account_status,
total_purchases
FROM customers
WHERE
CASE
WHEN @filter_type = 'active' THEN
CASE WHEN account_status = 'ACTIVE' THEN 1 ELSE 0 END
WHEN @filter_type = 'high_value' THEN
CASE WHEN total_purchases >= @min_amount THEN 1 ELSE 0 END
WHEN @filter_type = 'all' THEN 1
ELSE 0
END = 1;
For cleaner code, use CASE to handle NULL comparisons:
SELECT
product_id,
product_name,
discontinued_date
FROM products
WHERE
CASE
WHEN discontinued_date IS NULL THEN 1
WHEN discontinued_date > CURRENT_DATE THEN 1
ELSE 0
END = 1;
UPDATE with Conditional Logic
Apply different updates based on row conditions in a single statement:
UPDATE employees
SET
salary = CASE
WHEN performance_rating >= 4.5 AND years_employed >= 3 THEN salary * 1.10
WHEN performance_rating >= 4.0 THEN salary * 1.07
WHEN performance_rating >= 3.0 THEN salary * 1.03
ELSE salary
END,
bonus = CASE
WHEN performance_rating >= 4.5 THEN salary * 0.15
WHEN performance_rating >= 4.0 THEN salary * 0.10
WHEN performance_rating >= 3.5 THEN salary * 0.05
ELSE 0
END
WHERE last_review_date >= '2024-01-01';
Update multiple columns with interdependent logic:
UPDATE inventory
SET
stock_status = CASE
WHEN quantity = 0 THEN 'OUT_OF_STOCK'
WHEN quantity <= reorder_point THEN 'LOW_STOCK'
WHEN quantity > reorder_point * 3 THEN 'OVERSTOCKED'
ELSE 'NORMAL'
END,
reorder_needed = CASE
WHEN quantity <= reorder_point THEN TRUE
ELSE FALSE
END,
priority_level = CASE
WHEN quantity = 0 AND days_out_of_stock > 7 THEN 'CRITICAL'
WHEN quantity <= reorder_point THEN 'HIGH'
ELSE 'NORMAL'
END;
Nested CASE Expressions
Handle complex conditional logic by nesting CASE expressions:
SELECT
employee_id,
employee_name,
department,
salary,
years_employed,
CASE
WHEN department = 'Sales' THEN
CASE
WHEN years_employed >= 5 THEN salary * 1.15
WHEN years_employed >= 2 THEN salary * 1.10
ELSE salary * 1.05
END
WHEN department = 'Engineering' THEN
CASE
WHEN years_employed >= 5 THEN salary * 1.12
WHEN years_employed >= 2 THEN salary * 1.08
ELSE salary * 1.04
END
ELSE
CASE
WHEN years_employed >= 5 THEN salary * 1.08
ELSE salary * 1.03
END
END AS projected_salary
FROM employees;
Performance Considerations
CASE expressions execute for every row, so complex logic impacts query performance. Index columns referenced in CASE conditions:
-- Good: Uses indexed column in WHERE, CASE only for display
SELECT
customer_id,
CASE
WHEN total_purchases > 10000 THEN 'VIP'
ELSE 'Standard'
END AS tier
FROM customers
WHERE total_purchases > 1000;
-- Better: Move logic to WHERE when possible
SELECT customer_id, 'VIP' AS tier
FROM customers
WHERE total_purchases > 10000
UNION ALL
SELECT customer_id, 'Standard' AS tier
FROM customers
WHERE total_purchases BETWEEN 1000 AND 10000;
Avoid repeating expensive CASE expressions. Use CTEs or derived tables:
WITH categorized_products AS (
SELECT
product_id,
product_name,
price,
CASE
WHEN price < 20 THEN 'Budget'
WHEN price BETWEEN 20 AND 100 THEN 'Standard'
ELSE 'Premium'
END AS category
FROM products
)
SELECT
category,
COUNT(*) AS product_count,
AVG(price) AS avg_price
FROM categorized_products
GROUP BY category;
CASE expressions provide essential conditional logic directly in SQL, reducing application complexity and network overhead. Master these patterns to write more efficient, maintainable queries that handle complex business rules at the database layer.