CASE Statements - Conditional Logic in SQL

Introduction

What if you want to display different values based on conditions—like categorizing customers as "High Value" or "Low Value" based on their spending?

SQL isn't a procedural language like Python or JavaScript, but it has CASE statements for conditional logic!

CASE lets you:

• Create calculated columns with conditional values
• Categorize data into buckets
• Handle NULL values intelligently
• Perform conditional aggregations

CASE Expression Syntax

There are two forms of CASE: Simple CASE and Searched CASE.

Searched CASE (Most Common)

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

Example:

SELECT
    name,
    price,
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_tier
FROM products;

Result:

       name        | price  | price_tier
-------------------+--------+------------
 Laptop Pro 15     | 999.99 | Premium
 Wireless Mouse    |  79.99 | Mid-range
 USB Cable         |   9.99 | Budget
 Monitor 27"       | 299.99 | Premium

Simple CASE

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Example:

SELECT
    name,
    status,
    CASE status
        WHEN 'pending' THEN 'Order Received'
        WHEN 'shipped' THEN 'On the Way'
        WHEN 'delivered' THEN 'Complete'
        ELSE 'Unknown'
    END AS status_description
FROM orders;

When to use:

• Searched CASE: Complex conditions (comparisons, AND, OR, etc.)
• Simple CASE: Exact value matching (=)

CASE in SELECT Clause

Categorizing Data

SELECT
    name,
    total_spent,
    CASE
        WHEN total_spent >= 1000 THEN 'VIP'
        WHEN total_spent >= 500 THEN 'High Value'
        WHEN total_spent >= 100 THEN 'Regular'
        WHEN total_spent > 0 THEN 'Low Value'
        ELSE 'No Purchases'
    END AS customer_tier
FROM (
    SELECT
        c.name,
        COALESCE(SUM(o.total), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
) AS customer_totals;

Handling NULL Values

SELECT
    name,
    email,
    phone,
    CASE
        WHEN email IS NOT NULL AND phone IS NOT NULL THEN 'Complete'
        WHEN email IS NOT NULL THEN 'Email Only'
        WHEN phone IS NOT NULL THEN 'Phone Only'
        ELSE 'No Contact Info'
    END AS contact_status
FROM customers;

Multiple Conditions with AND/OR

SELECT
    name,
    stock,
    price,
    CASE
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 AND price > 100 THEN 'Low Stock (High Value)'
        WHEN stock < 10 THEN 'Low Stock'
        WHEN stock >= 100 THEN 'Well Stocked'
        ELSE 'Normal'
    END AS stock_status
FROM products;

CASE in WHERE Clause

-- Dynamic filtering based on conditions
SELECT name, price
FROM products
WHERE
    CASE
        WHEN category = 'Electronics' THEN price > 500
        WHEN category = 'Books' THEN price > 20
        ELSE price > 50
    END;

This filters products based on category-specific price thresholds.

CASE in ORDER BY

Custom Sorting

-- Sort by custom priority
SELECT name, status
FROM orders
ORDER BY
    CASE status
        WHEN 'pending' THEN 1
        WHEN 'processing' THEN 2
        WHEN 'shipped' THEN 3
        WHEN 'delivered' THEN 4
        ELSE 5
    END,
    order_date DESC;

Result: Orders sorted by status priority, then by date.

Conditional Sorting Direction

-- Sort by price ascending for books, descending for electronics
SELECT name, category, price
FROM products
ORDER BY
    CASE
        WHEN category = 'Books' THEN price
    END ASC,
    CASE
        WHEN category = 'Electronics' THEN price
    END DESC;

CASE in Aggregations

Conditional Counting

SELECT
    COUNT(*) AS total_customers,
    COUNT(CASE WHEN email IS NOT NULL THEN 1 END) AS has_email,
    COUNT(CASE WHEN phone IS NOT NULL THEN 1 END) AS has_phone,
    COUNT(CASE WHEN email IS NOT NULL AND phone IS NOT NULL THEN 1 END) AS has_both
FROM customers;

Result:

 total_customers | has_email | has_phone | has_both
-----------------+-----------+-----------+----------
       150       |    147    |    128    |    125

Conditional Summing

SELECT
    SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) AS delivered_revenue,
    SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped_revenue,
    SUM(CASE WHEN status = 'pending' THEN total ELSE 0 END) AS pending_revenue
FROM orders;

Result:

 delivered_revenue | shipped_revenue | pending_revenue
-------------------+-----------------+-----------------
      87500.25      |    12450.75     |     5847.50

Pivoting Data

-- Revenue by category and month (pivoted)
SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(CASE WHEN p.category = 'Electronics' THEN oi.quantity * oi.price ELSE 0 END) AS electronics_revenue,
    SUM(CASE WHEN p.category = 'Books' THEN oi.quantity * oi.price ELSE 0 END) AS books_revenue,
    SUM(CASE WHEN p.category = 'Clothing' THEN oi.quantity * oi.price ELSE 0 END) AS clothing_revenue
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

Result:

        month        | electronics_revenue | books_revenue | clothing_revenue
---------------------+---------------------+---------------+------------------
 2024-01-01 00:00:00 |       8750.00       |    2547.50    |      3125.75
 2024-02-01 00:00:00 |       9450.75       |    3125.25    |      3847.50

CASE in GROUP BY

-- Group by calculated tier
SELECT
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_tier,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
        ELSE 'Premium'
    END
ORDER BY avg_price;

Note: You must repeat the CASE expression in GROUP BY (can't use alias in most databases).

Cleaner alternative with CTE:

WITH product_tiers AS (
    SELECT
        *,
        CASE
            WHEN price < 20 THEN 'Budget'
            WHEN price < 100 THEN 'Mid-range'
            ELSE 'Premium'
        END AS price_tier
    FROM products
)
SELECT
    price_tier,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM product_tiers
GROUP BY price_tier
ORDER BY avg_price;

Nested CASE Statements

SELECT
    name,
    stock,
    price,
    CASE
        WHEN stock = 0 THEN 'Out of Stock'
        ELSE
            CASE
                WHEN price > 500 THEN 'In Stock (Premium)'
                WHEN price > 100 THEN 'In Stock (Mid-range)'
                ELSE 'In Stock (Budget)'
            END
    END AS availability
FROM products;

Tip: Deeply nested CASE can be hard to read. Consider using multiple columns or CTEs instead.

CASE with COALESCE and NULLIF

Combining CASE and COALESCE

SELECT
    name,
    CASE
        WHEN COALESCE(email, phone) IS NOT NULL THEN 'Contactable'
        ELSE 'No Contact Info'
    END AS contact_status
FROM customers;

Using NULLIF

-- Avoid division by zero
SELECT
    category,
    COUNT(*) AS total_products,
    SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock,
    ROUND(
        100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) /
        NULLIF(COUNT(*), 0),
        2
    ) AS in_stock_pct
FROM products
GROUP BY category;

Common CASE Patterns

Pattern 1: Age Buckets

SELECT
    name,
    created_at,
    CASE
        WHEN created_at >= CURRENT_DATE - INTERVAL '30 days' THEN 'New (0-30 days)'
        WHEN created_at >= CURRENT_DATE - INTERVAL '90 days' THEN 'Recent (30-90 days)'
        WHEN created_at >= CURRENT_DATE - INTERVAL '365 days' THEN 'Active (90-365 days)'
        ELSE 'Old (1+ year)'
    END AS customer_age
FROM customers;

Pattern 2: Status Indicators

SELECT
    product_name,
    stock,
    CASE
        WHEN stock = 0 THEN '🔴 Out of Stock'
        WHEN stock < 10 THEN '🟡 Low Stock'
        ELSE '🟢 In Stock'
    END AS status_indicator
FROM products;

Pattern 3: Score Grading

SELECT
    student_name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM test_scores;

Pattern 4: Binary Flags

SELECT
    name,
    CASE WHEN active = TRUE THEN 'Yes' ELSE 'No' END AS is_active,
    CASE WHEN verified = TRUE THEN 'Yes' ELSE 'No' END AS is_verified
FROM customers;

CASE vs IF/SWITCH in Other Languages

SQL CASE

SELECT
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
        ELSE 'Premium'
    END
FROM products;

JavaScript Equivalent

let tier;
if (price < 20) {
    tier = 'Budget';
} else if (price < 100) {
    tier = 'Mid-range';
} else {
    tier = 'Premium';
}

Python Equivalent (Python 3.10+)

match price:
    case p if p < 20: tier = 'Budget'
    case p if p < 100: tier = 'Mid-range'
    case _: tier = 'Premium'

SQL CASE is an expression (returns a value), not a statement!

Performance Tips

Avoid Redundant CASEs

-- ❌ Inefficient: Multiple CASE on same logic
SELECT
    CASE WHEN total > 100 THEN 'High' ELSE 'Low' END AS tier1,
    CASE WHEN total > 100 THEN 'Premium' ELSE 'Standard' END AS tier2
FROM orders;

-- ✅ Better: Use CTE
WITH order_tiers AS (
    SELECT
        *,
        total > 100 AS is_high_value
    FROM orders
)
SELECT
    CASE WHEN is_high_value THEN 'High' ELSE 'Low' END AS tier1,
    CASE WHEN is_high_value THEN 'Premium' ELSE 'Standard' END AS tier2
FROM order_tiers;

Order Conditions by Frequency

-- ✅ Put most common conditions first
CASE
    WHEN status = 'delivered' THEN ...  -- 70% of orders
    WHEN status = 'shipped' THEN ...    -- 20%
    WHEN status = 'pending' THEN ...    -- 9%
    ELSE ...                            -- 1%
END

Common Mistakes

Mistake 1: Missing ELSE

-- ❌ Returns NULL for unmatched rows
SELECT
    name,
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
    END AS tier
FROM products;
-- Products with price >= 100 have tier = NULL

-- ✅ Always include ELSE
SELECT
    name,
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
        ELSE 'Premium'
    END AS tier
FROM products;

Mistake 2: Overlapping Conditions

-- ❌ Wrong: Overlapping ranges
CASE
    WHEN price < 100 THEN 'Cheap'    -- Matches price = 50
    WHEN price < 50 THEN 'Very Cheap' -- Never reached!
END

-- ✅ Correct: Non-overlapping, ordered conditions
CASE
    WHEN price < 50 THEN 'Very Cheap'
    WHEN price < 100 THEN 'Cheap'
    ELSE 'Expensive'
END

Mistake 3: Using = Instead of IS NULL

-- ❌ Wrong: NULL never equals anything
CASE WHEN email = NULL THEN 'No Email' END

-- ✅ Correct: Use IS NULL
CASE WHEN email IS NULL THEN 'No Email' END

Practice Exercises

Exercise 1

Categorize customers by order count: 'New' (0 orders), 'Occasional' (1-5), 'Regular' (6-10), 'Frequent' (11+).

SELECT
    c.name,
    COUNT(o.id) AS order_count,
    CASE
        WHEN COUNT(o.id) = 0 THEN 'New'
        WHEN COUNT(o.id) BETWEEN 1 AND 5 THEN 'Occasional'
        WHEN COUNT(o.id) BETWEEN 6 AND 10 THEN 'Regular'
        ELSE 'Frequent'
    END AS customer_type
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY order_count DESC;

Exercise 2

Create a summary showing count of orders by status.

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

Exercise 3

Calculate shipping fee based on order total: Free for $100+, $10 for $50-$99, $15 otherwise.

SELECT
    id,
    total,
    CASE
        WHEN total >= 100 THEN 0
        WHEN total >= 50 THEN 10
        ELSE 15
    END AS shipping_fee,
    total + CASE
        WHEN total >= 100 THEN 0
        WHEN total >= 50 THEN 10
        ELSE 15
    END AS total_with_shipping
FROM orders;

Exercise 4

Pivot revenue by quarter for 2024.

SELECT
    SUM(CASE WHEN EXTRACT(QUARTER FROM order_date) = 1 THEN total ELSE 0 END) AS q1_revenue,
    SUM(CASE WHEN EXTRACT(QUARTER FROM order_date) = 2 THEN total ELSE 0 END) AS q2_revenue,
    SUM(CASE WHEN EXTRACT(QUARTER FROM order_date) = 3 THEN total ELSE 0 END) AS q3_revenue,
    SUM(CASE WHEN EXTRACT(QUARTER FROM order_date) = 4 THEN total ELSE 0 END) AS q4_revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

Key Takeaways

• ✅ CASE is an expression, not a statement (returns a value)
• ✅ Searched CASE uses WHEN conditions (most flexible)
• ✅ Simple CASE matches exact values (less common)
• ✅ Use in SELECT for calculated columns and categorization
• ✅ Use in WHERE for conditional filtering
• ✅ Use in ORDER BY for custom sorting
• ✅ Use in aggregates for conditional counting/summing
• ✅ Always include ELSE to avoid unexpected NULLs
• ✅ Order conditions from most specific to most general
• ✅ Use IS NULL, not = NULL

Next Steps

You've mastered CASE statements! In the next lesson, we'll explore DISTINCT and LIMIT—removing duplicates and controlling result set size for efficient queries.

Keep building your SQL toolkit!