SQL CASE WHEN: adding conditional logic and categorising data

Creating values from conditions

Data stored in a table often contains raw values, while the result we need contains business-friendly categories or labels. Consider the same orders table:

Suppose the business wants orders to be classified as Small, Medium, or Large based on their order amount. The table does not contain an order_size column. We can calculate the order amount quantity * unit_price and then use SQL to assign a category based on that value. This is what CASE is designed for.

CASE WHEN

A CASE expression evaluates conditions and returns a value based on which condition matches. A common structure is:

CASE
    WHEN condition_1 THEN value_1
    WHEN condition_2 THEN value_2
    ELSE default_value
END

You can read this as: if condition 1 is true, return value 1; otherwise, if condition 2 is true, return value 2; otherwise, return the default value. Consider this requirement: label orders worth 5,000 or more as Large, orders worth 2,000 or more as Medium, and everything else as Small.

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount,
    CASE
        WHEN quantity * unit_price >= 5000 THEN 'Large'
        WHEN quantity * unit_price >= 2000 THEN 'Medium'
        ELSE 'Small'
    END AS order_size
FROM orders
ORDER BY order_id;

The values Large, Medium, and Small do not need to exist in the original table. CASE creates them in the query result.

How CASE evaluates conditions

Consider order 1004, whose amount is 8500. SQL evaluates WHEN order_amount >= 5000. The condition is TRUE, so SQL returns Large and stops evaluating additional WHEN conditions for that CASE. Now consider order 1002, amount 3200: the first condition 3200 >= 5000 is false, so SQL moves to 3200 >= 2000, which is true, and the result is Medium. Finally, order 1005 has amount 750: neither condition matches, so SQL reaches ELSE 'Small'.

When the conditions are in the wrong order

Suppose someone writes the same categorisation like this. At first glance, both rules appear to be present. But consider the order worth 8500.

CASE
    WHEN quantity * unit_price >= 2000 THEN 'Medium'
    WHEN quantity * unit_price >= 5000 THEN 'Large'
    ELSE 'Small'
END

The fix: put more specific conditions first

For overlapping ranges, check the more restrictive condition first:

CASE
    WHEN quantity * unit_price >= 5000 THEN 'Large'
    WHEN quantity * unit_price >= 2000 THEN 'Medium'
    ELSE 'Small'
END

Now an 8500 order matches >= 5000 first and becomes Large. A 3200 order fails the first condition and matches >= 2000, so it becomes Medium.

CASE does not filter rows

CASE and WHERE both use conditions, but they solve different problems. Consider:

SELECT
    order_id,
    status,
    CASE
        WHEN status = 'paid' THEN 'Completed'
        ELSE 'Not completed'
    END AS payment_state
FROM orders;

Every order is still returned; CASE changes the value produced for each row. Compare that with SELECT order_id, status FROM orders WHERE status = 'paid';, where WHERE removes rows that do not satisfy the condition.

Business-friendly labels

Raw database values are not always the values we want to display. Our status column contains paid, pending, and cancelled. Suppose a report should show Completed, Awaiting payment, and Cancelled:

SELECT
    order_id,
    customer,
    status,
    CASE
        WHEN status = 'paid' THEN 'Completed'
        WHEN status = 'pending' THEN 'Awaiting payment'
        WHEN status = 'cancelled' THEN 'Cancelled'
        ELSE 'Other'
    END AS status_label
FROM orders
ORDER BY order_id;

The stored value remains unchanged. CASE only creates a different representation in this query result.

The simple CASE form

When all conditions compare one expression with exact values, SQL also supports a shorter form:

CASE status
    WHEN 'paid' THEN 'Completed'
    WHEN 'pending' THEN 'Awaiting payment'
    WHEN 'cancelled' THEN 'Cancelled'
    ELSE 'Other'
END

This works well for direct equality mappings. But it is not suitable for rules such as amount >= 5000 or status = 'paid' AND discount_amount > 100. For those cases, use the more flexible searched form with a full condition after each WHEN.

CASE can use multiple conditions

A WHEN condition can contain the same logical expressions used in a WHERE clause. Suppose the business wants to identify high-value paid orders: the order must be paid and worth at least 3,000.

SELECT
    order_id,
    customer,
    status,
    quantity * unit_price AS order_amount,
    CASE
        WHEN status = 'paid'
         AND quantity * unit_price >= 3000
            THEN 'High-value paid order'
        ELSE 'Other'
    END AS order_type
FROM orders
ORDER BY order_id;

Order 1002 is worth more than 3,000, but it is pending. Order 1004 is worth 8,500, but it is cancelled. Both conditions must be true.

Using CASE for multiple categories

Business categorisation often contains more than two outcomes. Suppose we define order priority as: large paid order → Priority 1, other paid order → Priority 2, pending order → Priority 3, everything else → Review.

CASE
    WHEN status = 'paid'
     AND quantity * unit_price >= 5000
        THEN 'Priority 1'
    WHEN status = 'paid'
        THEN 'Priority 2'
    WHEN status = 'pending'
        THEN 'Priority 3'
    ELSE 'Review'
END AS priority

Notice the order of conditions. The more specific rule (status = 'paid' AND order_amount >= 5000) comes before the broader rule (status = 'paid'). Otherwise, every paid order would match the broader condition first, and no paid order could ever become Priority 1.

ELSE, NULL, gaps, and types

The ELSE value handles rows that match none of the previous conditions. For status = cancelled in a CASE that only tests paid and pending, neither WHEN matches, so the result is Other. This provides an explicit fallback.

CASE and NULL

The conditions inside CASE follow the same NULL rules covered earlier. This does not correctly detect missing values, because discount_amount = NULL evaluates to UNKNOWN, not TRUE:

CASE
    WHEN discount_amount = NULL THEN 'No discount'
    ELSE 'Discount recorded'
END

Use IS NULL:

CASE
    WHEN discount_amount IS NULL THEN 'No discount recorded'
    ELSE 'Discount recorded'
END

The same three-valued logic rules apply whether the condition appears inside WHERE or CASE.

Gotcha: categories can have gaps

CASE result values should be compatible

Consider a CASE where one branch returns text ('Completed') and another returns a number (0). Database systems need to determine a common result type for a CASE expression, and mixing unrelated types can produce conversion problems or errors depending on the database and values involved. Prefer branches with compatible meanings and data types: all text, or all numbers. A CASE expression represents one output column, so its possible results should belong naturally to the same kind of value.

When CASE may be useful, and where it goes wrong

Look for these situations when CASE may be useful:

Also look for these common mistakes:

Turn a business rule into CASE with four questions

CASE
    WHEN quantity * unit_price >= 5000 THEN 'Large'
    WHEN quantity * unit_price >= 2000 THEN 'Medium'
    ELSE 'Small'
END

One more prediction

Consider these order amounts. The business rule is: 5000 or more → Large, 2000 or more → Medium, below 2000 → Small. Which expression is correct?

-- Option A
CASE
    WHEN order_amount >= 2000 THEN 'Medium'
    WHEN order_amount >= 5000 THEN 'Large'
    ELSE 'Small'
END

-- Option B
CASE
    WHEN order_amount >= 5000 THEN 'Large'
    WHEN order_amount >= 2000 THEN 'Medium'
    ELSE 'Small'
END

-- Option C
CASE
    WHEN order_amount < 2000 THEN 'Small'
    WHEN order_amount >= 5000 THEN 'Large'
END

-- Option D
CASE
    WHEN order_amount = 5000 THEN 'Large'
    WHEN order_amount = 2000 THEN 'Medium'
    ELSE 'Small'
END

Lock these in

Summary

CASE allows SQL to create values based on conditions. It can turn raw values into business-friendly labels, create categories from numeric ranges, and combine several conditions. The most important rule is that CASE returns the result from the first WHEN condition that evaluates to TRUE. Because of that, condition order matters whenever rules overlap.

Next, we will use CASE together with aggregate functions to calculate multiple business metrics from the same set of rows using conditional aggregation.