SQL CASE WHEN for Conditional Logic

Every application has conditional logic. Show "In Stock" or "Out of Stock" based on inventory. Label customers as "New" or "Returning." Calculate different shipping rates by region. Group revenue into buckets for a report. In application code, you use if/else statements. In SQL, the equivalent is CASE WHEN.

The SQL CASE WHEN expression brings if/else logic directly into your queries, letting you transform values, create computed labels, control sort order, conditionally aggregate data, and even pivot rows into columns, all without pulling data into your application for processing. It works everywhere an expression is valid: in SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and inside aggregate functions.

This guide covers both forms of CASE, demonstrates its use in every major clause, shows how to pivot data with it, and walks through the mistakes that trip up developers learning conditional SQL.

The Sample Data

All examples use these tables:

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    price         DECIMAL(10,2),
    stock_qty     INT,
    rating        DECIMAL(2,1)
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    status        VARCHAR(20),
    total_amount  DECIMAL(10,2),
    region        VARCHAR(20)
);

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    department    VARCHAR(50),
    salary        DECIMAL(10,2),
    hire_date     DATE,
    performance   VARCHAR(20)
);

INSERT INTO products VALUES
(101, 'Wireless Mouse',      'Electronics', 25.99,  150, 4.5),
(102, 'USB-C Cable',          'Electronics',  9.99,  300, 3.8),
(103, 'Notebook A5',          'Stationery',   4.50,  500, 4.2),
(104, 'Mechanical Keyboard',  'Electronics', 89.99,    0, 4.9),
(105, 'Desk Lamp',            'Furniture',   34.50,    3, 3.0),
(106, 'Pen Pack',             'Stationery',   2.99,  800, 4.1),
(107, 'Standing Desk',        'Furniture',  299.99,   12, 4.7),
(108, 'Monitor Arm',          'Furniture',   74.50,    0, 3.5);

INSERT INTO orders VALUES
(1001, 1, '2024-01-15', 'delivered',  155.97, 'North'),
(1002, 2, '2024-02-22', 'delivered',  299.99, 'South'),
(1003, 1, '2024-03-10', 'delivered',   45.48, 'North'),
(1004, 3, '2024-04-05', 'cancelled',   89.99, 'East'),
(1005, 2, '2024-05-18', 'shipped',      9.99, 'West'),
(1006, 4, '2024-06-01', 'delivered',  310.00, 'North'),
(1007, 1, '2024-06-10', 'pending',    175.00, 'South'),
(1008, 3, '2024-06-14', 'processing',  64.50, 'East'),
(1009, 5, '2024-06-15', 'pending',     42.00, 'West');

INSERT INTO employees VALUES
(201, 'Alice',  'Engineering', 95000,  '2019-03-15', 'excellent'),
(202, 'Bob',    'Engineering', 82000,  '2021-07-01', 'good'),
(203, 'Carol',  'Marketing',   68000,  '2022-01-10', 'excellent'),
(204, 'Dave',   'Marketing',   72000,  '2020-09-20', 'average'),
(205, 'Eve',    'Sales',       61000,  '2023-02-14', 'good'),
(206, 'Frank',  'Sales',       58000,  '2022-08-30', 'average'),
(207, 'Grace',  'Engineering', 78000,  '2020-11-05', 'good'),
(208, 'Hank',   'Sales',       63000,  '2021-04-18', 'excellent');

Simple CASE: Matching Exact Values

The simple CASE compares a single expression against a list of possible values. It works like a switch statement in many programming languages.

Syntax

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    WHEN value3 THEN result3
    ELSE default_result
END

The database evaluates expression once, then checks each WHEN value in order. The first match wins, and its corresponding THEN result is returned. If no WHEN matches, the ELSE result is returned. If there is no ELSE, the result is NULL.

Example: Translate Status Codes

SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending'    THEN 'Awaiting Processing'
        WHEN 'processing' THEN 'Being Prepared'
        WHEN 'shipped'    THEN 'In Transit'
        WHEN 'delivered'  THEN 'Completed'
        WHEN 'cancelled'  THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

order_id	status	status_label
1001	delivered	Completed
1002	delivered	Completed
1003	delivered	Completed
1004	cancelled	Cancelled
1005	shipped	In Transit
1006	delivered	Completed
1007	pending	Awaiting Processing
1008	processing	Being Prepared
1009	pending	Awaiting Processing

Example: Map Categories to Departments

SELECT
    product_name,
    category,
    CASE category
        WHEN 'Electronics' THEN 'Tech Team'
        WHEN 'Stationery'  THEN 'Office Supplies'
        WHEN 'Furniture'   THEN 'Facilities'
        ELSE 'Unassigned'
    END AS responsible_team
FROM products;

product_name	category	responsible_team
Wireless Mouse	Electronics	Tech Team
USB-C Cable	Electronics	Tech Team
Notebook A5	Stationery	Office Supplies
Mechanical Keyboard	Electronics	Tech Team
Desk Lamp	Furniture	Facilities
Pen Pack	Stationery	Office Supplies
Standing Desk	Furniture	Facilities
Monitor Arm	Furniture	Facilities

Simple CASE Limitations

Simple CASE only supports exact equality comparisons. You cannot use it for ranges, NULL checks, LIKE patterns, or any condition other than =. For those, you need the searched CASE.

Searched CASE: Flexible Conditions

The searched CASE evaluates independent boolean conditions rather than matching a single expression. Each WHEN contains its own complete condition, giving you full flexibility.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

Example: Price Tiers

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 100 THEN 'Premium'
        WHEN price >= 30  THEN 'Mid-Range'
        WHEN price >= 10  THEN 'Budget'
        ELSE 'Bargain'
    END AS price_tier
FROM products
ORDER BY price DESC;

product_name	price	price_tier
Standing Desk	299.99	Premium
Mechanical Keyboard	89.99	Mid-Range
Monitor Arm	74.50	Mid-Range
Desk Lamp	34.50	Mid-Range
Wireless Mouse	25.99	Budget
USB-C Cable	9.99	Bargain
Notebook A5	4.50	Bargain
Pen Pack	2.99	Bargain

info

Conditions are evaluated top to bottom. The first WHEN that returns TRUE wins. The Standing Desk at $299.99 satisfies both price >= 100 and price >= 30, but it matches the first condition and returns 'Premium'. Order your conditions from most specific to least specific.

Example: Stock Status with Multiple Conditions

SELECT
    product_name,
    stock_qty,
    CASE
        WHEN stock_qty = 0             THEN 'Out of Stock'
        WHEN stock_qty BETWEEN 1 AND 10 THEN 'Low Stock'
        WHEN stock_qty BETWEEN 11 AND 100 THEN 'In Stock'
        ELSE 'Well Stocked'
    END AS availability
FROM products;

product_name	stock_qty	availability
Wireless Mouse	150	Well Stocked
USB-C Cable	300	Well Stocked
Notebook A5	500	Well Stocked
Mechanical Keyboard	0	Out of Stock
Desk Lamp	3	Low Stock
Pen Pack	800	Well Stocked
Standing Desk	12	In Stock
Monitor Arm	0	Out of Stock

Example: Multi-Column Conditions

Searched CASE can reference multiple columns in each condition:

SELECT
    product_name,
    price,
    rating,
    CASE
        WHEN price < 10 AND rating >= 4.0 THEN 'Great Value'
        WHEN price < 10 AND rating < 4.0  THEN 'Cheap but Meh'
        WHEN price >= 50 AND rating >= 4.5 THEN 'Premium Pick'
        WHEN price >= 50 AND rating < 4.0  THEN 'Overpriced'
        ELSE 'Standard'
    END AS assessment
FROM products;

product_name	price	rating	assessment
Wireless Mouse	25.99	4.5	Standard
USB-C Cable	9.99	3.8	Cheap but Meh
Notebook A5	4.50	4.2	Great Value
Mechanical Keyboard	89.99	4.9	Premium Pick
Desk Lamp	34.50	3.0	Standard
Pen Pack	2.99	4.1	Great Value
Standing Desk	299.99	4.7	Premium Pick
Monitor Arm	74.50	3.5	Overpriced

Handling NULLs

Simple CASE cannot match NULL because NULL = NULL is UNKNOWN, not TRUE. Use searched CASE with IS NULL:

Wrong (never matches NULL):

CASE some_column
    WHEN NULL THEN 'Missing'    -- This NEVER matches!
    ELSE 'Has Value'
END

Correct:

CASE
    WHEN some_column IS NULL THEN 'Missing'
    ELSE 'Has Value'
END

For example:

SELECT
    product_name,
    rating,
    CASE
        WHEN rating IS NULL THEN 'Not Rated'
        WHEN rating >= 4.5  THEN 'Excellent'
        WHEN rating >= 3.5  THEN 'Good'
        ELSE 'Below Average'
    END AS rating_label
FROM products;

| product_name | rating | rating_label | |---------------------+--------+---------------| | Wireless Mouse | 4.5 | Excellent | | USB-C Cable | 3.8 | Good | | Notebook A5 | 4.2 | Good | | Mechanical Keyboard | 4.9 | Excellent | | Desk Lamp | 3.0 | Below Average | | Pen Pack | 4.1 | Good | | Standing Desk | 4.7 | Excellent | | Monitor Arm | 3.5 | Good |

warning

This is one of the most common CASE bugs. CASE column WHEN NULL THEN ... silently fails because SQL's three-valued logic means NULL = NULL evaluates to UNKNOWN, which is treated as FALSE. Always use CASE WHEN column IS NULL for NULL checks.

The ELSE Clause: Always Include It

When no WHEN condition matches, the result depends on whether an ELSE clause exists:

With ELSE: Returns the ELSE value.
Without ELSE: Returns NULL.

-- Without ELSE: unmatched values become NULL
SELECT
    product_name,
    CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Stationery'  THEN 'Office'
    END AS team
FROM products;

product_name	team
Wireless Mouse	Tech
USB-C Cable	Tech
Notebook A5	Office
Mechanical Keyboard	Tech
Desk Lamp	NULL
Pen Pack	Office
Standing Desk	NULL
Monitor Arm	NULL

The Furniture products get NULL because no WHEN matched and there is no ELSE.

tip

Always include an ELSE clause unless you intentionally want NULL for unmatched cases. An explicit ELSE 'Unknown' or ELSE 'Other' makes your intent clear and prevents NULLs from silently propagating through calculations.

CASE in Different Clauses

CASE is an expression, not a statement. This means it can appear anywhere a value is valid.

CASE in WHERE

Filter rows based on conditional logic:

-- Show only products that are "available" (in stock or low stock)
SELECT product_name, stock_qty
FROM products
WHERE CASE
    WHEN stock_qty > 0 THEN 'available'
    ELSE 'unavailable'
END = 'available';

While this works, a simpler WHERE stock_qty > 0 achieves the same result. CASE in WHERE is more useful for complex conditional filtering:

-- Dynamic filter: show different products based on a "mode"
-- In practice, the mode would come from a parameter
SELECT product_name, price, category
FROM products
WHERE CASE
    WHEN 'budget' = 'budget' THEN price < 30
    WHEN 'budget' = 'premium' THEN price >= 50
    ELSE TRUE
END;

product_name	price	category
Wireless Mouse	25.99	Electronics
USB-C Cable	9.99	Electronics
Notebook A5	4.50	Stationery
Pen Pack	2.99	Stationery

CASE in ORDER BY

Control sort order dynamically, or sort by custom priority rather than alphabetical order:

-- Sort by status in a custom priority order
SELECT
    order_id,
    status,
    total_amount
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'    THEN 1
        WHEN 'processing' THEN 2
        WHEN 'shipped'    THEN 3
        WHEN 'delivered'  THEN 4
        WHEN 'cancelled'  THEN 5
    END;

order_id	status	total_amount
1007	pending	175.00
1009	pending	42.00
1008	processing	64.50
1005	shipped	9.99
1001	delivered	155.97
1006	delivered	310.00
1003	delivered	45.48
1002	delivered	299.99
1004	cancelled	89.99

Sort with mixed direction:

-- High-value orders first, then alphabetically by status
SELECT order_id, status, total_amount
FROM orders
ORDER BY
    CASE WHEN total_amount >= 100 THEN 0 ELSE 1 END,
    total_amount DESC;

order_id	status	total_amount
1006	delivered	310.00
1002	delivered	299.99
1007	pending	175.00
1001	delivered	155.97
1004	cancelled	89.99
1008	processing	64.50
1003	delivered	45.48
1009	pending	42.00
1005	shipped	9.99

Orders $100+ appear first (sorted by amount descending), followed by orders under $100 (also sorted by amount descending).

CASE in GROUP BY

Group rows by computed categories:

SELECT
    CASE
        WHEN price >= 100 THEN 'Premium'
        WHEN price >= 30  THEN 'Mid-Range'
        ELSE 'Budget'
    END AS price_tier,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price,
    SUM(stock_qty) AS total_stock
FROM products
GROUP BY
    CASE
        WHEN price >= 100 THEN 'Premium'
        WHEN price >= 30  THEN 'Mid-Range'
        ELSE 'Budget'
    END
ORDER BY avg_price DESC;

price_tier	product_count	avg_price	total_stock
Premium	2	299.99	12
Mid-Range	3	66.33	3
Budget	3	10.87	1750

tip

In PostgreSQL and MySQL, you can reference the SELECT alias in GROUP BY:

-- PostgreSQL / MySQL: use the alias
SELECT
    CASE WHEN price >= 100 THEN 'Premium'
         WHEN price >= 30  THEN 'Mid-Range'
         ELSE 'Budget'
    END AS price_tier,
    COUNT(*) AS product_count
FROM products
GROUP BY price_tier;   -- alias works here

SQL Server and Oracle require repeating the full CASE expression in GROUP BY.

CASE in UPDATE

Conditionally update values based on different rules:

-- Apply different price adjustments by category
UPDATE products
SET price = CASE category
    WHEN 'Electronics' THEN ROUND(price * 1.10, 2)    -- 10% increase
    WHEN 'Furniture'   THEN ROUND(price * 0.90, 2)    -- 10% decrease
    ELSE price                                        -- no change
END;

To verify:

SELECT product_name, category, price FROM products;

product_name	category	price
Wireless Mouse	Electronics	28.59
USB-C Cable	Electronics	10.99
Notebook A5	Stationery	4.50
Mechanical Keyboard	Electronics	98.99
Desk Lamp	Furniture	31.05
Pen Pack	Stationery	2.99
Standing Desk	Furniture	269.99
Monitor Arm	Furniture	67.05

Electronics prices went up 10%. Furniture prices went down 10%. Stationery stayed the same.

CASE Inside Aggregate Functions: Conditional Aggregation

One of the most powerful uses of CASE is inside aggregate functions. This lets you count, sum, or average only rows that meet specific conditions, all in a single query without multiple subqueries.

Conditional COUNT

SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'delivered'  THEN 1 END) AS delivered,
    COUNT(CASE WHEN status = 'shipped'    THEN 1 END) AS shipped,
    COUNT(CASE WHEN status = 'pending'    THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'processing' THEN 1 END) AS processing,
    COUNT(CASE WHEN status = 'cancelled'  THEN 1 END) AS cancelled
FROM orders;

total_orders	delivered	shipped	pending	processing	cancelled
9	4	1	2	1	1

How it works: CASE WHEN status = 'delivered' THEN 1 END returns 1 for delivered orders and NULL for everything else. COUNT ignores NULL values, so it only counts the rows where the condition is true.

Conditional SUM

SELECT
    region,
    SUM(total_amount) AS total_revenue,
    SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END) AS delivered_revenue,
    SUM(CASE WHEN status = 'cancelled' THEN total_amount ELSE 0 END) AS lost_revenue
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

region	total_revenue	delivered_revenue	lost_revenue
North	511.45	511.45	0.00
South	474.99	299.99	0.00
East	154.49	0.00	89.99
West	51.99	0.00	0.00

Conditional AVG

SELECT
    department,
    COUNT(*) AS headcount,
    ROUND(AVG(salary), 0) AS avg_salary,
    ROUND(AVG(CASE WHEN performance = 'excellent' THEN salary END), 0) AS avg_excellent_salary,
    ROUND(AVG(CASE WHEN performance = 'average'   THEN salary END), 0) AS avg_average_salary
FROM employees
GROUP BY department;

department	headcount	avg_salary	avg_excellent_salary	avg_average_salary
Marketing	2	70000	68000	72000
Engineering	3	85000	95000	NULL
Sales	3	60667	63000	58000

The NULL for Engineering's avg_average_salary means no Engineering employees have "average" performance.

Percentage Calculations

SELECT
    category,
    COUNT(*) AS total,
    ROUND(
        100.0 * COUNT(CASE WHEN stock_qty = 0 THEN 1 END) / COUNT(*),
        1
    ) AS pct_out_of_stock,
    ROUND(
        100.0 * COUNT(CASE WHEN rating >= 4.0 THEN 1 END) / COUNT(*),
        1
    ) AS pct_highly_rated
FROM products
GROUP BY category;

category	total	pct_out_of_stock	pct_highly_rated
Furniture	3	33.3	33.3
Electronics	3	33.3	66.7
Stationery	2	0.0	100.0

info

FILTER (WHERE ...) as an alternative (PostgreSQL):

PostgreSQL offers a cleaner syntax for conditional aggregation:

SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered_revenue
FROM orders;

total_orders	delivered	pending	delivered_revenue
9	4	2	811.44

This is equivalent to COUNT(CASE WHEN ... THEN 1 END) but more readable. It is PostgreSQL-only.

Using CASE for Pivoting Data

One of the most practical applications of conditional aggregation is pivoting: turning row values into column headers.

Example: Monthly Revenue by Region

Raw data has one row per order. We want a summary with regions as rows and months as columns.

SELECT
    region,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN total_amount ELSE 0 END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN total_amount ELSE 0 END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN total_amount ELSE 0 END) AS mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4 THEN total_amount ELSE 0 END) AS apr,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 5 THEN total_amount ELSE 0 END) AS may,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 6 THEN total_amount ELSE 0 END) AS jun
FROM orders
GROUP BY region
ORDER BY region;

region	jan	feb	mar	apr	may	jun
East	0.00	0.00	0.00	89.99	0.00	64.50
North	155.97	0.00	45.48	0.00	0.00	310.00
South	0.00	299.99	0.00	0.00	0.00	175.00
West	0.00	0.00	0.00	0.00	9.99	42.00

Example: Employee Count by Department and Performance

SELECT
    department,
    COUNT(CASE WHEN performance = 'excellent' THEN 1 END) AS excellent,
    COUNT(CASE WHEN performance = 'good'      THEN 1 END) AS good,
    COUNT(CASE WHEN performance = 'average'   THEN 1 END) AS average,
    COUNT(*) AS total
FROM employees
GROUP BY department
ORDER BY department;

department	excellent	good	average	total
Engineering	1	2	0	3
Marketing	1	0	1	2
Sales	1	1	1	3

Example: Order Status Dashboard

SELECT
    CASE
        WHEN order_date >= CURRENT_DATE - INTERVAL '7 days' THEN 'This Week'
        WHEN order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'This Month'
        ELSE 'Older'
    END AS period,
    COUNT(CASE WHEN status IN ('pending', 'processing') THEN 1 END) AS active,
    COUNT(CASE WHEN status IN ('shipped', 'delivered')  THEN 1 END) AS fulfilled,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    SUM(CASE WHEN status != 'cancelled' THEN total_amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY
    CASE
        WHEN order_date >= CURRENT_DATE - INTERVAL '7 days' THEN 'This Week'
        WHEN order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'This Month'
        ELSE 'Older'
    END;

period	active	fulfilled	cancelled	active_revenue
Older	3	5	1	1102.93

Nested CASE Expressions

CASE expressions can be nested inside each other for multi-level logic:

SELECT
    product_name,
    category,
    price,
    stock_qty,
    CASE category
        WHEN 'Electronics' THEN
            CASE
                WHEN price > 50 THEN 'Premium Electronics'
                ELSE 'Budget Electronics'
            END
        WHEN 'Furniture' THEN
            CASE
                WHEN stock_qty = 0 THEN 'Furniture - Backordered'
                ELSE 'Furniture - Available'
            END
        ELSE 'Other'
    END AS product_label
FROM products;

product_name	category	price	stock_qty	product_label
Wireless Mouse	Electronics	25.99	150	Budget Electronics
USB-C Cable	Electronics	9.99	300	Budget Electronics
Notebook A5	Stationery	4.50	500	Other
Mechanical Keyboard	Electronics	89.99	0	Premium Electronics
Desk Lamp	Furniture	34.50	3	Furniture - Available
Pen Pack	Stationery	2.99	800	Other
Standing Desk	Furniture	299.99	12	Furniture - Available
Monitor Arm	Furniture	74.50	0	Furniture - Backordered

tip

Nested CASE is powerful but quickly becomes hard to read. If you find yourself nesting more than two levels deep, consider whether a lookup table, a CTE with pre-computed labels, or application-level logic would be cleaner.

COALESCE and NULLIF: CASE Shortcuts

Two common CASE patterns are so frequent that SQL provides dedicated shorthand functions.

COALESCE: First Non-NULL Value

COALESCE returns the first non-NULL value from its arguments:

-- These are equivalent:
SELECT COALESCE(phone, email, 'No Contact') AS contact_info ...

SELECT CASE
    WHEN phone IS NOT NULL THEN phone
    WHEN email IS NOT NULL THEN email
    ELSE 'No Contact'
END AS contact_info ...

NULLIF: Return NULL If Values Match

NULLIF(a, b) returns NULL if a = b, otherwise returns a:

-- These are equivalent:
SELECT total / NULLIF(item_count, 0) ...

SELECT total / CASE WHEN item_count = 0 THEN NULL ELSE item_count END ...

NULLIF is most commonly used to prevent division by zero.

Common Mistakes

Mistake 1: Forgetting That Conditions Are Evaluated in Order

-- Wrong: the first condition catches everything >= 30, including Premium
CASE
    WHEN price >= 30  THEN 'Mid-Range'    -- $299.99 matches here first!
    WHEN price >= 100 THEN 'Premium'      -- Never reached for expensive items
    ELSE 'Budget'
END

Correct: most restrictive condition first:

CASE
    WHEN price >= 100 THEN 'Premium'
    WHEN price >= 30  THEN 'Mid-Range'
    ELSE 'Budget'
END

Mistake 2: Using Simple CASE for NULL Checks

-- Bug: NULL never matches in simple CASE
CASE rating
    WHEN NULL THEN 'Not Rated'    -- This NEVER matches
    WHEN 5.0  THEN 'Perfect'
    ELSE 'Rated'
END
-- Products with NULL rating get 'Rated' instead of 'Not Rated'

Fix: use searched CASE:

CASE
    WHEN rating IS NULL THEN 'Not Rated'
    WHEN rating = 5.0   THEN 'Perfect'
    ELSE 'Rated'
END

Mistake 3: Missing ELSE Causes Unexpected NULLs

SELECT
    product_name,
    -- No ELSE: Furniture products get NULL
    CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Stationery'  THEN 'Office'
    END AS team,
    -- NULL in concatenation breaks the string (PostgreSQL)
    'Team: ' || CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Stationery'  THEN 'Office'
    END AS team_label
FROM products;

product_name	team	team_label
Wireless Mouse	Tech	Team: Tech
USB-C Cable	Tech	Team: Tech
Notebook A5	Office	Team: Office
Mechanical Keyboard	Tech	Team: Tech
Desk Lamp	NULL	NULL
Pen Pack	Office	Team: Office
Standing Desk	NULL	NULL
Monitor Arm	NULL	NULL

The Desk Lamp's entire team_label becomes NULL because concatenating with NULL produces NULL in PostgreSQL.

Fix:

SELECT
    product_name,
    CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Stationery'  THEN 'Office'
        ELSE 'Other'
    END AS team,
        'Team: ' || CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Stationery'  THEN 'Office'
        ELSE 'Other'
    END AS team_label
FROM products;

product_name	team	team_label
Wireless Mouse	Tech	Team: Tech
USB-C Cable	Tech	Team: Tech
Notebook A5	Office	Team: Office
Mechanical Keyboard	Tech	Team: Tech
Desk Lamp	Other	Other
Pen Pack	Office	Team: Office
Standing Desk	Other	Other
Monitor Arm	Other	Other

Mistake 4: Inconsistent Data Types in THEN/ELSE

All THEN and ELSE results must be compatible types:

-- Error: mixing string and number
CASE
    WHEN stock_qty > 0 THEN stock_qty      -- integer
    ELSE 'Out of Stock'                    -- string
END

ERROR: CASE types integer and text cannot be matched

Fix: use consistent types:

-- Option A: all strings
CASE
    WHEN stock_qty > 0 THEN CAST(stock_qty AS VARCHAR)
    ELSE 'Out of Stock'
END

-- Option B: all numbers (use a sentinel value)
CASE
    WHEN stock_qty > 0 THEN stock_qty
    ELSE -1
END

Mistake 5: Repeating CASE in GROUP BY (SQL Server / Oracle)

-- SQL Server / Oracle: must repeat the full expression
SELECT
    CASE WHEN price >= 100 THEN 'Premium' ELSE 'Standard' END AS tier,
    COUNT(*)
FROM products
GROUP BY
    CASE WHEN price >= 100 THEN 'Premium' ELSE 'Standard' END;

-- PostgreSQL / MySQL: can use the alias
GROUP BY tier;  -- cleaner

To avoid repetition in SQL Server, use a CTE:

WITH categorized AS (
    SELECT
        *,
        CASE WHEN price >= 100 THEN 'Premium' ELSE 'Standard' END AS tier
    FROM products
)
SELECT tier, COUNT(*) FROM categorized GROUP BY tier;

Quick Reference

Pattern	Example
Simple CASE	`CASE status WHEN 'active' THEN 'Yes' ELSE 'No' END`
Searched CASE	`CASE WHEN price > 100 THEN 'High' ELSE 'Low' END`
NULL check	`CASE WHEN col IS NULL THEN 'Missing' ELSE col END`
In SELECT	`SELECT CASE WHEN ... END AS label FROM t`
In WHERE	`WHERE CASE WHEN ... END = 'value'`
In ORDER BY	`ORDER BY CASE status WHEN 'urgent' THEN 1 ELSE 2 END`
In GROUP BY	`GROUP BY CASE WHEN price > 50 THEN 'High' ELSE 'Low' END`
Conditional COUNT	`COUNT(CASE WHEN status='active' THEN 1 END)`
Conditional SUM	`SUM(CASE WHEN region='North' THEN amount ELSE 0 END)`
Pivot	`SUM(CASE WHEN month=1 THEN amount ELSE 0 END) AS jan`
In UPDATE	`UPDATE t SET col = CASE WHEN ... THEN v1 ELSE v2 END`

Summary

SQL CASE WHEN is the conditional expression that brings if/else logic into your queries. It comes in two forms: simple CASE for matching exact values, and searched CASE for evaluating flexible boolean conditions.

Key takeaways:

Simple CASE (CASE expr WHEN val THEN ...) compares one expression against multiple values. Use it for straightforward value mapping like status labels or category translations.
Searched CASE (CASE WHEN condition THEN ...) evaluates independent boolean conditions. Use it for ranges, multi-column logic, NULL checks, and any comparison beyond exact equality.
Conditions are evaluated top to bottom. The first matching WHEN wins. Place the most specific conditions first.
Always include ELSE unless you intentionally want NULL for unmatched cases. Missing ELSE is a common source of unexpected NULLs.
CASE works everywhere an expression is valid: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, UPDATE SET, and inside aggregate functions.
Conditional aggregation (COUNT/SUM/AVG with CASE inside) is one of the most powerful SQL patterns. It replaces multiple subqueries with a single pass over the data.
Pivoting uses CASE inside SUM or COUNT with GROUP BY to turn row values into column headers, transforming vertical data into horizontal reports.
Simple CASE cannot match NULL. Always use searched CASE with IS NULL for null checks.
All THEN/ELSE results must have compatible types. You cannot mix strings and numbers in the same CASE expression.

Master CASE WHEN and you unlock the ability to write sophisticated business logic, dynamic reports, and data transformations entirely in SQL.

The Sample Data​

Simple CASE: Matching Exact Values​

Syntax​

Example: Translate Status Codes​

Example: Map Categories to Departments​

Simple CASE Limitations​

Searched CASE: Flexible Conditions​

Syntax​

Example: Price Tiers​

Example: Stock Status with Multiple Conditions​

Example: Multi-Column Conditions​

Handling NULLs​

The ELSE Clause: Always Include It​

CASE in Different Clauses​

CASE in WHERE​

CASE in ORDER BY​

CASE in GROUP BY​

CASE in UPDATE​

CASE Inside Aggregate Functions: Conditional Aggregation​

Conditional COUNT​

Conditional SUM​

Conditional AVG​

Percentage Calculations​

Using CASE for Pivoting Data​

Example: Monthly Revenue by Region​

Example: Employee Count by Department and Performance​

Example: Order Status Dashboard​

Nested CASE Expressions​

COALESCE and NULLIF: CASE Shortcuts​

COALESCE: First Non-NULL Value​

NULLIF: Return NULL If Values Match​

Common Mistakes​

Mistake 1: Forgetting That Conditions Are Evaluated in Order​

Mistake 2: Using Simple CASE for NULL Checks​

Mistake 3: Missing ELSE Causes Unexpected NULLs​

Mistake 4: Inconsistent Data Types in THEN/ELSE​

Mistake 5: Repeating CASE in GROUP BY (SQL Server / Oracle)​

Quick Reference​

Summary​

Table of Contents

The Sample Data

Simple CASE: Matching Exact Values

Syntax

Example: Translate Status Codes

Example: Map Categories to Departments

Simple CASE Limitations

Searched CASE: Flexible Conditions

Syntax

Example: Price Tiers

Example: Stock Status with Multiple Conditions

Example: Multi-Column Conditions

Handling NULLs

The ELSE Clause: Always Include It

CASE in Different Clauses

CASE in WHERE

CASE in ORDER BY

CASE in GROUP BY

CASE in UPDATE

CASE Inside Aggregate Functions: Conditional Aggregation

Conditional COUNT

Conditional SUM

Conditional AVG

Percentage Calculations

Using CASE for Pivoting Data

Example: Monthly Revenue by Region

Example: Employee Count by Department and Performance

Example: Order Status Dashboard

Nested CASE Expressions

COALESCE and NULLIF: CASE Shortcuts

COALESCE: First Non-NULL Value

NULLIF: Return NULL If Values Match

Common Mistakes

Mistake 1: Forgetting That Conditions Are Evaluated in Order

Mistake 2: Using Simple CASE for NULL Checks

Mistake 3: Missing ELSE Causes Unexpected NULLs

Mistake 4: Inconsistent Data Types in THEN/ELSE

Mistake 5: Repeating CASE in GROUP BY (SQL Server / Oracle)

Quick Reference

Summary