CASE WHEN in SQL: Complete Guide with Examples

The CASE expression is SQL's version of if-then-else logic. It lets you add conditional logic directly inside your queries, transforming values, categorizing data, and creating computed columns without leaving the database.

This guide covers every way you will use CASE in practice, from simple value mapping to complex nested conditions.

Basic Syntax

There are two forms of the CASE expression in SQL.

Simple CASE compares one expression to multiple values:

SELECT order_id,
       status,
       CASE status
           WHEN 'shipped' THEN 'On the way'
           WHEN 'delivered' THEN 'Complete'
           WHEN 'returned' THEN 'Refunded'
           ELSE 'Processing'
       END AS status_label
FROM orders;

Searched CASE evaluates independent Boolean conditions:

SELECT product_name,
       price,
       CASE
           WHEN price >= 100 THEN 'Premium'
           WHEN price >= 50 THEN 'Mid-range'
           WHEN price >= 20 THEN 'Budget'
           ELSE 'Economy'
       END AS price_tier
FROM products;

The searched form is more flexible because each WHEN clause can test a completely different condition.

CASE WHEN with Aggregations

One of the most powerful patterns is combining CASE with aggregate functions. This lets you pivot data, create conditional counts, and build summary reports in a single query.

Conditional COUNT:

SELECT
    department,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary BETWEEN 50000 AND 100000 THEN 1 END) AS mid_earners,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS entry_level
FROM employees
GROUP BY department;

Conditional SUM:

SELECT
    region,
    SUM(revenue) AS total_revenue,
    SUM(CASE WHEN channel = 'online' THEN revenue ELSE 0 END) AS online_revenue,
    SUM(CASE WHEN channel = 'retail' THEN revenue ELSE 0 END) AS retail_revenue
FROM sales
GROUP BY region;

Conditional AVG:

SELECT
    product_category,
    AVG(CASE WHEN return_reason IS NULL THEN rating END) AS avg_rating_kept,
    AVG(CASE WHEN return_reason IS NOT NULL THEN rating END) AS avg_rating_returned
FROM reviews
GROUP BY product_category;

CASE in WHERE Clauses

While less common, CASE can appear in WHERE clauses for dynamic filtering:

SELECT *
FROM orders
WHERE CASE
    WHEN @filter_type = 'recent' THEN order_date > DATEADD(day, -30, GETDATE())
    WHEN @filter_type = 'large' THEN total_amount > 1000
    WHEN @filter_type = 'problem' THEN status IN ('cancelled', 'returned')
    ELSE 1 = 1
END;

CASE in ORDER BY

Sort results using custom logic:

SELECT ticket_id, priority, created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END,
    created_at ASC;

Nested CASE Expressions

You can nest CASE inside CASE for multi-dimensional logic:

SELECT
    customer_name,
    total_spend,
    account_age_months,
    CASE
        WHEN total_spend > 10000 THEN
            CASE
                WHEN account_age_months > 24 THEN 'VIP Loyal'
                ELSE 'VIP New'
            END
        WHEN total_spend > 1000 THEN
            CASE
                WHEN account_age_months > 12 THEN 'Regular Loyal'
                ELSE 'Regular Growing'
            END
        ELSE 'Casual'
    END AS customer_segment
FROM customers;

Keep nesting to two levels maximum. Beyond that, consider a lookup table or a CTE to improve readability.

CASE with NULL Handling

CASE interacts with NULLs in specific ways. A simple CASE cannot match NULL with equality:

-- This will NOT match NULL values
CASE status
    WHEN NULL THEN 'Unknown'  -- Never matches
    WHEN 'active' THEN 'Active'
END

-- Use searched CASE instead
CASE
    WHEN status IS NULL THEN 'Unknown'
    WHEN status = 'active' THEN 'Active'
END

CASE for Data Cleaning

CASE is essential for standardizing messy data:

SELECT
    raw_country,
    CASE
        WHEN UPPER(raw_country) IN ('US', 'USA', 'UNITED STATES', 'U.S.', 'U.S.A.') THEN 'United States'
        WHEN UPPER(raw_country) IN ('UK', 'GB', 'UNITED KINGDOM', 'GREAT BRITAIN') THEN 'United Kingdom'
        WHEN UPPER(raw_country) IN ('DE', 'GERMANY', 'DEUTSCHLAND') THEN 'Germany'
        ELSE INITCAP(raw_country)
    END AS standardized_country
FROM customer_addresses;

CASE for Bucketing and Histograms

Create distribution buckets without external tools:

SELECT
    CASE
        WHEN response_time_ms < 100 THEN '0-100ms'
        WHEN response_time_ms < 500 THEN '100-500ms'
        WHEN response_time_ms < 1000 THEN '500ms-1s'
        WHEN response_time_ms < 5000 THEN '1-5s'
        ELSE '5s+'
    END AS latency_bucket,
    COUNT(*) AS request_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS percentage
FROM api_logs
WHERE timestamp > CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY MIN(response_time_ms);

Performance Considerations

CASE expressions are evaluated row by row. For large tables:

1. Put the most common conditions first (SQL evaluates WHEN clauses in order and stops at the first match)
2. Avoid calling functions inside CASE when possible (pre-compute in a CTE)
3. Consider materialized columns or generated columns if the same CASE logic runs on every query

CASE Across Different Databases

Common Mistakes

Forgetting ELSE: Without ELSE, unmatched rows return NULL. This is fine for counts but breaks string concatenation:

-- Returns NULL for unmatched rows (potentially breaks downstream)
CASE WHEN status = 'active' THEN 'Yes' END

-- Always specify a default
CASE WHEN status = 'active' THEN 'Yes' ELSE 'No' END

Overlapping conditions: CASE stops at the first match. Order matters:

-- Bug: everything over 50 matches the first condition
CASE WHEN score > 50 THEN 'Pass' WHEN score > 90 THEN 'Excellent' END

-- Fix: put the most specific condition first
CASE WHEN score > 90 THEN 'Excellent' WHEN score > 50 THEN 'Pass' END

Real-World Patterns

Revenue recognition by quarter:

SELECT
    EXTRACT(YEAR FROM invoice_date) AS fiscal_year,
    SUM(CASE WHEN EXTRACT(QUARTER FROM invoice_date) = 1 THEN amount ELSE 0 END) AS q1,
    SUM(CASE WHEN EXTRACT(QUARTER FROM invoice_date) = 2 THEN amount ELSE 0 END) AS q2,
    SUM(CASE WHEN EXTRACT(QUARTER FROM invoice_date) = 3 THEN amount ELSE 0 END) AS q3,
    SUM(CASE WHEN EXTRACT(QUARTER FROM invoice_date) = 4 THEN amount ELSE 0 END) AS q4
FROM invoices
GROUP BY 1
ORDER BY 1;

Funnel conversion analysis:

SELECT
    utm_source,
    COUNT(*) AS visitors,
    COUNT(CASE WHEN signed_up THEN 1 END) AS signups,
    COUNT(CASE WHEN activated THEN 1 END) AS activated,
    COUNT(CASE WHEN paid THEN 1 END) AS paying,
    ROUND(COUNT(CASE WHEN paid THEN 1 END) * 100.0 / COUNT(*), 2) AS conversion_rate
FROM user_journeys
GROUP BY utm_source
ORDER BY conversion_rate DESC;

When to Use CASE vs. Application Logic

Use CASE in SQL when:

• You need the conditional logic for filtering, grouping, or aggregation
• You want the database to handle the transformation before sending data over the network
• You are building reports or materialized views

Use application code when:

• The logic requires external data (API calls, file lookups)
• The conditions are complex enough to warrant unit tests
• You need to format data for display (locale-specific currency, date formatting)

Platforms like Skopx eliminate the need to write CASE expressions manually. You describe what you want in plain English ("show me revenue by region, split by online vs retail") and the AI generates the appropriate SQL with CASE logic included.

Summary

CASE is one of the most versatile tools in SQL. Master these patterns and you can handle data categorization, pivot tables, conditional aggregation, and custom sorting without ever leaving your database.