MySQL CASE function

The MySQL CASE function is a powerful function used for conditional logic within SQL queries. It allows you to perform conditional operations based on specified conditions and return different values depending on whether the conditions are met or not. The CASE function is often used in conjunction with the SELECT statement to create more flexible and dynamic queries.

Syntax

The basic syntax of the CASE function in MySQL is as follows:

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

CASE: Keyword to start the CASE expression.
WHEN condition THEN result: Specifies a condition and the result to be returned if that condition is true.
ELSE default_result: Specifies the result to be returned if none of the previous conditions are true.
END: Keyword to end the CASE expression.

Example

Let’s consider a hypothetical scenario where we have a table named employees with columns employee_id, first_name, last_name, and salary. We want to create a new column that categorizes employees based on their salary into different salary ranges.

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary >= 50000 AND salary < 70000 THEN 'Low Range'
        WHEN salary >= 70000 AND salary < 90000 THEN 'Mid Range'
        WHEN salary >= 90000 THEN 'High Range'
        ELSE 'Unknown'
    END AS salary_range
FROM
    employees;

In this example, the CASE function is used to categorize employees into different salary ranges. The conditions specify the salary ranges, and the corresponding results indicate the category for each employee. The ELSE clause provides a default result in case none of the specified conditions is met.

Nested CASE Statements

You can also nest CASE statements to create more complex conditional logic. Here’s a simple example:

SELECT
    column1,
    column2,
    CASE
        WHEN condition1 THEN
            CASE
                WHEN nested_condition1 THEN result1
                WHEN nested_condition2 THEN result2
                ELSE nested_default_result
            END
        WHEN condition2 THEN result3
        ELSE default_result
    END AS final_result
FROM
    your_table;

The nested CASE statements allow you to handle multiple levels of conditions and provide different results based on various criteria.

In summary, the CASE function in MySQL is a versatile tool for implementing conditional logic in SQL queries, allowing you to customize query results based on specific conditions.