MySQL CASE Expression

The `CASE` expression in MySQL is used for conditional logic in SQL queries, allowing you to return specific values based on different conditions. It functions similarly to an if-else statement in programming languages.

Usage

The `CASE` expression is utilized to execute conditional logic within SQL queries, primarily in `SELECT`, `UPDATE`, and `ORDER BY` clauses. It evaluates conditions in order and returns a specified value when the first true condition is encountered.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

In this syntax, `CASE` checks each condition sequentially and returns the corresponding result for the first true condition; if none are true, it returns the `ELSE` result.

Examples

1. Basic Case Usage

SELECT product_name,
       CASE
           WHEN stock_quantity > 0 THEN 'In Stock'
           ELSE 'Out of Stock'
       END AS stock_status
FROM products;

This example evaluates the `stock_quantity` and returns 'In Stock' if the quantity is greater than zero, otherwise 'Out of Stock'.

2. Multiple Conditions

SELECT employee_name,
       CASE
           WHEN salary > 50000 THEN 'High Salary'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM employees;

In this query, the `CASE` expression categorizes employees into salary bands, offering more granular control over output labels.

3. Using CASE in ORDER BY

SELECT order_id, order_date
FROM orders
ORDER BY
    CASE
        WHEN order_status = 'Pending' THEN 1
        WHEN order_status = 'Shipped' THEN 2
        ELSE 3
    END;

Here, the `CASE` expression is used to order results based on `order_status`, prioritizing 'Pending', followed by 'Shipped', then any other status.

4. Using CASE in UPDATE

UPDATE employees
SET salary_category = 
    CASE
        WHEN salary > 50000 THEN 'High Salary'
        WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END;

This example shows how `CASE` can be used in an `UPDATE` statement to set the `salary_category` field based on different salary ranges.

Tips and Best Practices

• Ensure completeness. Always include an `ELSE` clause to handle unexpected or null cases, enhancing query robustness.
• Keep conditions simple. Simplify conditions within `CASE` to maintain readability and reduce errors.
• Use appropriate data types. Ensure the data types of results in `THEN` and `ELSE` clauses are consistent to avoid errors.
• Optimize performance. Place the most likely true conditions first to minimize evaluation time and improve query performance.
• SQL Standards. MySQL's `CASE` expression adheres to SQL standards, ensuring compatibility across different SQL databases.