MySQL CASE Expression

The CASE expression in MySQL is a conditional expression that allows you to return different values based on conditions, similar to IF-ELSE logic.

1. MySQL CASE Syntax

Simple CASE Expression

Used when comparing a column/variable to multiple values.

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

Searched CASE Expression

Used when evaluating complex conditions.

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

2. Using CASE in SELECT Statement

Example 1: Categorizing Employees by Salary

SELECT name, salary,
    CASE 
        WHEN salary >= 10000 THEN 'High'
        WHEN salary >= 5000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

✅ Output:

name	salary	salary_category
John	12000	High
Alice	7000	Medium
Bob	3000	Low

3. Using CASE in ORDER BY

Example 2: Ordering Products Based on Stock Status

SELECT name, stock, 
    CASE 
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products
ORDER BY 
    CASE 
        WHEN stock = 0 THEN 1
        WHEN stock < 10 THEN 2
        ELSE 3
    END;

✅ Orders products with out-of-stock items first, low-stock next, and in-stock last.

4. Using CASE in UPDATE Statement

Example 3: Updating Employee Bonus Based on Performance

UPDATE employees
SET bonus = 
    CASE 
        WHEN performance_rating = 'Excellent' THEN salary * 0.20
        WHEN performance_rating = 'Good' THEN salary * 0.10
        ELSE salary * 0.05
    END;

✅ Assigns a bonus percentage based on employee performance.

5. Using CASE in GROUP BY

Example 4: Counting Employees in Different Age Groups

SELECT 
    CASE 
        WHEN age < 25 THEN 'Young'
        WHEN age BETWEEN 25 AND 40 THEN 'Mid-age'
        ELSE 'Senior'
    END AS age_group,
    COUNT(*) AS total
FROM employees
GROUP BY age_group;

✅ Output:

age_group	total
Young	5
Mid-age	12
Senior	8

6. Using CASE with Aggregate Functions

Example 5: Calculating Total Sales for Different Categories

SELECT 
    SUM(CASE WHEN category = 'Electronics' THEN sales ELSE 0 END) AS electronics_sales,
    SUM(CASE WHEN category = 'Clothing' THEN sales ELSE 0 END) AS clothing_sales
FROM sales;

✅ Calculates total sales per category.

7. Using CASE in HAVING Clause

Example 6: Filtering Customers Based on Order Value

SELECT customer_id, SUM(order_value) AS total_spent
FROM orders
GROUP BY customer_id
HAVING 
    CASE 
        WHEN SUM(order_value) > 500 THEN 1
        ELSE 0
    END = 1;

✅ Filters customers who spent more than $500.

8. MySQL CASE with NULL Values

Example 7: Handling NULL in CASE

SELECT name, 
    CASE 
        WHEN email IS NULL THEN 'No Email'
        ELSE email
    END AS email_status
FROM customers;

✅ Replaces NULL values with "No Email" in the result.

9. Conclusion

Use Case	Example
Categorizing Data	`SELECT name, CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END FROM employees;`
Conditional Sorting	`ORDER BY CASE WHEN stock = 0 THEN 1 ELSE 2 END;`
Updating Data Based on Condition	`UPDATE employees SET bonus = CASE WHEN rating = 'A' THEN 1000 ELSE 500 END;`
Filtering with Aggregate Functions	`HAVING CASE WHEN SUM(sales) > 500 THEN 1 ELSE 0 END = 1;`

🚀 Master MySQL CASE expressions to write dynamic queries!

Souy Soeng

MySQL CASE Expression