SQL CASE Statement Explained

The SQL CASE statement lets you add if/else-style logic directly inside a query — most commonly in a SELECT list, to turn raw values into readable labels, but it also works in WHERE, ORDER BY, and GROUP BY. It's the closest thing SQL has to a conditional expression.

There are two forms: simple CASE, which compares one expression against a list of exact values, and searched CASE, which evaluates a list of independent boolean conditions. Searched CASE is the one you'll use most often, since it can express any condition, not just equality.

Searched CASE: Categorizing Salary Bands

SELECT
    name,
    salary,
    CASE
        WHEN salary < 50000 THEN 'Low'
        WHEN salary < 80000 THEN 'Medium'
        ELSE 'High'
    END AS salary_band
FROM employees;

Result:

name | salary | salary_band
--------|--------|------------
Diego | 45000 | Low
Priya | 61000 | Medium
Bob | 75000 | Medium
Alice | 82000 | High

SQL checks the WHEN conditions top to bottom and stops at the first one that's true, which is why the ranges don't need to repeat the lower bound — once a row fails the salary < 50000 check, the next condition only needs to test the upper bound. ELSE is the fallback for anything that didn't match any condition above it; without it, non-matching rows would just return NULL instead of a value.

Simple CASE: Matching Exact Values

When you're comparing one column against a fixed list of exact values (rather than ranges or more complex conditions), simple CASE is a shorter way to write it:

SELECT
    name,
    department,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales'       THEN 'Revenue'
        ELSE 'Other'
    END AS division
FROM employees;

Result:

name | department | division
--------|-------------|----------
Alice | Engineering | Tech
Priya | Sales | Revenue
Diego | Support | Other

This is functionally identical to writing CASE WHEN department = 'Engineering' THEN ... as a searched CASE — simple CASE just saves you from repeating the column name in every branch. The moment you need a comparison other than equality (ranges, IS NULL, multiple columns), you're back to searched CASE.

CASE Inside Other Clauses

Because CASE returns a value, it works anywhere an expression is allowed — not just in SELECT. A common pattern is using it inside an aggregate to count rows that match a condition, similar to a conditional COUNT:

SELECT
    department,
    COUNT(CASE WHEN salary >= 70000 THEN 1 END) AS high_earners
FROM employees
GROUP BY department;

Result:

department | high_earners
-------------|-------------
Engineering | 2
Sales | 1

Here CASE returns 1 for matching rows and implicitly NULL for everything else — and COUNT() only counts non-NULL values, so this neatly counts just the high earners per department group without needing a separate filtered query.