ADVANCED ⏱ 45 minutes

CASE Expressions: Conditional Logic

The CASE expression is SQL's version of if-then-else logic, allowing you to return different values based on conditions. Think of it as a multi-way switch: "If this condition is true, return X; if that condition is true, return Y; otherwise return Z." CASE expressions transform how you present data—turning numeric codes into readable labels, categorizing values into ranges, creating custom sort orders, or performing complex conditional calculations. You can use CASE in SELECT, WHERE, ORDER BY, and even in aggregations. Mastering CASE expressions turns static queries into dynamic, business-logic-aware data transformations.

📚 What You'll Learn

What CASE expressions are and how they work
The difference between Simple and Searched CASE
How to use CASE in SELECT clause
How to use CASE in WHERE clause
How to use CASE in ORDER BY clause
How to use CASE with aggregation functions
How to handle multiple conditions and ELSE
Common CASE patterns and use cases
Best practices and performance considerations

1. What is a CASE Expression?

CASE is a conditional expression that returns a value based on one or more conditions. It evaluates conditions sequentially and returns the result of the first TRUE condition.

Basic Structure

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

Simple Example

-- Categorize orders by size
SELECT 
    order_id,
    total,
    CASE
        WHEN total >= 1000 THEN 'Large'
        WHEN total >= 100 THEN 'Medium'
        ELSE 'Small'
    END AS order_size
FROM orders;

-- Process:
-- If total >= 1000 → 'Large'
-- Else if total >= 100 → 'Medium'
-- Else → 'Small'

2. Two Types of CASE

Simple CASE (Equality Check)

-- Simple CASE: Checks equality against a value
SELECT 
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN 'Awaiting Processing'
        WHEN 'shipped' THEN 'In Transit'
        WHEN 'delivered' THEN 'Completed'
        ELSE 'Unknown Status'
    END AS status_description
FROM orders;

-- Equivalent to:
-- IF status = 'pending' THEN 'Awaiting Processing'
-- ELSE IF status = 'shipped' THEN 'In Transit'
-- ...

Searched CASE (Any Condition)

-- Searched CASE: Can use any boolean expression
SELECT 
    product_id,
    name,
    price,
    stock,
    CASE
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 'Low Stock'
        WHEN stock >= 100 THEN 'Overstocked'
        ELSE 'Normal'
    END AS stock_status
FROM products;

-- Can use different operators: <, >, <=, >=, !=, LIKE, BETWEEN, etc.

When to Use Each

Type	Use When	Example
Simple CASE	Checking one column for exact matches	Status codes, category IDs
Searched CASE	Complex conditions, ranges, multiple columns	Price ranges, date comparisons

3. Using CASE in SELECT

Creating Categories

-- Customer tier based on spending
SELECT 
    customer_id,
    name,
    total_spent,
    CASE
        WHEN total_spent >= 10000 THEN 'Platinum'
        WHEN total_spent >= 5000 THEN 'Gold'
        WHEN total_spent >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_tier
FROM customers
ORDER BY total_spent DESC;

Multiple CASE Expressions

-- Product analysis with multiple categorizations
SELECT 
    product_id,
    name,
    price,
    stock,
    
    -- Price category
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Standard'
        ELSE 'Premium'
    END AS price_category,
    
    -- Stock status
    CASE
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 'Low'
        WHEN stock > 100 THEN 'High'
        ELSE 'Normal'
    END AS stock_status,
    
    -- Action needed
    CASE
        WHEN stock = 0 THEN 'Reorder Immediately'
        WHEN stock < 10 AND price > 100 THEN 'Reorder Soon (High Value)'
        WHEN stock < 10 THEN 'Monitor'
        ELSE 'No Action Needed'
    END AS action
FROM products;

Calculated Values

-- Apply different discount rates
SELECT 
    order_id,
    customer_tier,
    total,
    CASE customer_tier
        WHEN 'Platinum' THEN total * 0.80  -- 20% off
        WHEN 'Gold' THEN total * 0.90      -- 10% off
        WHEN 'Silver' THEN total * 0.95    -- 5% off
        ELSE total                          -- No discount
    END AS discounted_total
FROM orders;

4. Handling NULL Values

Replacing NULL

-- Replace NULL with descriptive text
SELECT 
    employee_id,
    name,
    CASE
        WHEN manager_id IS NULL THEN 'No Manager (CEO)'
        ELSE CAST(manager_id AS CHAR)
    END AS manager_id,
    CASE
        WHEN phone IS NULL THEN 'No Phone on File'
        ELSE phone
    END AS phone_display
FROM employees;

NULL in Conditions

-- Check for NULL explicitly
SELECT 
    product_id,
    name,
    CASE
        WHEN discontinue_date IS NULL THEN 'Active'
        WHEN discontinue_date > CURRENT_DATE THEN 'Scheduled for Discontinuation'
        ELSE 'Discontinued'
    END AS product_status
FROM products;

5. Using CASE in WHERE Clause

Dynamic Filtering

-- Filter based on conditional logic
SELECT 
    order_id,
    customer_id,
    total,
    order_date
FROM orders
WHERE 
    CASE
        WHEN DAYOFWEEK(order_date) IN (1, 7) THEN total > 50   -- Weekends: min $50
        ELSE total > 20                                        -- Weekdays: min $20
    END;

-- Returns orders that meet the day-specific minimum

Complex Conditions

-- Priority customers: different criteria per tier
SELECT 
    customer_id,
    name,
    loyalty_tier,
    total_spent
FROM customers
WHERE 
    CASE loyalty_tier
        WHEN 'Platinum' THEN total_spent >= 5000
        WHEN 'Gold' THEN total_spent >= 2000
        WHEN 'Silver' THEN total_spent >= 500
        ELSE total_spent >= 100
    END;

6. Using CASE in ORDER BY

Custom Sort Order

-- Sort by priority order (not alphabetical)
SELECT 
    order_id,
    status,
    total
FROM orders
ORDER BY 
    CASE status
        WHEN 'failed' THEN 1      -- Failed orders first
        WHEN 'pending' THEN 2     -- Then pending
        WHEN 'shipped' THEN 3     -- Then shipped
        WHEN 'delivered' THEN 4   -- Then delivered
        ELSE 5                     -- Everything else last
    END,
    order_date DESC;              -- Within each status, newest first

Conditional Sorting

-- Sort expensive items by discount, cheap items by name
SELECT 
    product_id,
    name,
    price,
    discount_percent
FROM products
ORDER BY 
    CASE
        WHEN price > 100 THEN discount_percent  -- Expensive: by discount
        ELSE 0                                  -- Cheap: ignored
    END DESC,
    CASE
        WHEN price <= 100 THEN name             -- Cheap: by name
        ELSE ''                                 -- Expensive: ignored
    END;

7. CASE in Aggregate Functions

Conditional Counting

-- Count orders by status for each customer
SELECT 
    customer_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY customer_id;

Conditional Sums

-- Calculate revenue by product category
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(CASE WHEN category = 'Electronics' THEN total ELSE 0 END) AS electronics_revenue,
    SUM(CASE WHEN category = 'Clothing' THEN total ELSE 0 END) AS clothing_revenue,
    SUM(CASE WHEN category = 'Books' THEN total ELSE 0 END) AS books_revenue,
    SUM(total) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

Conditional Averages

-- Average order value by customer tier
SELECT 
    customer_tier,
    AVG(CASE WHEN total >= 100 THEN total END) AS avg_large_order,
    AVG(CASE WHEN total < 100 THEN total END) AS avg_small_order,
    COUNT(CASE WHEN total >= 100 THEN 1 END) AS large_order_count,
    COUNT(CASE WHEN total < 100 THEN 1 END) AS small_order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY customer_tier;

8. Nested CASE Expressions

CASE Inside CASE

-- Complex product classification
SELECT 
    product_id,
    name,
    price,
    stock,
    category,
    CASE
        WHEN category = 'Electronics' THEN
            CASE
                WHEN price > 1000 THEN 'Premium Electronics'
                WHEN price > 500 THEN 'Mid-Range Electronics'
                ELSE 'Budget Electronics'
            END
        WHEN category = 'Clothing' THEN
            CASE
                WHEN price > 100 THEN 'Designer Clothing'
                ELSE 'Standard Clothing'
            END
        ELSE 'Other'
    END AS product_classification
FROM products;

Multi-Factor Decision

-- Shipping recommendation based on multiple factors
SELECT 
    order_id,
    total,
    weight,
    destination_country,
    CASE
        WHEN destination_country = 'USA' THEN
            CASE
                WHEN weight < 1 AND total < 50 THEN 'Standard Mail'
                WHEN weight < 5 THEN 'Ground Shipping'
                ELSE 'Freight'
            END
        WHEN destination_country IN ('Canada', 'Mexico') THEN
            CASE
                WHEN weight < 2 THEN 'International Standard'
                ELSE 'International Express'
            END
        ELSE
            'International Freight'
    END AS recommended_shipping
FROM orders;

9. Common CASE Patterns

Pattern 1: Pivot-Style Reporting

-- Transform rows into columns
SELECT 
    customer_id,
    MAX(CASE WHEN order_date >= '2024-01-01' 
             AND order_date < '2024-04-01' THEN total END) AS q1_total,
    MAX(CASE WHEN order_date >= '2024-04-01' 
             AND order_date < '2024-07-01' THEN total END) AS q2_total,
    MAX(CASE WHEN order_date >= '2024-07-01' 
             AND order_date < '2024-10-01' THEN total END) AS q3_total,
    MAX(CASE WHEN order_date >= '2024-10-01' 
             AND order_date < '2025-01-01' THEN total END) AS q4_total
FROM orders
GROUP BY customer_id;

Pattern 2: Flag Creation

-- Create boolean flags
SELECT 
    customer_id,
    name,
    CASE WHEN total_spent > 5000 THEN 1 ELSE 0 END AS is_high_value,
    CASE WHEN last_order_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) 
         THEN 1 ELSE 0 END AS is_at_risk,
    CASE WHEN registration_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
         THEN 1 ELSE 0 END AS is_new_customer
FROM customers;

Pattern 3: Data Validation

-- Identify data quality issues
SELECT 
    product_id,
    name,
    CASE
        WHEN name IS NULL OR name = '' THEN 'Missing Name'
        WHEN price IS NULL THEN 'Missing Price'
        WHEN price <= 0 THEN 'Invalid Price'
        WHEN stock IS NULL THEN 'Missing Stock'
        WHEN category_id IS NULL THEN 'Missing Category'
        ELSE 'Valid'
    END AS data_quality_status
FROM products
WHERE 
    CASE
        WHEN name IS NULL OR name = '' THEN TRUE
        WHEN price IS NULL OR price <= 0 THEN TRUE
        WHEN stock IS NULL THEN TRUE
        WHEN category_id IS NULL THEN TRUE
        ELSE FALSE
    END;

10. Performance Considerations

Best Practices

✅ Order conditions from most to least likely (short-circuit evaluation)
✅ Keep CASE expressions simple and readable
✅ Avoid repeating complex CASE logic (use derived tables)
✅ Use Simple CASE when possible (slightly faster)
✅ Consider computed columns for frequently-used CASE logic
✅ Index columns used in CASE conditions

Optimize Condition Order

-- ✅ GOOD: Most common cases first
CASE
    WHEN status = 'delivered' THEN 'Complete'      -- 80% of orders
    WHEN status = 'shipped' THEN 'In Transit'      -- 15% of orders
    WHEN status = 'pending' THEN 'Processing'      -- 4% of orders
    WHEN status = 'cancelled' THEN 'Cancelled'     -- 1% of orders
END

-- ❌ SLOW: Rare cases first
CASE
    WHEN status = 'cancelled' THEN 'Cancelled'     -- 1% (checked first!)
    WHEN status = 'pending' THEN 'Processing'      -- 4%
    WHEN status = 'shipped' THEN 'In Transit'      -- 15%
    WHEN status = 'delivered' THEN 'Complete'      -- 80% (checked last!)
END

11. 🎯 Practice Exercises

Exercise 1: Customer Segmentation

Given this schema:

customers (customer_id, name, registration_date, total_spent, last_order_date)
orders (order_id, customer_id, order_date, total, status)

Tasks:

Create customer tiers: Platinum (>$10k), Gold ($5k-10k), Silver ($1k-5k), Bronze (<$1k)
Categorize customers: Active, At Risk, or Churned based on last order date
Create a customer health score combining both factors

Show Solution

-- Task 1: Customer tiers
SELECT 
    customer_id,
    name,
    total_spent,
    CASE
        WHEN total_spent >= 10000 THEN 'Platinum'
        WHEN total_spent >= 5000 THEN 'Gold'
        WHEN total_spent >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_tier
FROM customers
ORDER BY total_spent DESC;


-- Task 2: Activity status
SELECT 
    customer_id,
    name,
    last_order_date,
    DATEDIFF(CURRENT_DATE, last_order_date) AS days_since_order,
    CASE
        WHEN last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) 
            THEN 'Active'
        WHEN last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY) 
            THEN 'At Risk'
        ELSE 'Churned'
    END AS activity_status
FROM customers
ORDER BY last_order_date DESC;


-- Task 3: Customer health score
SELECT 
    customer_id,
    name,
    total_spent,
    last_order_date,
    CASE
        WHEN total_spent >= 10000 THEN 'Platinum'
        WHEN total_spent >= 5000 THEN 'Gold'
        WHEN total_spent >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS tier,
    CASE
        WHEN last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) 
            THEN 'Active'
        WHEN last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY) 
            THEN 'At Risk'
        ELSE 'Churned'
    END AS activity,
    CASE
        -- Platinum + Active = Excellent
        WHEN total_spent >= 10000 
         AND last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
            THEN 'Excellent'
        -- High value but at risk
        WHEN total_spent >= 5000 
         AND last_order_date < DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY)
            THEN 'Win-Back Priority'
        -- Active but low spend
        WHEN total_spent < 1000 
         AND last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
            THEN 'Growth Opportunity'
        -- Churned
        WHEN last_order_date < DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY)
            THEN 'Churned'
        ELSE 'Good'
    END AS health_score
FROM customers
ORDER BY 
    CASE
        WHEN total_spent >= 10000 
         AND last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
            THEN 1
        WHEN total_spent >= 5000 
         AND last_order_date < DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY)
            THEN 2
        ELSE 3
    END;

Exercise 2: Product Performance Analysis

Given this schema:

products (product_id, name, price, stock, category, last_restocked)
order_items (item_id, order_id, product_id, quantity)
reviews (review_id, product_id, rating, review_date)

Tasks:

Categorize products by price range: Budget, Standard, Premium, Luxury
Create stock alerts: Critical, Low, Normal, High, Overstocked
Generate action recommendations based on stock, sales, and ratings

Show Solution

-- Task 1: Price categories
SELECT 
    product_id,
    name,
    price,
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 100 THEN 'Standard'
        WHEN price < 500 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_range
FROM products
ORDER BY price;


-- Task 2: Stock alerts
SELECT 
    product_id,
    name,
    stock,
    last_restocked,
    CASE
        WHEN stock = 0 THEN 'Critical - Out of Stock'
        WHEN stock < 5 THEN 'Low - Reorder Soon'
        WHEN stock < 20 THEN 'Normal'
        WHEN stock < 100 THEN 'High'
        ELSE 'Overstocked - Review'
    END AS stock_alert
FROM products
ORDER BY 
    CASE
        WHEN stock = 0 THEN 1
        WHEN stock < 5 THEN 2
        ELSE 3
    END,
    stock;


-- Task 3: Action recommendations
SELECT 
    p.product_id,
    p.name,
    p.price,
    p.stock,
    COUNT(oi.item_id) AS times_ordered,
    AVG(r.rating) AS avg_rating,
    CASE
        -- Out of stock + popular = urgent restock
        WHEN p.stock = 0 AND COUNT(oi.item_id) > 10
            THEN 'URGENT: Restock Popular Item'
        -- Low stock + high value = priority restock
        WHEN p.stock < 10 AND p.price > 100 AND COUNT(oi.item_id) > 5
            THEN 'Priority: Restock High-Value Item'
        -- Poor rating = investigate or discontinue
        WHEN AVG(r.rating) < 3.0 AND AVG(r.rating) IS NOT NULL
            THEN 'Review: Low Rating - Consider Changes'
        -- Overstocked + slow sales = discount
        WHEN p.stock > 100 AND COUNT(oi.item_id) < 5
            THEN 'Consider: Discount to Clear Stock'
        -- Good performer = maintain
        WHEN AVG(r.rating) >= 4.0 AND COUNT(oi.item_id) > 20
            THEN 'Excellent: Top Performer'
        ELSE 'Normal: Monitor'
    END AS recommendation
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.name, p.price, p.stock
ORDER BY 
    CASE
        WHEN p.stock = 0 AND COUNT(oi.item_id) > 10 THEN 1
        WHEN p.stock < 10 AND p.price > 100 THEN 2
        ELSE 3
    END;

🔥 Challenge: Dynamic Sales Report

Create a comprehensive sales report with pivot-style quarterly breakdown and multiple CASE-based classifications:

orders (order_id, customer_id, order_date, total, status)
customers (customer_id, name, country, loyalty_tier)
order_items (item_id, order_id, product_id, quantity, price)
products (product_id, name, category)

Challenge: Create a report showing quarterly sales by category, customer tier distribution, and performance indicators.

Show Solution

-- Comprehensive quarterly sales report
SELECT 
    p.category,
    
    -- Quarterly revenue pivot
    SUM(CASE 
        WHEN QUARTER(o.order_date) = 1 AND YEAR(o.order_date) = 2024 
        THEN oi.quantity * oi.price 
        ELSE 0 
    END) AS q1_2024_revenue,
    
    SUM(CASE 
        WHEN QUARTER(o.order_date) = 2 AND YEAR(o.order_date) = 2024 
        THEN oi.quantity * oi.price 
        ELSE 0 
    END) AS q2_2024_revenue,
    
    SUM(CASE 
        WHEN QUARTER(o.order_date) = 3 AND YEAR(o.order_date) = 2024 
        THEN oi.quantity * oi.price 
        ELSE 0 
    END) AS q3_2024_revenue,
    
    SUM(CASE 
        WHEN QUARTER(o.order_date) = 4 AND YEAR(o.order_date) = 2024 
        THEN oi.quantity * oi.price 
        ELSE 0 
    END) AS q4_2024_revenue,
    
    -- Total revenue
    SUM(oi.quantity * oi.price) AS total_revenue,
    
    -- Customer tier breakdown
    COUNT(DISTINCT CASE WHEN c.loyalty_tier = 'Platinum' THEN c.customer_id END) AS platinum_customers,
    COUNT(DISTINCT CASE WHEN c.loyalty_tier = 'Gold' THEN c.customer_id END) AS gold_customers,
    COUNT(DISTINCT CASE WHEN c.loyalty_tier = 'Silver' THEN c.customer_id END) AS silver_customers,
    
    -- Performance indicators
    CASE
        WHEN SUM(oi.quantity * oi.price) > 100000 THEN 'Top Category'
        WHEN SUM(oi.quantity * oi.price) > 50000 THEN 'Strong Performer'
        WHEN SUM(oi.quantity * oi.price) > 10000 THEN 'Average'
        ELSE 'Underperforming'
    END AS performance_tier,
    
    -- Growth indicator (Q4 vs Q1)
    CASE
        WHEN SUM(CASE WHEN QUARTER(o.order_date) = 4 THEN oi.quantity * oi.price ELSE 0 END) >
             SUM(CASE WHEN QUARTER(o.order_date) = 1 THEN oi.quantity * oi.price ELSE 0 END) * 1.2
        THEN 'Strong Growth'
        WHEN SUM(CASE WHEN QUARTER(o.order_date) = 4 THEN oi.quantity * oi.price ELSE 0 END) >
             SUM(CASE WHEN QUARTER(o.order_date) = 1 THEN oi.quantity * oi.price ELSE 0 END)
        THEN 'Moderate Growth'
        ELSE 'Declining'
    END AS trend

FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
    AND YEAR(o.order_date) = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Advanced CASE Techniques:

✅ Pivot-style reporting with multiple CASE in SUM
✅ Conditional aggregation by tier
✅ Nested CASE for complex classifications
✅ Quarter-over-quarter comparisons
✅ Multiple performance indicators
💡 This pattern powers real business dashboards!

📝 Key Takeaways

CASE expressions provide if-then-else logic in SQL
Simple CASE checks equality, Searched CASE handles any condition
CASE evaluates conditions sequentially (first TRUE wins)
ELSE clause provides default value (optional but recommended)
Use CASE in SELECT, WHERE, ORDER BY, and aggregations
CASE enables custom categorization and labeling
Create pivot-style reports with CASE in aggregations
Nested CASE allows complex multi-factor decisions
Use CASE for conditional counting and summing
Order conditions from most to least likely for performance
CASE with NULL requires IS NULL/IS NOT NULL
Use CASE in ORDER BY for custom sort orders
Simple CASE is slightly faster than Searched CASE
CASE expressions make queries more readable and maintainable
Essential for business logic and data transformation