SQL CASE WHEN: Complete Guide with Examples (2026)

What Is SQL CASE WHEN?

SQL CASE WHEN is a conditional expression that returns different values depending on which condition is true. It works like an if-then-else block in programming languages, but inside a SQL query. You can use it in SELECT, WHERE, ORDER BY, GROUP BY, and inside aggregate functions.

Every time SQL evaluates a CASE expression, it checks each WHEN condition from top to bottom and returns the result of the first condition that is true. If none match and you have an ELSE clause, it returns the ELSE value. If none match and there is no ELSE, it returns NULL.

This makes CASE WHEN one of the most versatile tools in SQL. It lets you apply logic inside a query rather than post-processing results in your application code.

Here is the sample table used throughout this guide:

CREATE TABLE orders (
    order_id   INT PRIMARY KEY,
    customer   VARCHAR(50),
    product    VARCHAR(50),
    amount     DECIMAL(10,2),
    status     VARCHAR(20),
    order_date DATE
);

INSERT INTO orders VALUES
(1,  'Alice',   'Laptop',   1200.00, 'paid',      '2026-01-05'),
(2,  'Bob',     'Mouse',      25.00, 'pending',   '2026-01-07'),
(3,  'Charlie', 'Monitor',   450.00, 'paid',      '2026-01-10'),
(4,  'Diana',   'Keyboard',   80.00, 'refunded',  '2026-01-12'),
(5,  'Eve',     'Laptop',   1150.00, 'paid',      '2026-02-01'),
(6,  'Frank',   'Webcam',     60.00, 'pending',   '2026-02-03'),
(7,  'Grace',   'Monitor',   480.00, 'paid',      '2026-02-15'),
(8,  'Hank',    'Mouse',      30.00, 'refunded',  '2026-02-20'),
(9,  'Ivy',     'Laptop',   1300.00, 'pending',   '2026-03-01'),
(10, 'Jack',    'Keyboard',   75.00, NULL,         '2026-03-05');

Basic Syntax

There are two forms of CASE WHEN in SQL: searched CASE and simple CASE. Both end with END and optionally include an ELSE clause.

Searched CASE (most common)

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Each WHEN clause contains a full boolean expression. This is the most flexible form because you can compare different columns, use ranges, and mix operators.

Simple CASE

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Simple CASE compares a single expression against a list of values. It can only check equality — you cannot use >, <, BETWEEN, or IS NULL in a simple CASE. Use searched CASE whenever you need anything beyond equality checks.

Simple CASE vs Searched CASE

Both forms produce the same output when you are checking equality, but searched CASE is strictly more powerful. Here is the same logic written both ways:

-- Simple CASE: equality only
SELECT
    order_id,
    status,
    CASE status
        WHEN 'paid'     THEN 'Completed'
        WHEN 'pending'  THEN 'In Progress'
        WHEN 'refunded' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

-- Searched CASE: full boolean conditions
SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 1000 THEN 'Large'
        WHEN amount >= 100  THEN 'Medium'
        ELSE 'Small'
    END AS order_size
FROM orders;

The second query uses range comparisons (>=) which are impossible in simple CASE. In practice, most developers default to searched CASE for consistency.

Evaluation order matters. CASE stops at the first matching condition. In the amount example above, a $1,200 order matches amount >= 1000 first and returns 'Large' — even though it also satisfies amount >= 100. Always put your most specific conditions first.

CASE WHEN with Multiple Conditions

You can combine multiple logical operators inside a single WHEN clause using AND, OR, and NOT. You can also chain as many WHEN clauses as you need.

-- Multiple conditions in one WHEN clause
SELECT
    order_id,
    customer,
    amount,
    status,
    CASE
        WHEN status = 'paid' AND amount >= 1000 THEN 'High-Value Paid'
        WHEN status = 'paid' AND amount < 1000  THEN 'Low-Value Paid'
        WHEN status = 'pending'                 THEN 'Awaiting Payment'
        WHEN status = 'refunded'                THEN 'Returned'
        ELSE 'Unknown'
    END AS order_category
FROM orders;

Result

order_id	customer	amount	order_category
1	Alice	1200.00	High-Value Paid
2	Bob	25.00	Awaiting Payment
3	Charlie	450.00	Low-Value Paid
4	Diana	80.00	Returned
5	Eve	1150.00	High-Value Paid
10	Jack	75.00	Unknown

Jack's order has a NULL status, so none of the WHEN conditions match and it falls through to the ELSE clause.

CASE WHEN in SELECT

The most common place to use CASE WHEN is inside the SELECT list to create computed columns. This is useful for labeling, bucketing values, and transforming data for display without changing the underlying table.

-- Assign a discount tier based on order amount
SELECT
    order_id,
    customer,
    amount,
    CASE
        WHEN amount >= 1000 THEN 0.10
        WHEN amount >= 500  THEN 0.05
        WHEN amount >= 100  THEN 0.02
        ELSE 0
    END AS discount_rate,
    amount * (1 - CASE
        WHEN amount >= 1000 THEN 0.10
        WHEN amount >= 500  THEN 0.05
        WHEN amount >= 100  THEN 0.02
        ELSE 0
    END) AS discounted_price
FROM orders
WHERE status = 'paid';

You can also reference a CASE expression in a subquery or CTE to avoid repeating the logic:

WITH categorized AS (
    SELECT
        order_id,
        customer,
        amount,
        CASE
            WHEN amount >= 1000 THEN 'Large'
            WHEN amount >= 100  THEN 'Medium'
            ELSE 'Small'
        END AS order_size
    FROM orders
)
SELECT order_size, COUNT(*) AS count, AVG(amount) AS avg_amount
FROM categorized
GROUP BY order_size;

CASE WHEN in WHERE

You can use CASE WHEN inside a WHERE clause to apply conditional filter logic. This is less common than using it in SELECT, but useful when the filtering condition itself depends on a variable or parameter.

-- Filter rows where the status label matches a target
SELECT order_id, customer, amount, status
FROM orders
WHERE
    CASE
        WHEN status IN ('paid', 'pending') THEN 'active'
        ELSE 'inactive'
    END = 'active';

This returns orders with status 'paid' or 'pending'. In this specific case, using WHERE status IN ('paid', 'pending') is simpler and faster. Reach for CASE in WHERE when the condition itself requires conditional branching based on a runtime value, such as when building dynamic queries or when different columns need to be checked under different circumstances.

CASE WHEN in ORDER BY

CASE WHEN in ORDER BY lets you sort rows using a custom priority that is not part of the table data. A common use case is putting certain values first regardless of alphabetical order.

-- Sort by custom status priority: pending first, then paid, then refunded
SELECT order_id, customer, amount, status
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'  THEN 1
        WHEN 'paid'     THEN 2
        WHEN 'refunded' THEN 3
        ELSE 4
    END,
    order_date DESC;

This query sorts pending orders to the top (priority 1), then paid orders, then refunded, and puts any unknown status last. Within each priority group, rows are sorted by date descending.

Another use case is pushing NULLs to the end rather than the beginning (default in some databases):

SELECT order_id, customer, status
FROM orders
ORDER BY
    CASE WHEN status IS NULL THEN 1 ELSE 0 END,
    status ASC;

CASE WHEN in GROUP BY

You can group rows using a CASE expression, which lets you define custom grouping buckets on the fly. This is useful for grouping continuous values into ranges or applying custom categorization before aggregating.

-- Group orders into size buckets and count + total each
SELECT
    CASE
        WHEN amount >= 1000 THEN 'Large (1000+)'
        WHEN amount >= 100  THEN 'Medium (100-999)'
        ELSE 'Small (under 100)'
    END AS order_size,
    COUNT(*)      AS order_count,
    SUM(amount)   AS total_revenue,
    AVG(amount)   AS avg_order_value
FROM orders
WHERE status = 'paid'
GROUP BY
    CASE
        WHEN amount >= 1000 THEN 'Large (1000+)'
        WHEN amount >= 100  THEN 'Medium (100-999)'
        ELSE 'Small (under 100)'
    END
ORDER BY SUM(amount) DESC;

In most databases (PostgreSQL, SQL Server, Oracle), you must repeat the full CASE expression in the GROUP BY clause. MySQL and SQLite allow referencing the SELECT alias (GROUP BY order_size), but repeating the expression is the portable, standards-compliant approach. See the GROUP BY guide for more on how grouping works.

CASE WHEN with Aggregate Functions (SUM CASE WHEN)

Wrapping CASE WHEN inside an aggregate function is one of the most powerful patterns in SQL. It lets you calculate conditional totals, counts, or averages in a single query row — eliminating the need for multiple queries or subqueries.

Conditional SUM

-- Total revenue broken down by status, in one row per product
SELECT
    product,
    SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid_revenue,
    SUM(CASE WHEN status = 'pending'  THEN amount ELSE 0 END) AS pending_revenue,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount,
    SUM(amount) AS total_amount
FROM orders
GROUP BY product
ORDER BY paid_revenue DESC;

Result

product	paid_revenue	pending_revenue	refunded_amount
Laptop	2350.00	1300.00	0.00
Monitor	930.00	0.00	0.00
Keyboard	0.00	0.00	80.00
Mouse	0.00	25.00	30.00
Webcam	0.00	60.00	0.00

This pattern is sometimes called a conditional pivot. It is the standard way to simulate a PIVOT operation in databases that do not have a native PIVOT operator (such as PostgreSQL and MySQL).

Conditional COUNT

-- Count orders by status category, in one query
SELECT
    COUNT(*)                                              AS total_orders,
    COUNT(CASE WHEN status = 'paid'     THEN 1 END)      AS paid_count,
    COUNT(CASE WHEN status = 'pending'  THEN 1 END)      AS pending_count,
    COUNT(CASE WHEN status = 'refunded' THEN 1 END)      AS refunded_count,
    COUNT(CASE WHEN status IS NULL      THEN 1 END)      AS unknown_count
FROM orders;

When the CASE returns NULL (no ELSE clause, condition not met), COUNT ignores it — because COUNT only counts non-NULL values. This makes COUNT(CASE WHEN ... THEN 1 END) a reliable pattern for conditional counting.

CASE WHEN with NULL

NULL values require special handling in SQL, and CASE WHEN is no exception. The most important rule: never use = NULL to check for NULL. NULL is not equal to anything, including itself. The comparison column = NULL always evaluates to UNKNOWN (not TRUE), so the WHEN branch will never fire.

-- WRONG: this will never match NULL rows
SELECT order_id,
    CASE
        WHEN status = NULL THEN 'No Status'   -- This never fires
        ELSE status
    END AS status_display
FROM orders;

-- CORRECT: use IS NULL
SELECT order_id,
    CASE
        WHEN status IS NULL THEN 'No Status'  -- This correctly detects NULL
        ELSE status
    END AS status_display
FROM orders;

You can also use CASE WHEN to replace NULLs with a default value, which is similar to what COALESCE does:

-- Replace NULL status with a default label
SELECT
    order_id,
    customer,
    CASE WHEN status IS NULL THEN 'Unclassified' ELSE status END AS status_display
FROM orders;

-- COALESCE is shorter for this specific pattern
SELECT order_id, customer, COALESCE(status, 'Unclassified') AS status_display
FROM orders;

Use CASE WHEN when the replacement logic is more complex than a simple NULL substitution. Use COALESCE when you only need the first non-NULL value from a list.

Real-World Examples

Here are four practical patterns you will encounter regularly in production SQL work.

1. Classify customers by lifetime spend

SELECT
    customer,
    SUM(amount) AS lifetime_spend,
    CASE
        WHEN SUM(amount) >= 2000 THEN 'VIP'
        WHEN SUM(amount) >= 500  THEN 'Regular'
        ELSE 'Occasional'
    END AS customer_tier
FROM orders
WHERE status = 'paid'
GROUP BY customer
ORDER BY lifetime_spend DESC;

2. Month-over-month revenue comparison

SELECT
    product,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS jan_revenue,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS feb_revenue,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS mar_revenue
FROM orders
WHERE status = 'paid'
  AND YEAR(order_date) = 2026
GROUP BY product;

3. Flag anomalous rows

-- Identify orders that may need review
SELECT
    order_id,
    customer,
    amount,
    status,
    CASE
        WHEN status IS NULL          THEN 'Missing status'
        WHEN status = 'pending'
             AND order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                                     THEN 'Stale pending'
        WHEN status = 'refunded'
             AND amount >= 1000      THEN 'Large refund'
        ELSE 'OK'
    END AS review_flag
FROM orders
WHERE
    CASE
        WHEN status IS NULL          THEN 1
        WHEN status = 'pending'
             AND order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1
        WHEN status = 'refunded'
             AND amount >= 1000      THEN 1
        ELSE 0
    END = 1;

4. Dynamic column formatting

-- Format amount display based on value
SELECT
    order_id,
    customer,
    CASE
        WHEN amount >= 1000
            THEN CONCAT('$', FORMAT(amount, 0), ' (large order)')
        ELSE
            CONCAT('$', FORMAT(amount, 2))
    END AS formatted_amount
FROM orders;

Common Mistakes

1. Using = NULL instead of IS NULL

As covered above, WHEN column = NULL never evaluates to TRUE. Always use WHEN column IS NULL. This is the single most common CASE WHEN bug.

2. Forgetting that CASE stops at the first match

CASE evaluates conditions from top to bottom and stops the moment one is true. If you write overlapping conditions in the wrong order, rows can be assigned to the wrong bucket.

-- WRONG order: every order >= 100 matches the first condition
CASE
    WHEN amount >= 100  THEN 'Medium'   -- A $1200 order matches here and stops
    WHEN amount >= 1000 THEN 'Large'    -- This is never reached
    ELSE 'Small'
END

-- CORRECT order: most restrictive condition first
CASE
    WHEN amount >= 1000 THEN 'Large'
    WHEN amount >= 100  THEN 'Medium'
    ELSE 'Small'
END

3. Missing ELSE causing unexpected NULLs

If no WHEN condition matches and there is no ELSE, the CASE returns NULL silently. This can corrupt aggregations. For example, SUM(CASE WHEN ... THEN amount END) without ELSE excludes non-matching rows entirely (because SUM ignores NULLs), but AVG also ignores NULLs, which may skew your average. Add ELSE 0 for numeric expressions and ELSE 'Unknown' or ELSE NULL explicitly for labels so your intent is clear.

4. Comparing CASE results to values of different data types

All THEN branches in a single CASE expression must be compatible data types. Mixing a numeric THEN with a string ELSE (for example, THEN 1 ... ELSE 'none') will cause a type error in strict databases like PostgreSQL. Keep all THEN and ELSE results the same type.

5. Using CASE WHEN in GROUP BY but not matching SELECT exactly

If you use a CASE expression in the SELECT list and want to group by it, the GROUP BY expression must be textually identical to the SELECT expression (in databases that require it). If you change even spacing or capitalization, some databases will treat it as a different expression. Using a CTE to define the computed column first, then grouping on the alias, is a cleaner approach.

Frequently Asked Questions

What is the difference between simple CASE and searched CASE in SQL?

Simple CASE compares one expression to a list of values using CASE expression WHEN value THEN result syntax. It can only check equality. Searched CASE evaluates independent boolean conditions using CASE WHEN condition THEN result syntax, which allows comparisons like >, <, BETWEEN, LIKE, and IS NULL. Searched CASE is more flexible and is the form used in most real-world queries.

What happens if no WHEN condition matches and there is no ELSE?

If no WHEN condition matches and you have not included an ELSE clause, SQL returns NULL for that row. This is the default behavior in all major databases including MySQL, PostgreSQL, SQL Server, and Oracle. To avoid unexpected NULLs in your output, always add an ELSE clause as a catch-all, for example ELSE 'Unknown' or ELSE 0.

Can I use CASE WHEN inside a SUM or other aggregate function?

Yes. SUM(CASE WHEN condition THEN value ELSE 0 END) is one of the most useful patterns in SQL. It lets you calculate conditional totals within a single query. For example, SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) computes the total of only paid transactions. This technique is often used to pivot rows into columns without a dedicated PIVOT operator. Learn more in the GROUP BY guide.

Can CASE WHEN handle NULL values?

Yes, but you cannot use CASE WHEN column = NULL because NULL is never equal to anything, including another NULL. Use CASE WHEN column IS NULL THEN ... instead. SQL evaluates IS NULL correctly and will match rows where the column has no value.

Can I nest CASE WHEN expressions inside each other?

Yes, SQL allows nested CASE expressions where the THEN or ELSE result is itself another CASE WHEN block. This works in all major databases. However, deep nesting makes queries hard to read and maintain. If you find yourself nesting more than two levels deep, consider refactoring the logic into a subquery, CTE, or application layer. See also: SQL WHERE clause for filtering alternatives and SQL JOINs for combining data before applying CASE logic.