CASE Expressions

Basic CASE Expression

The CASE expression provides if-then-else logic in SQL. It evaluates conditions and returns a value based on the first matching condition.

Basic Syntax

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Sample Data

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10,2),
    status VARCHAR(20),
    priority INT
);

INSERT INTO orders (customer_id, total, status, priority)
VALUES
    (1, 250.00, 'pending', 1),
    (2, 1500.00, 'processing', 2),
    (3, 75.00, 'shipped', 3),
    (4, 3200.00, 'delivered', 1),
    (5, 450.00, 'pending', 2);

Basic CASE Examples

-- Simple CASE with column value
SELECT 
    order_id,
    total,
    status,
    CASE status
        WHEN 'pending' THEN 'Order Received'
        WHEN 'processing' THEN 'Being Prepared'
        WHEN 'shipped' THEN 'In Transit'
        WHEN 'delivered' THEN 'Completed'
        ELSE 'Unknown Status'
    END AS status_description
FROM orders;

-- CASE with priority levels
SELECT 
    order_id,
    priority,
    CASE priority
        WHEN 1 THEN 'High'
        WHEN 2 THEN 'Medium'
        WHEN 3 THEN 'Low'
        ELSE 'Normal'
    END AS priority_level
FROM orders;

-- CASE in ORDER BY
SELECT order_id, total, 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;

Basic CASE Rules

1. Expression is evaluated once - The expression after CASE is compared to each WHEN value
2. Comparison is equality - Uses = comparison
3. Returns first match - Stops at the first matching WHEN
4. ELSE is optional - Returns NULL if no match and no ELSE

-- CASE without ELSE (returns NULL if no match)
SELECT 
    order_id,
    CASE status
        WHEN 'pending' THEN 'Needs Attention'
        WHEN 'shipped' THEN 'Track This'
    END AS action_required
FROM orders;

Basic CASE is simple and readable when comparing a single column to specific values.

Searched CASE Expression

A searched CASE allows you to use different conditions in each WHEN clause. It's more flexible than basic CASE.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Searched CASE Examples

-- Categorize orders by total amount
SELECT 
    order_id,
    total,
    CASE
        WHEN total < 100 THEN 'Small Order'
        WHEN total BETWEEN 100 AND 500 THEN 'Medium Order'
        WHEN total BETWEEN 501 AND 1000 THEN 'Large Order'
        WHEN total > 1000 THEN 'Bulk Order'
        ELSE 'Unknown'
    END AS order_category
FROM orders;

-- Customer tier based on multiple conditions
SELECT 
    customer_id,
    total,
    CASE
        WHEN total >= 2000 THEN 'Platinum'
        WHEN total >= 1000 THEN 'Gold'
        WHEN total >= 500 THEN 'Silver'
        WHEN total >= 100 THEN 'Bronze'
        ELSE 'Basic'
    END AS customer_tier
FROM orders;

-- Conditional logic with AND/OR
SELECT 
    order_id,
    status,
    priority,
    CASE
        WHEN status = 'pending' AND priority = 1 THEN 'Urgent Processing'
        WHEN status = 'pending' AND priority > 1 THEN 'Normal Processing'
        WHEN status = 'shipped' THEN 'Track and Notify'
        WHEN status = 'delivered' THEN 'Request Review'
        ELSE 'Standard Handling'
    END AS action_required
FROM orders;

Searched CASE with NULLs

-- Handle NULL values
SELECT 
    order_id,
    CASE
        WHEN total IS NULL THEN 'Amount Not Set'
        WHEN total = 0 THEN 'Free Order'
        WHEN total > 0 THEN CONCAT('$', total)
    END AS total_display
FROM orders;

-- NULLIF to prevent division by zero
SELECT 
    order_id,
    total,
    CASE
        WHEN quantity > 0 THEN total / quantity
        ELSE 0
    END AS unit_price
FROM orders;

Searched CASE in Different Clauses

-- In WHERE clause (rare but useful)
SELECT * FROM orders
WHERE 
    CASE
        WHEN status = 'pending' THEN priority
        ELSE 1
    END <= 2;

-- In UPDATE statement
UPDATE orders
SET status = 
    CASE
        WHEN total > 1000 THEN 'priority'
        WHEN total > 500 THEN 'standard'
        ELSE 'economy'
    END
WHERE status = 'pending';

Searched CASE Best Practices

1. Order matters - Conditions are evaluated top to bottom
2. Be specific first - More specific conditions should come before general ones
3. Always include ELSE - Makes the intent clear
4. Use for complex logic - When basic CASE isn't flexible enough

-- Good: Specific to general
SELECT 
    total,
    CASE
        WHEN total >= 10000 THEN 'Enterprise'
        WHEN total >= 1000 THEN 'Corporate'
        WHEN total >= 100 THEN 'Business'
        ELSE 'Individual'
    END AS tier
FROM orders;

-- Bad: General to specific (never matches)
SELECT 
    total,
    CASE
        WHEN total >= 0 THEN 'Individual'  -- Always matches!
        WHEN total >= 100 THEN 'Business'
        WHEN total >= 1000 THEN 'Corporate'
        WHEN total >= 10000 THEN 'Enterprise'
    END AS tier
FROM orders;

Searched CASE is the most flexible form and handles complex conditional logic.

Using CASE in Different Contexts

The CASE expression is versatile and can be used in SELECT, ORDER BY, GROUP BY, and other clauses.

CASE in SELECT

-- Conditional column creation
SELECT 
    order_id,
    total,
    CASE
        WHEN total >= 1000 THEN 'Yes'
        ELSE 'No'
    END AS is_large_order,
    CASE
        WHEN status = 'delivered' THEN 'Complete'
        WHEN status = 'cancelled' THEN 'Cancelled'
        ELSE 'In Progress'
    END AS order_status
FROM orders;

-- Pivoting data with CASE
SELECT 
    customer_id,
    SUM(CASE WHEN status = 'pending' THEN total ELSE 0 END) AS pending_total,
    SUM(CASE WHEN status = 'processing' THEN total ELSE 0 END) AS processing_total,
    SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped_total,
    SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) AS delivered_total
FROM orders
GROUP BY customer_id;

CASE in ORDER BY

-- Custom sort order
SELECT order_id, status, total
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,
    total DESC;

-- Sort by priority then by date
SELECT * FROM orders
ORDER BY 
    CASE
        WHEN priority = 1 THEN 0
        WHEN priority = 2 THEN 1
        ELSE 2
    END,
    created_at DESC;

CASE in GROUP BY

-- Create custom groups
SELECT 
    CASE
        WHEN total < 100 THEN 'Under $100'
        WHEN total BETWEEN 100 AND 500 THEN '$100-$500'
        WHEN total BETWEEN 501 AND 1000 THEN '$501-$1000'
        ELSE 'Over $1000'
    END AS order_range,
    COUNT(*) AS order_count,
    AVG(total) AS avg_total
FROM orders
GROUP BY 
    CASE
        WHEN total < 100 THEN 'Under $100'
        WHEN total BETWEEN 100 AND 500 THEN '$100-$500'
        WHEN total BETWEEN 501 AND 1000 THEN '$501-$1000'
        ELSE 'Over $1000'
    END;

-- Conditional aggregation
SELECT 
    customer_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN total > 500 THEN 1 ELSE 0 END) AS large_orders,
    SUM(CASE WHEN total <= 500 THEN 1 ELSE 0 END) AS small_orders
FROM orders
GROUP BY customer_id;

CASE in INSERT/UPDATE

-- Conditional INSERT
INSERT INTO order_summary (customer_id, order_type, total)
SELECT 
    customer_id,
    CASE
        WHEN total >= 1000 THEN 'bulk'
        WHEN total >= 100 THEN 'standard'
        ELSE 'small'
    END,
    total
FROM orders;

-- Conditional UPDATE
UPDATE orders
SET discount = 
    CASE
        WHEN total >= 5000 THEN total * 0.15
        WHEN total >= 1000 THEN total * 0.10
        WHEN total >= 500 THEN total * 0.05
        ELSE 0
    END
WHERE status = 'pending';

CASE in HAVING

-- Filter groups based on conditions
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    AVG(total) AS avg_total
FROM orders
GROUP BY customer_id
HAVING 
    CASE
        WHEN AVG(total) > 1000 THEN TRUE
        WHEN COUNT(*) > 5 THEN TRUE
        ELSE FALSE
    END;

Nested CASE

-- Complex nested logic
SELECT 
    order_id,
    CASE
        WHEN status = 'cancelled' THEN 'Cancelled'
        ELSE 
            CASE
                WHEN total > 2000 THEN 'Priority Shipping'
                WHEN total > 500 THEN 'Standard Shipping'
                ELSE 'Economy Shipping'
            END
    END AS shipping_method
FROM orders;

-- Equivalent with cleaner syntax
SELECT 
    order_id,
    CASE
        WHEN status = 'cancelled' THEN 'Cancelled'
        WHEN total > 2000 THEN 'Priority Shipping'
        WHEN total > 500 THEN 'Standard Shipping'
        ELSE 'Economy Shipping'
    END AS shipping_method
FROM orders;

CASE is one of the most powerful SQL expressions for implementing conditional logic.

Practical CASE Examples

Here are real-world examples of using CASE expressions effectively.

E-Commerce Examples

-- Product availability status
SELECT 
    product_id,
    name,
    stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity <= 10 THEN 'Low Stock'
        WHEN stock_quantity <= 50 THEN 'In Stock'
        ELSE 'Well Stocked'
    END AS availability
FROM products;

-- Price comparison
SELECT 
    p.name,
    p.price AS current_price,
    p.original_price,
    CASE
        WHEN p.original_price IS NULL THEN 'N/A'
        WHEN p.price < p.original_price * 0.5 THEN '50%+ Off'
        WHEN p.price < p.original_price * 0.75 THEN '25-50% Off'
        WHEN p.price < p.original_price THEN 'On Sale'
        ELSE 'Regular Price'
    END AS discount_status
FROM products p;

HR Examples

-- Employee level based on salary
SELECT 
    emp_name,
    salary,
    CASE
        WHEN salary >= 150000 THEN 'Executive'
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 75000 THEN 'Mid-Level'
        WHEN salary >= 50000 THEN 'Junior'
        ELSE 'Entry Level'
    END AS employee_level
FROM employees;

-- Years of service
SELECT 
    emp_name,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) / 365 AS years_employed,
    CASE
        WHEN DATEDIFF(CURDATE(), hire_date) / 365 >= 10 THEN 'Veteran'
        WHEN DATEDIFF(CURDATE(), hire_date) / 365 >= 5 THEN 'Experienced'
        WHEN DATEDIFF(CURDATE(), hire_date) / 365 >= 2 THEN 'Developing'
        ELSE 'New Hire'
    END AS experience_level
FROM employees;

Reporting Examples

-- Monthly report with CASE
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) AS completed_revenue,
    SUM(CASE WHEN status = 'cancelled' THEN total ELSE 0 END) AS cancelled_revenue,
    COUNT(CASE WHEN total > 1000 THEN 1 END) AS large_orders
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- Pivot table example
SELECT 
    department,
    SUM(CASE WHEN YEAR(hire_date) = 2023 THEN 1 ELSE 0 END) AS hired_2023,
    SUM(CASE WHEN YEAR(hire_date) = 2024 THEN 1 ELSE 0 END) AS hired_2024,
    SUM(CASE WHEN YEAR(hire_date) = 2025 THEN 1 ELSE 0 END) AS hired_2025
FROM employees
GROUP BY department;

Data Cleaning Examples

-- Standardize phone numbers
SELECT 
    name,
    CASE
        WHEN phone LIKE '(%' THEN phone  -- Already formatted
        WHEN LENGTH(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '')) = 10 
            THEN CONCAT('(', SUBSTRING(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', ''), 1, 3), ') ', 
                         SUBSTRING(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', ''), 4, 3), '-', 
                         SUBSTRING(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', ''), 7, 4))
        ELSE phone  -- Can't format
    END AS formatted_phone
FROM contacts;

-- Map codes to descriptions
SELECT 
    order_id,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'C' THEN 'Completed'
        WHEN 'X' THEN 'Cancelled'
        WHEN 'R' THEN 'Returned'
        ELSE 'Unknown'
    END AS status_description
FROM orders_legacy;

Performance Considerations

-- CASE is evaluated for each row
-- For simple conditions, consider other approaches

-- CASE in WHERE (may prevent index usage)
SELECT * FROM orders
WHERE CASE WHEN total > 1000 THEN 'large' ELSE 'small' END = 'large';

-- Better: Direct condition
SELECT * FROM orders WHERE total > 1000;

-- CASE for conditional aggregation (usually efficient)
SELECT 
    customer_id,
    SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) AS completed_total
FROM orders
GROUP BY customer_id;

CASE expressions are essential for implementing business logic directly in SQL queries.