MySQL : CASE Statement

In MySQL, the CASE statement allows you to perform conditional logic within a SQL query. It's useful for tasks like data transformation, categorization, and generating calculated fields based on conditions.

Here's the basic syntax for a simple CASE statement:

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

And here's the syntax for a searched CASE statement, which evaluates each condition independently:

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

• condition1, condition2, etc.: These are the conditions that are evaluated. If a condition evaluates to true, the corresponding result is returned.
• result1, result2, etc.: These are the values returned if the corresponding condition evaluates to true.
• default_result: This is the value returned if none of the conditions are true (optional).

Here's an example to illustrate the usage of the CASE statement:

Suppose you have a table named employees with columns employee_id, first_name, last_name, and salary. You want to create a new column called salary_category, which categorizes employees based on their salary into three categories: "Low", "Medium", and "High".

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

In this example, the CASE statement categorizes employees based on their salary into "Low", "Medium", or "High" categories. The result set includes the original columns from the employees table along with the newly created salary_category column.