SQL CASE WHEN Clause: Syntax and Practical Examples

SQL CASE WHEN evaluates conditions and returns the result associated with the first match, letting you add conditional logic to a query. Although it is often called a clause or statement, CASE in a query is an expression that returns a value. This guide covers both forms of CASE, a runnable example, filtering and aggregation, and the database-specific rules that prevent common errors.

How SQL CASE WHEN Works

A CASE expression contains possible matches, their results, and an optional fallback:

• WHEN supplies a condition or a value to match.
• THEN supplies the result for that match.
• ELSE supplies the result when nothing matches.
• END closes the expression.

The first matching branch determines the result. If nothing matches and you omit ELSE, the result is NULL.

In a SELECT list, CASE calculates an output value for each row. It does not change the stored data or remove rows from the result.

SQL Server allows CASE wherever a valid expression is accepted. That includes expressions in SELECT, WHERE, ORDER BY, and HAVING, as well as appropriate expression positions within UPDATE, DELETE, and SET statements. It does not mean you can put an entire SQL command after THEN.

There are two forms:

• Simple CASE compares one expression against a series of equality values.
• Searched CASE tests separate conditions, such as comparisons, ranges, and missing-value checks.

How to Write a SQL CASE WHEN Expression

What You Need to Do	Form to Use	What Goes After WHEN
Map individual codes or values to labels	Simple CASE	A value to compare with the input
Classify values into ranges	Searched CASE	A condition such as amount >= 1000
Test multiple columns or compound conditions	Searched CASE	A condition using comparisons, AND, or OR
Handle missing values explicitly	Searched CASE	An IS NULL condition

Step 1: Choose the CASE Syntax

1. Use simple CASE when every branch compares the same expression for equality.
2. Use searched CASE when branches need independent conditions.

The simple form begins with CASE input_expression. This is a syntax template; replace the placeholder names with your expressions and values:

CASE input_expression
    WHEN match_value_1 THEN result_1
    WHEN match_value_2 THEN result_2
    ELSE fallback_result
END

The searched form has no input expression between CASE and the first WHEN:

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE fallback_result
END

Both forms return values. Use commas between separate expressions in a SELECT list, not between the WHEN branches.

Step 2: Define the Conditions and Their Order

1. Decide how to handle missing input.
2. Arrange overlapping conditions so the intended match comes first.
3. Choose a result for every meaningful outcome.

For this walkthrough, classify an order’s amount using these example business rules:

• A missing amount gets the label Unknown.
• An amount of at least 1000 gets High.
• An amount of at least 500 gets Medium.
• Anything else gets Standard.

The high-value test must precede the medium-value test because an amount of 1200 satisfies both. SQL does not automatically choose the most specific branch.

Use IS NULL to test missing values. Neither an equality comparison to NULL nor a simple WHEN NULL branch matches a missing input under normal SQL NULL semantics.

Step 3: Run a Complete SELECT Example

1. Run this read-only query in SQL Server.
2. Inspect the calculated order_band column.

The common table expression, or CTE, supplies sample rows without creating or modifying a table:

WITH sample_orders AS (
    SELECT
        1 AS order_id,
        CAST(1200.00 AS DECIMAL(10, 2)) AS amount
    UNION ALL
    SELECT 2, 1000.00
    UNION ALL
    SELECT 3, 500.00
    UNION ALL
    SELECT 4, 499.99
    UNION ALL
    SELECT 5, NULL
)
SELECT
    order_id,
    amount,
    CASE
        WHEN amount IS NULL THEN 'Unknown'
        WHEN amount >= 1000 THEN 'High'
        WHEN amount >= 500 THEN 'Medium'
        ELSE 'Standard'
    END AS order_band
FROM sample_orders
ORDER BY order_id;

END AS order_band names the calculated column. The alias belongs after the complete expression, not inside a branch.

Step 4: Verify the Output and Apply It to Your Table

1. Compare the returned rows with the expected result.
2. Replace sample_orders with your actual table and adjust the column names.
3. Run the adapted query as a SELECT and compare the labels against your business rules.

The sample produces:

order_id	amount	order_band
1	1200.00	High
2	1000.00	High
3	500.00	Medium
4	499.99	Standard
5	NULL	Unknown

The CTE exists only for the statement that follows it. It does not leave behind a table, so there is nothing to clean up after running the example.

More SQL CASE WHEN Examples

The mapping, sorting, and aggregation examples assume an existing orders table with order_id , customer_type , amount , and status columns. Substitute your own table, column names, and business values.

Map Values With Simple CASE

Use simple CASE when you want readable labels for known values:

SELECT
    order_id,
    CASE customer_type
        WHEN 'Wholesale' THEN 'Business customer'
        WHEN 'Retail' THEN 'Individual customer'
        ELSE 'Unclassified'
    END AS customer_label
FROM orders;

This compares customer_type with each listed value. The fallback handles unrecognized values and missing customer types.

Avoid simple CASE for inequalities. A branch such as an amount being greater than a threshold needs the searched form.

Set a Custom Sort Order

A CASE expression in ORDER BY can assign a sort rank without adding that rank to the displayed columns:

SELECT
    order_id,
    status,
    amount
FROM orders
ORDER BY
    CASE status
        WHEN 'Pending' THEN 1
        WHEN 'Paid' THEN 2
        WHEN 'Canceled' THEN 3
        ELSE 4
    END,
    order_id;

This puts pending orders first, then paid orders, then canceled orders, followed by other statuses. The second sort key, order_id, makes the order predictable within each status group when the IDs are unique.

Calculate Conditional Totals and Counts

Put CASE inside an aggregate to include only certain values in that calculation:

SELECT
    customer_type,
    SUM(
        CASE
            WHEN status = 'Paid' THEN amount
            ELSE 0
        END
    ) AS paid_total,
    SUM(
        CASE
            WHEN status = 'Paid' THEN 1
            ELSE 0
        END
    ) AS paid_order_count
FROM orders
GROUP BY customer_type;

The total uses paid amounts and contributes zero for other statuses. The count adds one for each paid order and zero for each other order.

This leaves all input rows available to the grouping operation. That matters when the same query also needs totals for other statuses.

For conditional counting, this is another valid pattern:

SELECT
    COUNT(
        CASE
            WHEN status = 'Paid' THEN 1
        END
    ) AS paid_order_count
FROM orders;

COUNT(expression) ignores NULL, so unmatched rows are not counted. Do not add ELSE 0 to this particular pattern: zero is non-NULL, so COUNT would count unmatched rows too.

For conditional sums, also decide what a missing amount means. SUM ignores NULL values; if a paid order has a NULL amount, that amount does not contribute to paid_total.

Common CASE WHEN Errors and Database Differences

Incompatible Result Types

The database must determine one result type for the entire CASE expression. Mixing numeric values with text labels can cause conversion errors, even though each branch looks reasonable by itself.

For example, this query is unsafe in SQL Server:

SELECT
    CASE
        WHEN amount IS NULL THEN 'Unknown'
        ELSE amount
    END AS displayed_amount
FROM orders;

SQL Server’s data-type precedence can make it attempt to convert Unknown to a numeric type.

If the result is intended for display, convert the numeric branch explicitly:

SELECT
    CASE
        WHEN amount IS NULL THEN 'Unknown'
        ELSE CAST(amount AS VARCHAR(50))
    END AS displayed_amount
FROM orders;

If later calculations need a number, keep the numeric output separate from its descriptive label instead of converting it to text.

Deeply Nested Expressions

Nesting means placing one CASE inside another CASE, not simply adding more WHEN branches.

SQL Server permits 10 levels of nesting, per Microsoft’s CASE (Transact-SQL) documentation. If your logic would exceed that limit, flatten compatible conditions into one searched expression or move the rules into a lookup table and join to it.

Do not treat that limit as universal across database engines.

Every Result Is a Bare NULL

In SQL Server, a CASE expression whose result expressions are all explicit, bare NULL constants raises error 8133. At least one THEN or ELSE result expression must be something other than the bare NULL constant.

That is a restriction on the expressions, not a requirement that the query return a non-NULL value. If an intentionally all-NULL result is needed, a typed NULL satisfies the requirement:

SELECT
    CASE
        WHEN 1 = 1 THEN CAST(NULL AS VARCHAR(20))
        ELSE NULL
    END AS optional_label;

This returns a NULL value with a defined character type.

Assuming CASE Prevents Every Evaluation Error

The first matching branch determines the result, but that is not a blanket guarantee that every potentially dangerous expression elsewhere in the query will remain unevaluated.

In SQL Server, aggregate expressions used in WHEN arguments can be evaluated before the CASE expression receives their results. An aggregate containing division by zero can therefore fail before a branch appears to guard it.

Protect the dangerous operation itself. For division, NULLIF can turn a zero denominator into NULL:

SELECT
    100.0 / NULLIF(0, 0) AS safe_ratio;

This returns NULL rather than raising a divide-by-zero error. Use that behavior only when an undefined result should be represented as NULL; otherwise, define a different business rule for the zero-denominator case.

Confusing Query Expressions With Procedural Statements

A query CASE expression chooses a value; procedural CASE-statement rules are not interchangeable with query-expression rules.

In SQL Server, CASE cannot select which statement or statement block executes. Use the platform’s control-flow constructs when you need to choose between commands, rather than placing commands in the result branches.

Db2 also has a distinct procedural construct named the CASE statement. It selects an execution path inside procedural SQL, including supported procedure, function, and trigger contexts, and ends with END CASE.

In Db2 12.1, if no procedural WHEN condition matches and there is no ELSE, execution of that statement terminates with SQLSTATE 20000. Include an appropriate fallback execution path, and confirm version-specific behavior in the Db2 CASE statement documentation.

That procedural error rule does not replace the query-expression behavior in SQL Server.

How to Check Your CASE Results

Before treating a CASE expression as finished, test inputs that exercise the edges of your rules, not just ordinary rows.

Test branch coverage, boundaries, overlaps, missing and unexpected values, output types, and row or aggregate behavior:

• Every branch: Supply at least one input intended for each outcome.
• Boundaries: Test values exactly at a threshold and immediately on either side, using the precision of your column.
• Overlaps: Test an input that satisfies more than one condition.
• Missing and unexpected values: Include NULLs and values outside the recognized categories.
• Output types: Confirm that the result remains suitable for downstream calculations, comparisons, and exports.
• Row and aggregate behavior: Check whether you intended to label rows, exclude rows, or change contributions to an aggregate.

When debugging an existing query, temporarily include the raw input columns next to the calculated result. That makes a wrong classification easier to trace than inspecting the label alone.

Run the expression as a read-only SELECT against representative data, confirm the expected outcomes, and only then reuse it in a data-changing statement.

Frequently Asked Questions

Can We Use CASE WHEN in a WHERE Clause?

Yes: a CASE expression can supply a value that a WHERE predicate compares. For an orders table with amount and customer_type columns, this query selects a different minimum amount for wholesale customers:

SELECT
    order_id,
    customer_type,
    amount
FROM orders
WHERE amount >=
    CASE
        WHEN customer_type = 'Wholesale' THEN 1000
        ELSE 100
    END;

The CASE chooses the threshold; WHERE decides whether to keep the row. When you only need to combine filtering conditions, ordinary AND and OR predicates are often clearer than wrapping those conditions in CASE.

What Is the Difference Between WHERE and HAVING?

WHERE filters individual input rows before grouping, while HAVING filters groups after aggregation. Use WHERE for a condition on an individual order and HAVING for a condition on a grouped result, such as keeping only customer types with a positive paid total:

SELECT
    customer_type,
    SUM(
        CASE
            WHEN status = 'Paid' THEN amount
            ELSE 0
        END
    ) AS paid_total
FROM orders
GROUP BY customer_type
HAVING SUM(
    CASE
        WHEN status = 'Paid' THEN amount
        ELSE 0
    END
) > 0;

Can One CASE Expression Return Multiple Columns?

No: a SQL Server CASE expression returns one value, not multiple separate columns in the SELECT list. Use a separate CASE expression for each output column, even if those expressions test the same condition. Repeating a condition is not, by itself, evidence of a performance problem; inspect the execution plan before introducing a more complicated rewrite.