CASE Expression
SQL · Conditional Expressions
Theory
CASE brings if / else logic into SQL. The searched form checks conditions in order: CASE WHEN salary > 80000 THEN 'Senior' WHEN salary > 60000 THEN 'Mid' ELSE 'Junior' END. The first WHEN that is TRUE decides the result, and later branches are skipped. If no branch matches and there is no ELSE, the result is NULL. The simple form compares one value against several: CASE dept WHEN 'IT' THEN 'Tech' WHEN 'HR' THEN 'People' END.
CASE is an expression, so it can go anywhere a value can: SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates. SUM(CASE WHEN city = 'Jaipur' THEN amount ELSE 0 END) is the classic conditional sum, used to pivot rows into columns. All branches should return the same data type. Because order matters, put the most specific condition first.
Why we use it
Raw values often need labels, buckets or rules applied: salary bands, pass/fail, region names, custom sort orders. CASE applies that logic right in the query instead of in a spreadsheet afterwards.
When to use it
Use CASE for bucketing, labelling, conditional aggregation and custom sorting. For simple NULL replacement, COALESCE is shorter. For long mappings that change often, a lookup table joined in is easier to maintain.
How it works
- Evaluate CASE for a row
- Is the first WHEN condition TRUE? Yes: Return its THEN value. No: Check the next WHEN.
- Is any later WHEN TRUE? Yes: Return the first matching THEN value. No: Go to ELSE.
- Return the ELSE value, or NULL if there is no ELSE
- Move to the next row
Syntax
CASE
WHEN condition THEN result
ELSE default
ENDExample
SELECT name,
CASE WHEN salary > 80000 THEN 'Senior' ELSE 'Junior' END AS level
FROM employees;Key points
- Can be used in SELECT, WHERE, ORDER BY, GROUP BY and aggregates
- Supports multiple WHEN branches, checked top to bottom
- The first TRUE branch wins; put specific conditions first
- No match and no ELSE returns NULL
- SUM(CASE WHEN ... THEN x ELSE 0 END) gives conditional totals