CASE Statement in MYSQL

In MySQL, the CASE statement is used for conditional logic within SQL queries, similar to other SQL databases like Oracle. It allows you to perform different operations based on specified conditions.

Here's the basic syntax for the CASE statement in MySQL:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

- condition1, condition2, etc.: These are the conditions to be evaluated.

- result1, result2, etc.: These are the values or expressions to be returned if the corresponding condition is true.

- default_result: This is an optional default value or expression to be returned if none of the conditions are true.

Let's see an example to understand how the CASE statement works in MySQL:

Suppose we have a table named employees with columns name and salary. We want to categorize employees into different salary ranges based on their salary. We'll use the CASE statement for this.

SELECT 

    name,

    salary,

    CASE

        WHEN salary < 50000 THEN 'Low'

        WHEN salary >= 50000 AND salary < 100000 THEN 'Medium'

        ELSE 'High'

    END AS salary_range

FROM 

    employees;

In this query:

- We select the name and salary columns directly.

- We use a CASE statement to categorize employees into different salary ranges based on their salary.

- If an employee's salary is less than 50000, we categorize them as 'Low'.

- If their salary is between 50000 (inclusive) and 100000 (exclusive), we categorize them as 'Medium'.

- For all other cases (salary greater than or equal to 100000), we categorize them as 'High'.

This query demonstrates how you can use the CASE statement to apply conditional logic within a SQL query in MySQL.