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.