SQL CASE WHEN: adding conditional logic and categorising data

Data stored in a table often contains values different from what business-friendly categories or labels denote. Consider the same orders table:

Suppose we want to group orders into Small, Medium, or Large based on their amount. However, the table doesn’t have an order_size column. We can calculate each order’s amount using quantity * unit_price and assign a category based on the result. SQL’s CASE expression lets us do this.

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:

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'.

The CASE expression below uses the same category rules, but in a different order.

Consider an order worth 8500. It satisfies both conditions. Does the order of the conditions affect which category SQL assigns?

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 and WHERE both use conditions, but they solve different problems. Consider the query below:

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 the following query, where WHERE removes rows that do not satisfy the condition:

SELECT
    order_id,
    status
FROM orders
WHERE status = 'paid';

Raw database values are not always the values we want to display. Our status column contains paid, pending, and cancelled. Suppose the requirement is:

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.

A Shorter CASE Statement for Exact Matches

When you need to compare a column against specific values, SQL provides a shorter way to write CASE. Instead of repeating the column in every condition, you write it once after CASE.

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

This is called a simple CASE. It works well for matching exact values, such as mapping order statuses to labels.

For comparisons like amount >= 5000 or combined conditions like status = 'paid' AND discount_amount > 100, use a searched CASE, where each WHEN evaluates a complete condition.

CASE can use multiple conditions

A WHEN condition can contain the same logical expressions used in a WHERE clause. Suppose the requirement is:

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 follows:

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.

The ELSE clause defines what happens when none of the WHEN conditions matches.

For example, if a CASE expression checks only for paid and pending, an order with status = 'cancelled' matches neither condition. With ELSE 'Other', SQL assigns it the value Other.

The ELSE clause is optional. If you omit it and none of the conditions matches, SQL returns NULL.

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

Suppose the intended rules are:

Someone might write a version whose Medium branch is >= 2000 AND order_amount < 4000. What happens to order_amount = 4500? It does not match any condition, and without ELSE the result becomes NULL. The SQL is valid, but the category definitions contain a gap.

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.

Before writing a CASE expression, answer these four questions.

1. What value do we want to create?
We want to assign an order_size category to each order.

2. What are the possible categories?
Each order should be classified as Large, Medium, or Small.

3. What determines each category?
Use the order amount (quantity * unit_price) to decide:

• Large: Amount is 5,000 or more.
• Medium: Amount is at least 2,000 but less than 5,000.
• Small: Amount is below 2,000.

4. Can the conditions overlap?
Yes. An amount of 8500 satisfies both >= 5000 and >= 2000. Since CASE returns the result of the first matching condition, we must check the higher threshold first.

Remember: Define the categories first, check for overlapping conditions, and place them in the correct order.

Consider these order amounts:

-- 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

A CASE expression can run without errors and still produce incorrect results. Before trusting the output, watch for these common mistakes.

1. Incorrect condition order
When multiple conditions match, only the first matching WHEN determines the result. Make sure the order reflects the intended priority of your rules.

2. Gaps or overlaps in ranges
Check values at the boundaries of each range. For example, if Medium ends below 4,000 and Large starts at 5,000, what happens to an amount of 4,500?

3. Missing ELSE
Without an ELSE, unmatched rows return NULL. Decide whether missing values are acceptable or whether every row needs a default category.

4. Incorrect NULL comparisons
Comparisons like discount_amount = NULL do not evaluate to TRUE. Use IS NULL when checking for missing values.

5. Incompatible result types
Keep the values returned by THEN and ELSE compatible. Mixing text and numbers may cause type conversion errors.

Key Takeaways

• CASE vs. WHERE: CASE determines the value to return; WHERE determines which rows to include.
• First match wins: SQL evaluates WHEN conditions in order and uses the result of the first true condition.
• Two forms of CASE: Use simple CASE for exact value matches and searched CASE for ranges or more complex conditions.
• Defaults matter: Use ELSE when unmatched rows need a specific value. Otherwise, they return NULL.

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.

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