Not every question in SQL is a straight column read. Sometimes you need to translate a number into a label, turn a status into a priority, or bucket salaries into bands before you count them. The tool for this is the CASE expression — SQL's way of saying 'if this, then that, otherwise something else.' It runs once per row, evaluates conditions in order, and returns a single value that you can select, filter, sort, or feed into an aggregate. Every major database supports it with the same syntax, so learning it once pays off across PostgreSQL, MySQL, SQL Server, and SQLite. In this lesson you will use it to categorise employees, custom-sort project statuses, and count conditionally — three patterns that appear in almost every real report.
flowchart LR
R["Row arrives"] --> C{"salary > 700000?"}
C -->|yes| H["#quot;High#quot;"]
C -->|no| L["#quot;Standard#quot;"]
style H fill:#0e7490,color:#fff
style L fill:#1e293b,color:#fff
The searched CASE form
There are two shapes, but the one you will use almost every time is the searched CASE — a list of WHEN/THEN pairs followed by an ELSE and terminated with END:
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
The database checks each WHEN from top to bottom. The first true condition wins; everything after it is skipped. If none match, ELSE provides the fallback. Omit ELSE and you get NULL — a silent surprise that can vanish from counts and averages without warning.
CASE is an expression, not a statement. That means it returns a value and can sit anywhere a value is legal: in the SELECT list, inside a WHERE clause, in ORDER BY, or nested inside SUM and COUNT. Because it runs during the SELECT stage, it sees the rows that have already survived any WHERE filter.
SELECT name, salary,
CASE
WHEN salary >= 800000 THEN 'Senior'
WHEN salary >= 600000 THEN 'Mid'
ELSE 'Junior'
END AS tier
FROM employees;
CASE inside aggregates
Placing CASE inside an aggregate is one of the most powerful patterns in SQL. It lets you count or sum only the rows that meet a condition, turning a vertical list into a horizontal summary.
COUNT(CASE WHEN status = 'active' THEN 1 END)
This counts only active projects. The CASE returns 1 for active rows and NULL for everything else; COUNT ignores NULLs, so only the active ones are tallied. You can repeat the pattern for each category you need, producing a pivot-style report in a single query.
The result is a cross-tabulation: one row of summary numbers, each derived from a different slice of the underlying data. This is how analysts build dashboard metrics directly in SQL without exporting to a spreadsheet. The key is remembering that COUNT counts non-NULL values, not 'true' values — so the ELSE clause matters more than it looks.
flowchart TD A["Row: status = #quot;active#quot;"] --> B["CASE returns 1"] B --> C["COUNT adds 1"] D["Row: status = #quot;completed#quot;"] --> E["CASE returns NULL"] E --> F["COUNT ignores NULL"] style C fill:#0e7490,color:#fff style F fill:#b45309,color:#fff
SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count, COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count, COUNT(CASE WHEN status = 'on hold' THEN 1 END) AS on_hold_count FROM projects;
CASE in ORDER BY
Because CASE returns a value, you can use it to define a custom sort that is not alphabetical or numeric. Suppose you want active projects first, then completed, then on hold. The alphabetical order of those words happens to match, but a more realistic example is ordering roles by seniority: Engineering Lead, then Senior Engineer, then Engineer.
The trick is to assign each category a numeric rank inside CASE, then sort by that rank. The learner sees the order they expect, while the database sees a simple integer sort. You can also use CASE to push NULLs to the end or top regardless of the default sort order, which varies between database engines. This technique is portable across every SQL dialect, unlike vendor-specific NULLS FIRST / LAST syntax.
flowchart LR U["Unsorted rows"] --> C["CASE assigns rank<br/>Lead = 1, Senior = 2"] C --> O["ORDER BY rank ASC"] O --> R["Sorted result"] style R fill:#0e7490,color:#fff
SELECT name, role
FROM employees
ORDER BY
CASE role
WHEN 'Engineer' THEN 1
WHEN 'Senior Engineer' THEN 2
WHEN 'Engineering Lead' THEN 3
ELSE 4
END;
CASE and WHERE: different stages
A common mistake is trying to filter rows with CASE inside a WHERE clause. CASE runs during SELECT, after the database has already decided which rows to keep. You cannot write WHERE CASE WHEN salary > 700000 THEN 'High' END = 'High' to filter high earners — well, you can, but it is slower and harder to read than a plain WHERE salary > 700000.
Use CASE to transform values for display or aggregation; use WHERE to filter rows before those transformations happen. Mixing the two jobs in one query is a sign that you are fighting the engine instead of working with it.
Practice: from labels to logic
The exercises below start with a straightforward label, move into conditional counting, then force you to repair a subtle aggregate bug. The final question asks you to control sort order with CASE — a transfer task that combines everything you have seen. Each query runs against the same company database, so the results are always real and immediately visible. Take your time with the second and third questions; they are the ones that separate a working query from a correct one.
Add a level column that reads 'High' when salary is at least 700000, 'Medium' when it is at least 500000, and 'Low' for everything else. Return name and level.
SELECT name, -- write your CASE here AS level FROM employees;
Use searched CASE: CASE WHEN salary >= 700000 THEN 'High' ... END
The ELSE handles everything below 500000.
SELECT name, CASE WHEN salary >= 700000 THEN 'High' WHEN salary >= 500000 THEN 'Medium' ELSE 'Low' END AS level FROM employees;
Count how many employees fall into each salary level from the previous exercise. Return two columns: level and headcount, with levels ordered High, Medium, Low.
SELECT
CASE
WHEN salary >= 700000 THEN 'High'
WHEN salary >= 500000 THEN 'Medium'
ELSE 'Low'
END AS level,
COUNT(*) AS headcount
FROM employees
GROUP BY level
-- add ORDER BY so High appears first, then Medium, then Low
;
GROUP BY the CASE expression (or its alias) to bucket the counts.
Use a second CASE in ORDER BY to enforce High=1, Medium=2, Low=3.
SELECT CASE WHEN salary >= 700000 THEN 'High' WHEN salary >= 500000 THEN 'Medium' ELSE 'Low' END AS level, COUNT(*) AS headcount FROM employees GROUP BY level ORDER BY CASE level WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END;
This query is meant to count how many employees earn at least 600000. Because of the ELSE 0, it counts every row instead of just the high earners. Fix the CASE so only matching rows are counted.
SELECT COUNT(CASE WHEN salary >= 600000 THEN 1 ELSE 0 END) AS high_earners FROM employees;
COUNT only ignores NULLs, not zeros.
Remove the ELSE so unmatched rows become NULL.
SELECT COUNT(CASE WHEN salary >= 600000 THEN 1 END) AS high_earners FROM employees;
List the name and role of every employee in the Engineering department, sorted by seniority with Engineering Lead first, then Senior Engineer, then Engineer. Use a CASE expression in ORDER BY.
SELECT e.name, e.role FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering' -- add ORDER BY with CASE here ;
Use CASE e.role WHEN 'Engineering Lead' THEN 1 ... in the ORDER BY clause.
The join and filter are already written; you only need to add the sort.
SELECT e.name, e.role FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering' ORDER BY CASE e.role WHEN 'Engineering Lead' THEN 1 WHEN 'Senior Engineer' THEN 2 WHEN 'Engineer' THEN 3 ELSE 4 END;
What happens if no WHEN condition in a CASE expression is true and there is no ELSE clause?
Without an ELSE, CASE returns NULL for every row that misses all WHEN conditions. That is harmless in a SELECT list but dangerous inside COUNT or AVG, because NULLs are ignored by aggregates and can silently skew your results.
Recap
- CASE is an expression that returns a value; it can live in SELECT, WHERE, ORDER BY, or inside aggregates.
- The searched form checks conditions top-to-bottom and returns the first match.
- Omitting ELSE yields NULL, which is ignored by aggregates and can silently skew results.
COUNT(CASE WHEN ... THEN 1 END)is the idiom for conditional counting; never use ELSE 0 inside COUNT.- Use CASE in ORDER BY to create custom sorts that are not tied to alphabetical or numeric order.
Next you will manipulate text and dates directly — trimming names, extracting years from hire dates, and formatting results without leaving the database.