How do you use the CASE statement in MySQL, and what are its use cases?

The ‘CASE‘ statement in MySQL is a conditional expression that allows you to perform different actions based on different conditions. It is often used as a replacement for the ‘IF‘ statement when more than two conditions are involved. The syntax for the ‘CASE‘ statement is as follows:

CASE case_value
    WHEN when_value THEN result
    [WHEN when_value THEN result ...]
    [ELSE else_result]
END;

Here, ‘case_value‘ is the value being evaluated, ‘when_value‘ is the value being tested against, ‘result‘ is the value returned if the condition is true, and ‘else_result‘ is the value returned if none of the conditions are true. The ‘CASE‘ statement can evaluate multiple conditions by using multiple ‘WHEN‘ clauses.

Let’s take a look at an example of how to use the ‘CASE‘ statement in MySQL. Suppose we have a table named ‘employees‘ with the following columns: ‘id‘, ‘name‘, ‘salary‘, and ‘department‘.

SELECT name, salary,
  CASE
    WHEN salary >= 5000 THEN 'High'
    WHEN salary >= 3000 THEN 'Medium'
    ELSE 'Low'
  END AS 'Salary Band'
FROM employees;

In this query, we are selecting the ‘name‘, ‘salary‘, and a ‘Salary Band‘ that will be calculated using the ‘CASE‘ statement. If an employee’s salary is greater than or equal to 5000, the ‘Salary Band‘ will be ‘High‘. If it is between 3000 and 4999, it will be ‘Medium‘. Otherwise, it will be ‘Low‘.

Another use case for the ‘CASE‘ statement is to update a column in a table based on certain conditions. For example, let’s say we want to update the ‘department‘ column of the ‘employees‘ table based on their salary. Employees with a salary greater than or equal to 5000 will be assigned to the ‘Management‘ department, while employees with a salary between 3000 and 4999 will be assigned to the ‘Staff‘ department. Employees with a salary less than 3000 will be assigned to the ‘Entry Level‘ department.

UPDATE employees
SET department = CASE
    WHEN salary >= 5000 THEN 'Management'
    WHEN salary >= 3000 THEN 'Staff'
    ELSE 'Entry Level'
  END;

In this query, we are updating the ‘department‘ column of the ‘employees‘ table using the ‘CASE‘ statement. If an employee’s salary is greater than or equal to 5000, they will be assigned to the ‘Management‘ department. If it is between 3000 and 4999, they will be assigned to the ‘Staff‘ department. Otherwise, they will be assigned to the ‘Entry Level‘ department.

In conclusion, the ‘CASE‘ statement in MySQL is a powerful tool that can be used to perform different actions based on different conditions. Its use cases include selecting calculated values in a query and updating columns in a table based on certain conditions.