MySQL CASE

Adding conditional logic to your SQL queries

🔀 What is CASE?

The CASE statement adds if-then-else logic to SQL queries. It evaluates conditions and returns different values based on which condition is true, similar to if-else statements in programming languages, making your queries more dynamic and flexible. 


-- Categorize products by price
SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-Range'
        ELSE 'Premium'
    END AS price_category
FROM products;

Output:

product_name price price_category
Mouse 25.99 Budget
Keyboard 89.99 Mid-Range

Key CASE Concepts

📝

Simple CASE

Compare one expression to values 

CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
END
🔍

Searched CASE

Evaluate multiple conditions 

CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
END
🎯

In SELECT

Create calculated columns 

SELECT name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
    END AS grade
FROM students;
🔄

In ORDER BY

Custom sorting logic 

ORDER BY
    CASE priority
        WHEN 'High' THEN 1
        WHEN 'Medium' THEN 2
        ELSE 3
    END

🔹 Simple CASE Statement

The simple CASE compares one expression against multiple values. It's clean and readable when you're checking a single column against specific values, like converting status codes to readable labels or mapping abbreviations to full names. 

-- Convert status codes to readable text
SELECT 
    order_id,
    customer_name,
    CASE status
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS order_status
FROM orders;

Output:

order_id customer_name order_status
1001 John Doe Shipped

🔹 Searched CASE Statement

The searched CASE evaluates multiple different conditions using WHEN clauses. This is more flexible than simple CASE because each condition can check different columns or use complex expressions with AND, OR, and comparison operators for sophisticated logic. 

-- Categorize employees by salary range
SELECT 
    employee_name,
    salary,
    CASE
        WHEN salary < 30000 THEN 'Entry Level'
        WHEN salary >= 30000 AND salary < 60000 THEN 'Mid Level'
        WHEN salary >= 60000 AND salary < 100000 THEN 'Senior Level'
        ELSE 'Executive Level'
    END AS salary_grade
FROM employees;

Output:

employee_name salary salary_grade
Alice Smith 45000 Mid Level

🔹 CASE in UPDATE Statements

Use CASE in UPDATE statements to set different values based on conditions. This lets you update multiple rows with different values in a single query, making bulk updates more efficient than running separate UPDATE statements for each condition. 

-- Apply different discounts based on customer type
UPDATE products
SET price = CASE
    WHEN category = 'Electronics' THEN price * 0.90
    WHEN category = 'Clothing' THEN price * 0.80
    WHEN category = 'Books' THEN price * 0.85
    ELSE price * 0.95
END
WHERE on_sale = 1;

Result:

Query OK, 342 rows affected (0.08 sec)

🔹 CASE with Aggregate Functions

Combine CASE with aggregate functions like COUNT, SUM, or AVG to create conditional aggregations. This powerful technique lets you count or sum only rows that meet specific criteria, perfect for creating pivot-style reports or conditional statistics. 

-- Count orders by status
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;

Output:

customer_id total_orders completed_orders pending_orders cancelled_orders
101 15 12 2 1

🔹 Practical Examples

Real-world applications of CASE statements in everyday database tasks. These examples demonstrate how to assign grades, calculate bonuses, prioritize tasks, and format output dynamically based on your data values and business rules. 

-- Assign letter grades
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 exam_results;

-- Calculate bonus based on performance
SELECT 
    employee_name,
    salary,
    CASE
        WHEN performance_rating = 'Excellent' THEN salary * 0.15
        WHEN performance_rating = 'Good' THEN salary * 0.10
        WHEN performance_rating = 'Average' THEN salary * 0.05
        ELSE 0
    END AS bonus
FROM employees;

-- Custom sorting with CASE
SELECT * FROM tasks
ORDER BY
    CASE priority
        WHEN 'Critical' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
    END,
    due_date;

🧠 Test Your Knowledge

What does the ELSE clause do in a CASE statement?