How Does the CASE Statement Work in SQL?
Understand the SQL CASE statement: simple vs searched forms, WHEN/THEN/ELSE logic, conditional aggregation, and where CASE can be used, with examples.
Expected Interview Answer
The CASE statement is SQL's conditional expression that returns a value based on the first matching condition, working like an if-then-else inside a query.
CASE comes in two forms: a simple CASE that compares one expression to a list of values, and a searched CASE that evaluates independent boolean conditions in WHEN clauses. It returns the result of the first WHEN that is true, or the ELSE value if none match (NULL when ELSE is omitted). Because it is an expression, CASE can appear in SELECT, WHERE, ORDER BY, GROUP BY, and aggregate functions.
- Adds conditional logic without leaving SQL
- Works inside SELECT, WHERE, ORDER BY and aggregates
- Transforms and categorizes values on the fly
- Enables conditional aggregation and pivoting
- Portable across most SQL databases
AI Mentor Explanation
A CASE statement is like a scorer deciding how to label each delivery: if runs equal six call it a maximum, if four call it a boundary, if a wicket falls call it a dismissal, otherwise just runs. The scorer checks conditions in order and writes the first label that fits. SQL's CASE does the same, evaluating each WHEN until one is true and returning that result.
Step-by-Step Explanation
Step 1
Choose the form
Use simple CASE to compare one expression to values, or searched CASE for independent boolean conditions.
Step 2
Write WHEN clauses
List each condition with its return value, ordered from most specific to least specific.
Step 3
Add an ELSE
Provide an ELSE for the fallback value; without it, unmatched rows return NULL.
Step 4
Close with END
Terminate the expression with END, and optionally alias it with AS.
Step 5
Place it in the query
Drop the CASE into SELECT, WHERE, ORDER BY, GROUP BY, or inside an aggregate as needed.
What Interviewer Expects
- Difference between simple and searched CASE
- Knowledge that the first matching WHEN wins
- Behavior of a missing ELSE returning NULL
- Using CASE inside aggregates for conditional counts
- Understanding CASE is an expression, not a control-flow statement
Common Mistakes
- Forgetting the END keyword
- Assuming all WHEN clauses are evaluated after a match
- Expecting NULL comparisons to work with simple CASE
- Omitting ELSE and being surprised by NULL results
- Confusing CASE with procedural IF blocks
Best Answer (HR Friendly)
“The CASE statement lets you add if-then-else logic directly inside a SQL query. It checks a list of conditions in order and returns the value tied to the first one that is true, with an optional default when nothing matches.”
Code Example
SELECT
name,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid'
ELSE 'Junior'
END AS band
FROM employees;SELECT
department,
COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS senior_count,
COUNT(*) AS total
FROM employees
GROUP BY department;Follow-up Questions
- What is the difference between simple and searched CASE?
- What does CASE return when no WHEN matches and there is no ELSE?
- Can you use CASE inside an ORDER BY clause?
- How do you count conditionally using CASE?
- Why does simple CASE not work well with NULL comparisons?
MCQ Practice
1. What does a CASE expression return if no WHEN matches and ELSE is omitted?
Without a matching WHEN and no ELSE, CASE returns NULL.
2. Which WHEN clause result is returned by CASE?
CASE evaluates conditions in order and returns the value of the first WHEN that is true.
3. Which keyword must close a CASE expression?
Every CASE expression must be terminated with the END keyword.
Flash Cards
Two forms of CASE? — Simple CASE (compares one expression to values) and searched CASE (independent boolean WHEN conditions).
Which WHEN wins? — The first WHEN that evaluates to true; remaining WHENs are not used.
No match, no ELSE? — The CASE expression returns NULL.
Where can CASE appear? — In SELECT, WHERE, ORDER BY, GROUP BY, and inside aggregate functions.