If you have a little idea about how if..else..elseif statement works in different programming languages then understanding the MySQL CASE function should be straight-forward.
The CASE..WHEN…THEN..END combination is like the if..else..elseif where conditions are given and the one that turns out TRUE executes its block of code.
As soon as a condition is TRUE, further conditions are not tested. If none of the condition or “case” is True then the ELSE part value is returned.
The syntax and examples below should make things clearer.
Syntax of using MySQL CASE function
The CASE function can be used in two ways in MySQL.
The first way:
CASE value WHEN value_compare THEN statement_list [WHEN value_compare THEN statement_list] ... [ELSE statement_list] END CASE
In this syntax, the case value is compared to the value_compare in WHEN until one of the value is equal.
As soon as a case value is found equal to the WHEN value, the statement in the corresponding THEN clause is executed.
If none of the case value is equal to the WHEN CLAUSE value then ELSE clause statement(s) execute.
It is like the SELECT..CASE statement in different programming languages.
The second way:
CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 WHEN condition_N THEN result_N … ELSE result END
In this syntax, the WHEN clause condition is tested. As soon as a condition evaluates as True, the statement(s) in that WHEN clause is executed. Again, if none of the condition is True in all WHEN clauses then the ELSE clause statement is returned.
An example of using CASE function
In this example, the search condition is used in the CASE function. For that, we have an employee table that stores staff record including their salary (for demo only).
In the first condition, the salary is checked for 4000 or more. The second condition checks salary for 5000 or more and returns the corresponding result as shown in the graphic below:
Query:
SELECT ID, employee_name, CASE WHEN employee_salary >=4000 THEN "Salary is greater or equal to 4000" WHEN employee_salary >=5000 THEN "Salary is greater or equal to 5000" ELSE "Salary is less than 4000" END AS "What Salary?" FROM tst_employees;
The result:
In the above graphic, the first table shows complete data while the other returned after using the CASE function.
Using CASE function with GROUP BY clause
In this query, I am using another table that stores salaries paid to each employee every month. The GROUP BY clause with COUNT function is used to get the number of salaries paid to each employee.
By using the MySQL CASE…WHEN..THEN…END, another column returned with the name “level”.
If salaries paid are three then level is senior, if two then intermediate and if one then junior level.
The query:
SELECT employee_name AS "Employee Name", COUNT( employee_salary_paid ) AS "No. Of Salaries", CASE COUNT( employee_salary_paid ) WHEN 1 THEN "Junior" WHEN 2 THEN "Intermediate" WHEN 3 THEN "Senior" END AS Level FROM tst_emp_paid_sal GROUP BY employee_name
The result:
In the graphic above, you can see the fourth column “Level” with respective value in relation to the CASE statement where I used COUNT( employee_salary_paid ) to provide the value.
For each group result, the CASE statement evaluated the returned value from the COUNT function and the value that equated the WHEN clause is returned.
A more simple example with a table
Well, if things are still not clear then the examples below should make it easy. Now, I am using the CASE function without using any table data for looking it simple.
A static value is assigned in the CASE function and then it is evaluated in the WHEN clauses. Have a look:
SELECT CASE 5 WHEN 1 THEN 'This is Monday' WHEN 2 THEN 'This is Tuesday' WHEN 3 THEN 'This is Wednesday' WHEN 4 THEN 'This is Thursday' WHEN 5 THEN 'This is Friday' WHEN 6 THEN 'This is Saturday' ELSE 'This is Weekend, Hurray!' END AS 'What is Day?';
The result:
This is Friday
Similarly, you may use text:
SELECT CASE 'Bwn' WHEN 'O' THEN 'Orange' WHEN 'R' THEN 'Red' WHEN 'Blk' THEN 'Black' WHEN 'Bwn' THEN 'Brown' ELSE 'White' END AS 'What is Color?';
The output:
Brown
In the real world queries, the source of value most probably is from the table data as shown in the first two queries. This can be simple table column value, or based on aggregated function etc.
An example of using CASE with an UPDATE statement
The following query shows using the CASE function with the UPDATE statement. We will update the employee salary column by using the CASE..WHEN.
The query:
UPDATE `tst_employees` SET `employee_salary` = CASE WHEN employee_salary = 2600 THEN 3000 WHEN employee_salary = 3500 THEN 4000 WHEN employee_salary = 4500 THEN 5000 END WHERE id in (1,2,3,4,5,6,7)
