Topic 29 of 52
CASE WHEN Expressions
Overview
SQL is not just for fetching raw data; it is capable of complex inline transformations. The CASE statement is SQL's version of an if/else block. It allows you to evaluate conditions row-by-row and mutate the output on the fly. This is incredibly useful for bucketing data (e.g., turning ages into 'Child', 'Adult', 'Senior') or pivoting tables without writing any Python/JS backend code.
Syntax
sql
-- Syntax: CASE WHEN condition THEN result ELSE fallback END
SELECT
order_id,
total_amount,
-- Creates a brand new, calculated column on the fly!
CASE
WHEN total_amount > 1000 THEN 'High Value'
WHEN total_amount > 100 THEN 'Standard'
ELSE 'Low Value'
END AS order_category
FROM orders;
-- You can also use it inside Aggregate Functions! (Pivot logic)
SELECT
department,
-- Count ONLY the active employees!
SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) AS active_count
FROM employees
GROUP BY department;Common Pitfalls
- Forgetting the
ENDkeyword. ACASEstatement must be explicitly closed withEND(and usually anAS alias). If you forget it, the parser will crash. - Overlapping conditions.
CASEevaluates strictly top-to-bottom and stops at the FIRST true condition. If you writeWHEN age > 10 THEN 'Kid' WHEN age > 20 THEN 'Adult', a 25-year-old will be labeled a 'Kid' because the first condition caught them. Always order your logic from most restrictive to least restrictive.
Interview Questions
Q:
How do you handle a scenario where a row matches NONE of the
WHEN conditions, and you forgot to provide an ELSE statement?A:
If no conditions evaluate to True, and there is no explicit ELSE provided, the CASE statement will automatically and silently return NULL for that row.
Real-World Example
Data scrubbing: Normalizing messy user input (M, m, Male, F, f, Female) into a standardized format before returning it to the frontend.
example
sql
SELECT
username,
CASE
WHEN UPPER(gender) IN ('M', 'MALE') THEN 'Male'
WHEN UPPER(gender) IN ('F', 'FEMALE') THEN 'Female'
ELSE 'Unspecified'
END AS standardized_gender
FROM users;Check Your Knowledge
Test your understanding of CASE WHEN Expressions with these quick questions.