SQL CASE Expression

The CASE expression is SQL’s built-in conditional logic — an if/then/else you can drop directly inside a SELECT list, a WHERE clause, an ORDER BY clause, or almost anywhere else a single value is expected. Instead of pulling raw data out of the database and reshaping it in application code, you can translate codes into labels, bucket numbers into ranges, or pivot rows into columns right inside the query. Every major SQL dialect (SQLite, MySQL, PostgreSQL, SQL Server, Oracle) supports it with identical syntax, which makes it one of the most portable tools in the language.

Overview: How the CASE Expression Works

A CASE expression evaluates a list of conditions in order and returns the result associated with the first condition that is true. If none of the conditions match, it returns the value in the optional ELSE clause — or NULL if there is no ELSE at all. There are two forms:

Simple CASE compares one expression against a list of literal values, similar to a switch statement in a programming language. It can only test for equality.

Searched CASE evaluates a list of independent boolean conditions (using >, <, BETWEEN, LIKE, IN, IS NULL, and so on), so it is far more flexible and is the form you’ll use most often.

Under the hood, CASE is not a separate query step — it is evaluated as part of whichever clause it appears in, once the engine already has a row to work with. If it appears in the SELECT list, it runs during the final projection step, after FROM, JOIN, WHERE, GROUP BY, and HAVING have already produced the row set. If it appears inside an aggregate function like SUM(CASE ...), it is evaluated once per input row before aggregation — the aggregate then sums whatever values the CASE expressions produced. That’s what makes conditional aggregation (sometimes called “SUMIF” or a manual pivot) possible.

The engine evaluates WHEN clauses strictly top to bottom and stops at the first one that is true — this is short-circuit evaluation. Later conditions are never checked once a match is found, even if they would also be true. This matters both for correctness (overlapping conditions must be ordered from most specific to least specific) and occasionally for safety (a condition that could error, such as a division, can be guarded by an earlier check).

Syntax

-- Simple CASE: compares one expression against literal values
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

-- Searched CASE: evaluates independent boolean conditions
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Part	Meaning
CASE	Begins the expression.
expression	(Simple form only) The column or expression being compared for equality against each value.
WHEN condition	(Searched form) Any boolean expression — comparisons, BETWEEN, IN, LIKE, IS NULL, etc.
THEN result	The value returned when the matching WHEN is reached. Can be a literal, column, or expression.
ELSE default_result	Optional. Returned when no WHEN matches. Omitting it means NULL is returned instead.
END	Closes the expression. Required — leaving it off is a syntax error.

Examples

Example 1: Translating status codes with a simple CASE

Orders are often stored with short status codes. A simple CASE turns them into readable labels without changing anything in the table:

SELECT
    order_id,
    status,
    CASE status
        WHEN 'P' THEN 'Processing'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM orders_demo
ORDER BY order_id;

Result:

order_id	status	status_label
1	P	Processing
2	S	Shipped
3	D	Delivered
4	C	Cancelled
5	P	Processing

Each row’s status is compared for equality against 'P', 'S', 'D', and 'C' in order. Since every code in this sample matches one of the four, ELSE 'Unknown' is never actually reached — but it’s there as a safety net for any status value the table might contain in the future.

Example 2: Bucketing numbers with a searched CASE

Simple CASE can’t test ranges, so bucketing a numeric column into bands requires the searched form:

SELECT
    name,
    department,
    salary,
    CASE
        WHEN salary >= 90000 THEN 'High'
        WHEN salary >= 60000 THEN 'Mid'
        ELSE 'Entry'
    END AS salary_band
FROM employees_demo
ORDER BY salary DESC;

Result:

name	department	salary	salary_band
Alice	Engineering	95000	High
Carol	Engineering	78000	Mid
David	Marketing	61000	Mid
Bob	Sales	52000	Entry
Eve	Sales	45000	Entry

Order matters here: the conditions are checked top to bottom, so salary >= 90000 is tested first. A salary of 95000 would also satisfy salary >= 60000, but because the first condition already matched, the engine never even looks at the second one — that’s short-circuit evaluation in action. If the two conditions were reversed, every employee earning 90000 or more would incorrectly land in the “Mid” bucket.

Example 3: Conditional aggregation (a manual pivot)

Wrapping CASE inside an aggregate function is one of the most useful patterns in SQL — it lets you turn row values into separate columns:

SELECT
    customer_name,
    SUM(CASE WHEN status = 'Delivered' THEN amount ELSE 0 END) AS delivered_total,
    SUM(CASE WHEN status = 'Cancelled' THEN amount ELSE 0 END) AS cancelled_total
FROM orders_demo2
GROUP BY customer_name
ORDER BY customer_name;

Result:

customer_name	delivered_total	cancelled_total
Nina	180	45
Omar	380	25

For every row, the two CASE expressions run first and reduce amount to either its own value or 0, depending on status. GROUP BY then buckets rows by customer_name, and SUM() adds up whatever the CASE expressions produced for each bucket. The result is a status-by-customer breakdown in a single query, with no self-joins or subqueries required.

How CASE Fits Into Query Processing Order

SQL clauses aren’t executed in the order you type them — they run logically as FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Where a CASE expression sits in your query determines which stage evaluates it:

• In the SELECT list, it’s evaluated during the final projection, once per output row.
• Inside an aggregate (SUM, COUNT, AVG…), it’s evaluated once per input row, before the values are combined per group.
• In WHERE or HAVING, it’s evaluated as part of filtering — though most of the time an equivalent AND/OR expression is clearer and just as correct.
• In ORDER BY, it can compute a custom sort key on the fly (for example, sorting a status column in a specific business-defined order rather than alphabetically).

Because a column alias defined in the SELECT list with CASE ... END AS alias doesn’t exist yet during WHERE or GROUP BY (those run first), you cannot reference that alias there in standard SQL — you must repeat the full CASE expression, or wrap the query in a subquery/CTE and filter on the alias in the outer query.

Common Mistakes

Mistake 1: Using a comparison operator inside a simple CASE

Simple CASE only tests equality — you cannot put an operator after WHEN:

CREATE TABLE employees_bad (
    name TEXT,
    salary REAL
);

INSERT INTO employees_bad (name, salary) VALUES ('Alice', 95000), ('Bob', 45000);

SELECT
    name,
    CASE salary
        WHEN > 50000 THEN 'High'
        ELSE 'Low'
    END AS salary_band
FROM employees_bad;

This raises a syntax error, because WHEN > 50000 is not a valid value to compare salary against — the simple form expects a literal or expression, not an operator. The fix is to switch to the searched form, which drops the expression after CASE and puts a full boolean condition after each WHEN:

SELECT
    name,
    CASE
        WHEN salary > 50000 THEN 'High'
        ELSE 'Low'
    END AS salary_band
FROM employees_bad;

Mistake 2: Comparing to NULL with = instead of IS NULL

This one is worse because it doesn’t error — it just silently returns the wrong answer:

CREATE TABLE employees_null (
    name TEXT,
    department TEXT
);

INSERT INTO employees_null (name, department) VALUES
    ('Alice', 'Engineering'),
    ('Bob', NULL),
    ('Carol', 'Sales');

SELECT
    name,
    CASE
        WHEN department = NULL THEN 'No Department'
        ELSE department
    END AS dept_label
FROM employees_null
ORDER BY name;

In SQL, NULL represents “unknown,” so department = NULL never evaluates to true — not even when department is actually NULL. Bob’s row falls through to ELSE department, which is itself NULL, so the query returns NULL instead of the intended label “No Department”. The fix is to test for NULL explicitly with IS NULL:

SELECT
    name,
    CASE
        WHEN department IS NULL THEN 'No Department'
        ELSE department
    END AS dept_label
FROM employees_null
ORDER BY name;

With IS NULL, Bob correctly receives the label “No Department” while Alice and Carol keep their real department names.

Best Practices

• Always include an ELSE clause, even if it’s just ELSE NULL written explicitly — it documents that the “no match” case was considered on purpose, rather than leaving readers to wonder if it was forgotten.
• Order WHEN conditions from most specific to least specific in a searched CASE, since the first true condition wins and later ones are skipped.
• Use IS NULL / IS NOT NULL to test for NULL, never = NULL or != NULL.
• Make sure every THEN and ELSE branch returns a compatible type (all text, or all numbers) — mixing types works in SQLite due to its flexible typing, but produces inconsistent, error-prone results in stricter databases like PostgreSQL or SQL Server.
• Prefer the simple form when you’re only checking equality against a single column — it’s shorter and communicates intent. Reach for the searched form as soon as you need ranges, NULL checks, or conditions spanning multiple columns.
• Use CASE inside aggregate functions for conditional counts and sums instead of writing several near-duplicate queries with different WHERE clauses and joining the results together.
• Give the result column a clear alias with AS — a bare CASE ... END produces an unnamed, hard-to-reference column.

Practice Exercises

1. Using the employees_demo shape from Example 2 (columns name, department, salary), write a searched CASE that labels each employee 'Manager Track' if their salary is at least 80000, 'Senior' if it’s at least 60000, and 'Junior' otherwise.
2. Using the orders_demo shape from Example 1 (columns order_id, status), write a query that counts how many orders have status 'D' versus all other statuses combined, using SUM(CASE ...) rather than two separate queries. Hint: you’ll need two SUM(CASE WHEN ... THEN 1 ELSE 0 END) expressions.
3. Take the “Mistake 2” table (employees_null) and write a query that adds a column labeling each row 'Missing' when department is NULL and 'Assigned' otherwise — without using the buggy = NULL comparison.

Summary

• CASE is SQL’s conditional expression, usable almost anywhere a value is expected — most commonly in SELECT, but also in WHERE, ORDER BY, and inside aggregate functions.
• Simple CASE (CASE expression WHEN value THEN ...) only tests equality; searched CASE (CASE WHEN condition THEN ...) supports any boolean condition and is more flexible.
• WHEN clauses are evaluated top to bottom, and the first true one wins — later conditions are skipped, so ordering matters.
• Without an ELSE, a CASE with no matching WHEN returns NULL instead of a default value.
• Always test for NULL with IS NULL, never = NULL, since NULL comparisons never evaluate to true.
• Wrapping CASE inside SUM() or COUNT() is the standard way to do conditional aggregation — computing multiple category totals in a single pass over the data.