CASE Expressions in SELECT
Writing inline conditional logic and the searched vs simple CASE forms.
CASE Expressions in SELECT is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
CASE: SQL's Conditional Expression
CASE is SQL's if/then/else, but it's an expression that returns one value — so it works anywhere a value can: SELECT, WHERE, ORDER BY, even inside aggregates.
SELECT order_id,
amount,
CASE WHEN amount > 100 THEN 'large' ELSE 'small' END AS size
FROM orders;Searched CASE Form
The searched CASE checks a boolean per WHEN and returns the first match. It's the most flexible form, and order matters — the first true condition wins.
SELECT score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM exam_results;Simple CASE Form
The simple CASE compares one expression against a list of values. It's shorter for equality checks, but it can't do ranges or NULL tests.
SELECT status_code,
CASE status_code
WHEN 1 THEN 'active'
WHEN 2 THEN 'paused'
WHEN 3 THEN 'closed'
ELSE 'unknown'
END AS status_label
FROM accounts;Searched vs Simple: When to Use Each
Use simple CASE for equality against fixed values, and searched CASE for ranges or NULL. Key trap: simple CASE can never match NULL — use IS NULL in a searched form.
-- Simple CASE will NOT catch NULL here
SELECT CASE WHEN bonus IS NULL THEN 'no bonus'
ELSE 'has bonus'
END AS bonus_flag
FROM employees;Order of WHEN Branches Matters
Since the first matching branch wins, order your WHENs from most specific to least. Put a loose threshold first and a score of 95 gets mislabeled.
-- Correct order: highest threshold first
SELECT CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C or below'
END AS grade
FROM exam_results;The ELSE and the Implicit NULL
If nothing matches and you skip ELSE, CASE returns NULL. Add an explicit ELSE for your default so unexpected values don't silently become NULL.
-- No ELSE: anything not 1/2 becomes NULL
SELECT CASE flag WHEN 1 THEN 'yes' WHEN 2 THEN 'no' END AS label
FROM toggles;Conditional Aggregation (Pivot Pattern)
The most valuable interview trick: conditional aggregation. Put CASE inside SUM or COUNT to tally rows per category in one pass — a tidy pivot-style report.
SELECT
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue
FROM orders;COUNT Ignores NULL: A CASE Shortcut
Since COUNT ignores NULLs, you can drop the ELSE so non-matching rows become NULL and aren't counted. It's a slightly shorter way to do conditional counting.
SELECT
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders
FROM orders;CASE in ORDER BY for Custom Sorting
Put CASE in ORDER BY to sort by custom priority instead of plain alphabetical or numeric order — like urgent before normal before everything else.
SELECT order_id, status
FROM orders
ORDER BY
CASE status
WHEN 'urgent' THEN 1
WHEN 'normal' THEN 2
ELSE 3
END,
order_id;Type Consistency Across Branches
All branches of a CASE must return a compatible type. Mixing a number and a string can error or force a cast — pick one output type and cast explicitly if needed.
-- Keep branches the same type
SELECT CASE WHEN qty > 0 THEN CAST(qty AS VARCHAR)
ELSE 'none'
END AS qty_label
FROM inventory;CASE in WHERE for Conditional Filtering
CASE in WHERE lets you apply different filter logic based on a flag — like a different price threshold per region. Often cleaner than a tangle of ANDs and ORs.
SELECT order_id, amount, region
FROM orders
WHERE amount > CASE region
WHEN 'US' THEN 100
WHEN 'EU' THEN 90
ELSE 50
END;Quick Check
Predict the result.
Recap
Recap: CASE returns one value and works in SELECT, WHERE, ORDER BY, and aggregates. Searched handles ranges and NULL; first match wins; no match means NULL; keep branch types compatible.
Frequently asked questions
Is the “CASE Expressions in SELECT” lesson free?
Yes — the full text of “CASE Expressions in SELECT” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “CASE Expressions in SELECT”?
Writing inline conditional logic and the searched vs simple CASE forms. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “CASE Expressions in SELECT” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.