MySQL CASE

In MySQL, the CASE statement is a powerful conditional expression that allows you to perform conditional logic within SQL queries. It provides a way to perform different actions based on different conditions, similar to the IF-THEN-ELSE structure in other programming languages. The CASE statement can be used in various SQL clauses, including SELECT, WHERE, ORDER BY, and HAVING.

Syntax

The basic syntax of the CASE statement looks like this:

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

Here’s a breakdown of the components:

CASE: The keyword that starts the CASE statement.
WHEN condition THEN result: Evaluates the specified condition and returns the corresponding result if the condition is true.
ELSE: An optional keyword that provides a default result if none of the preceding conditions are true.
END: The keyword that signifies the end of the CASE statement.

Example

Let’s look at an example to illustrate how the CASE statement can be used in a SELECT statement:

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

In this example, the CASE statement categorizes employees based on their salary into three categories: ‘High Salary,’ ‘Medium Salary,’ and ‘Low Salary.’

You can also use the CASE statement in the WHERE clause for filtering:

SELECT
    product_name,
    unit_price,
    CASE
        WHEN unit_price > 100 THEN 'Expensive'
        ELSE 'Affordable'
    END AS price_category
FROM
    products
WHERE
    CASE
        WHEN unit_price > 100 THEN 1
        ELSE 0
    END = 1;

This query selects products and categorizes them based on their unit price, filtering only those considered ‘Expensive.’

You can also use the CASE statement in conjunction with aggregate functions. For example:

SELECT
    department_id,
    AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) AS avg_high_salary,
    AVG(CASE WHEN salary <= 50000 THEN salary ELSE NULL END) AS avg_low_salary
FROM
    employees
GROUP BY
    department_id;

In this case, the CASE statement is used within the AVG function to calculate the average salary for employees with salaries greater than 50,000 (avg_high_salary) and those with salaries less than or equal to 50,000 (avg_low_salary).

The CASE statement can be a valuable tool for handling conditional logic directly within SQL queries, making it easier to transform and analyze data based on specific conditions.