intermediate
CASE Expressions
Add if/else logic directly inside a query to label, bucket, and transform values on the fly.
6 min read
Explanation
SQL has no if keyword, but it has CASE, which does the same job. A CASE
expression evaluates conditions in order and returns the first matching value.
It's incredibly handy for bucketing numbers into ranges, renaming codes into
labels, or computing conditional aggregates.
There are two forms:
- Simple CASE: compare one value against a list (
CASE x WHEN 1 THEN ...). - Searched CASE: evaluate independent boolean conditions (
CASE WHEN x > 10 THEN ...), which is more flexible.
Works anywhere a value does
Because CASE returns a value, you can put it in SELECT, WHERE, ORDER BY, GROUP
BY, or even inside an aggregate like COUNT(CASE WHEN ... THEN 1 END).
Syntax
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE default_result
ENDInteractive Example
Label each employee's salary as Low / Mid / High. Then count how many employees fall into each band using CASE inside an aggregate.
Loading database engine...
Loading database engine...
Common Mistakes
- Forgetting END. A CASE must always close with
END, or the query fails. - Overlapping WHEN clauses. Conditions are tested top to bottom, so put the most specific/narrowest first; a row stops at the first match.
- No ELSE. Unmatched rows return NULL — usually not what you want.
Best Practices
- Order
WHENbranches from most specific to least specific. - Add an explicit
ELSEso the output is predictable. - Use CASE inside aggregates to build pivot-style counts in a single query.
Practice Question
Write a query on employees that returns each employee's name and a tenure
label: 'Junior' if hired after 2021, 'Senior' if hired in 2019–2021, and 'Veteran'
if hired before 2019, ordered by hire_date.
Summary
CASE brings conditional logic into SQL, returning one value based on the first
matching WHEN. It works anywhere a value is allowed and is especially powerful
inside aggregates for grouped counts.