SQL CASE Statements - Conditional Logic Made Simple

Introduction

CASE statements are SQL’s way of handling “if-then-else” logic. Think of them as a way to make decisions in your queries, like sorting items into different categories.

Why CASE Statements Matter?

They’re useful when you need to:

• Categorize data
• Create custom labels
• Apply different calculations
• Transform values conditionally

Main Content: Understanding CASE Statements

Let’s use this simple table:

-- Create a simple table
CREATE TABLE products (
    product_name VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Insert sample data
INSERT INTO products VALUES
('Laptop', 1200.00, 5),
('Mouse', 25.00, 50),
('Keyboard', 80.00, 30),
('Monitor', 300.00, 15),
('Headphones', 150.00, 25);

Example 1: Simple CASE Statement

Let’s categorize products by price range:

SELECT 
    product_name,
    price,
    CASE 
        WHEN price >= 1000 THEN 'Premium'
        WHEN price >= 100 THEN 'Regular'
        ELSE 'Budget'
    END as price_category
FROM products
ORDER BY price DESC;

This query:

1. Checks each product’s price
2. Assigns a category based on price
3. Shows products with their categories

Results:

product_name | price    | price_category
-------------|----------|----------------
Laptop       | 1200.00  | Premium
Monitor      | 300.00   | Regular
Headphones   | 150.00   | Regular
Keyboard     | 80.00    | Budget
Mouse        | 25.00    | Budget

Example 2: CASE with Multiple Conditions

Let’s analyze stock levels:

SELECT 
    product_name,
    stock_quantity,
    CASE 
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        WHEN stock_quantity < 30 THEN 'Medium Stock'
        ELSE 'Well Stocked'
    END as stock_status,
    CASE 
        WHEN stock_quantity < 10 THEN 'Order Soon'
        ELSE 'Stock OK'
    END as action_needed
FROM products
ORDER BY stock_quantity;

This query shows:

1. Stock level categories
2. Required actions
3. Multiple CASE statements

Results:

product_name | stock_quantity | stock_status  | action_needed
-------------|----------------|---------------|---------------
Laptop       | 5              | Low Stock     | Order Soon
Monitor      | 15             | Medium Stock  | Stock OK
Headphones   | 25             | Medium Stock  | Stock OK
Keyboard     | 30             | Well Stocked  | Stock OK
Mouse        | 50             | Well Stocked  | Stock OK

Example 3: CASE in Calculations

Let’s calculate adjusted prices:

SELECT 
    product_name,
    price,
    CASE 
        WHEN stock_quantity > 40 THEN price * 0.9  -- 10% discount
        WHEN stock_quantity < 10 THEN price * 1.1  -- 10% markup
        ELSE price
    END as adjusted_price,
    CASE 
        WHEN stock_quantity > 40 THEN '10% Off'
        WHEN stock_quantity < 10 THEN '10% Mark-up'
        ELSE 'Regular Price'
    END as price_status
FROM products
ORDER BY stock_quantity;

This query:

1. Adjusts prices based on stock
2. Shows price adjustment reason
3. Uses CASE for calculations

Results:

product_name | price    | adjusted_price | price_status
-------------|----------|----------------|-------------
Laptop       | 1200.00  | 1320.00       | 10% Mark-up
Monitor      | 300.00   | 300.00        | Regular Price
Headphones   | 150.00   | 150.00        | Regular Price
Keyboard     | 80.00    | 80.00         | Regular Price
Mouse        | 25.00    | 22.50         | 10% Off

Best Practices

1. CASE Structure

   • Order conditions logically
   • Handle all possible cases
   • Include ELSE clause
   • End with END keyword

2. Readability

   • Use proper indentation
   • Keep conditions simple
   • Use meaningful labels
   • Comment complex logic

3. Common Uses

   • Categorization
   • Custom labels
   • Conditional calculations
   • Data transformation

4. Performance

   • Keep conditions simple
   • Order from most to least specific
   • Avoid unnecessary conditions
   • Consider indexing needs

5. Things to Remember

   • CASE evaluates in order
   • Stops at first TRUE condition
   • ELSE handles remaining cases
   • Returns NULL if no ELSE

Remember:

• CASE needs END
• Order matters
• Include ELSE
• Keep it simple

Common Mistakes to Avoid:

1. Missing END keyword
2. Overlapping conditions
3. Missing ELSE clause
4. Too complex conditions

Happy querying! 🚀