MySQL CASE Statement

MySQL CASE Statement

The CASE statement in MySQL is used for conditional logic. It evaluates a series of conditions and returns a value when the first condition is met. If no conditions are true, an optional ELSE value is returned. The CASE statement can be used in SQL queries, stored procedures, and functions.

Syntax

Simple CASE

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

• expression: The value or column being compared.
• value1, value2, etc.: The values to compare the expression against.
• result1, result2, etc.: The values returned when a match is found.
• default_result: The value returned if no match is found (optional).

Searched CASE

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

• condition1, condition2, etc.: Conditions evaluated in sequence.
• result1, result2, etc.: The values returned when a condition evaluates to true.
• default_result: The value returned if no conditions are true (optional).

Key Features

1. Flexibility: Can evaluate expressions or conditions.
2. Return Type: The CASE statement returns a single value.
3. Optional ELSE: If ELSE is omitted and no condition matches, NULL is returned.
4. Usable in Queries: Commonly used in SELECT, WHERE, ORDER BY, and other SQL clauses.

Examples

1. Simple CASE in SELECT

SELECT 
    employee_id,
    first_name,
    CASE department_id
        WHEN 1 THEN 'HR'
        WHEN 2 THEN 'Finance'
        WHEN 3 THEN 'Engineering'
        ELSE 'Unknown'
    END AS department_name
FROM employees;

Result

employee_id	first_name	department_name
101	John	HR
102	Sarah	Finance
103	Mike	Unknown

2. Searched CASE in SELECT

SELECT 
    employee_id,
    first_name,
    salary,
    CASE
        WHEN salary > 100000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

Result

employee_id	first_name	salary	salary_category
101	John	120000	High
102	Sarah	75000	Medium
103	Mike	40000	Low

3. Using CASE in WHERE

SELECT * 
FROM employees
WHERE 
    CASE 
        WHEN department_id = 1 THEN salary > 50000
        WHEN department_id = 2 THEN salary > 70000
        ELSE salary > 30000
    END;

4. CASE in ORDER BY

SELECT 
    product_name,
    price,
    CASE
        WHEN price > 100 THEN 'High'
        WHEN price BETWEEN 50 AND 100 THEN 'Medium'
        ELSE 'Low'
    END AS price_category
FROM products
ORDER BY 
    CASE
        WHEN price > 100 THEN 1
        WHEN price BETWEEN 50 AND 100 THEN 2
        ELSE 3
    END;

Result

The rows will be sorted as High → Medium → Low.

5. CASE with Aggregation

SELECT 
    CASE
        WHEN department_id = 1 THEN 'HR'
        WHEN department_id = 2 THEN 'Finance'
        ELSE 'Others'
    END AS department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY 
    CASE
        WHEN department_id = 1 THEN 'HR'
        WHEN department_id = 2 THEN 'Finance'
        ELSE 'Others'
    END;

Result

department	total_employees
HR	5
Finance	8
Others	12

Key Notes

1. Default Value: If no condition matches and no ELSE is provided, NULL is returned.
2. Nested CASE: You can nest CASE statements to handle complex logic.

   CASE
       WHEN condition1 THEN 
           CASE
               WHEN sub_condition1 THEN result1
               ELSE result2
           END
       ELSE result3
   END
   

3. NULL Handling: Be cautious when comparing NULL values. Use IS NULL instead of = for comparisons.

Practical Use Cases

1. Categorize Data: Categorizing employees, products, or transactions based on conditions.
2. Conditional Aggregation: Applying conditional logic within aggregation functions like SUM or COUNT.
3. Dynamic Filtering: Writing flexible WHERE clauses for queries.

Performance Tips

1. Index Usage: Avoid using CASE in WHERE or JOIN clauses when possible, as it can prevent MySQL from using indexes efficiently.
2. Simplify Logic: For complex CASE statements, try to break them into smaller pieces or use temporary tables for better readability.

Conclusion

The CASE statement is a versatile tool for handling conditional logic directly within SQL. It simplifies complex queries and enhances their readability and functionality. Whether you're categorizing data or applying dynamic conditions, CASE provides a clean and powerful solution.

Let me know if you'd like further examples or assistance!