MySQL CASE Statement

The CASE statement in MySQL allows you to perform conditional logic within a SQL query. It allows you to return different values based on different conditions.

The basic syntax for the CASE statement in MySQL is as follows:

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

In this syntax, you can have multiple WHEN clauses to specify different conditions and their corresponding results. The ELSE clause is optional and specifies the result to return if none of the conditions are met.

For example, suppose you have a table called employees with columns employee_id, first_name, last_name, and salary, and you want to create a new column that categorizes employees based on their salary as either “Low”, “Medium”, or “High”. You can use the CASE statement as follows:

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

This will return a result set with a new column called salary_category, which categorizes employees based on their salary.

You can also use the CASE statement within an UPDATE statement to update a column based on conditions. The syntax for the CASE statement within an UPDATE statement is as follows:

UPDATE table_name
SET column_name = 
    CASE 
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       ELSE default_result
    END
WHERE condition;

In this syntax, the table_name is the name of the table that you want to update. The column_name is the name of the column that you want to update. The condition specifies which rows to update. The WHEN clauses specify the conditions and their corresponding results. The ELSE clause is optional and specifies the result to update if none of the conditions are met.

For example, suppose you want to update the salary_category column in the employees table based on the salary values. You can use the CASE statement as follows:

UPDATE employees
SET salary_category =
    CASE 
       WHEN salary < 50000 THEN 'Low'
       WHEN salary >= 50000 AND salary < 80000 THEN 'Medium'
       ELSE 'High'
    END;

This will update the salary_category column for all rows in the employees table based on the salary values.