MySQL | CASE
Start your free 7-days trial now!
MySQL's CASE operator allows us to add multiple conditions to our query and return a value for the first condition met.
Syntax
-- Syntax 1: Return result for first comparison of value = compare_value that is trueSELECT column(s),CASE value WHEN compare_value THEN result1 WHEN compare_value2 THEN result2 ELSE result3ENDFROM table;-- Syntax 2: Return result for first condition that is trueSELECT column(s),CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3ENDFROM table;
In both cases if no comparison/condition is true, the result after ELSE is returned or NULL if there is no ELSE part.
Examples
Consider the following table about students' extracurricular activities:
student_id | club | date_entered |
|---|---|---|
1 | Football | 2016-02-13 |
2 | Boxing | 2016-05-25 |
3 | Apple | 2018-08-17 |
4 | Fishing | 2017-01-01 |
5 | NULL | NULL |
The above sample table can be created using the code here.
Syntax 1
To check whether student_id is 1 and return a string depending on the result of the comparison:
SELECT student_id,CASE student_id WHEN 1 THEN "student1" ELSE "Not student1" END AS 'Case Outcome'FROM extracurricular;
+------------+--------------+| student_id | Case Outcome |+------------+--------------+| 1 | student1 || 2 | Not student1 || 3 | Not student1 || 4 | Not student1 || 5 | Not student1 |+------------+--------------+
For student_id=1 as the comparison is true we return "student1". For all other students, as the comparison is false, we return the ELSE block return value "Not student1".
Syntax 2
To return "Not student1" for all student_id greater than 1:
SELECT student_id,CASE WHEN student_id > 1 THEN "Not student1" ELSE "student1" ENDAS 'Case Outcome'FROM extracurricular;
+------------+--------------+| student_id | Case Outcome |+------------+--------------+| 1 | student1 || 2 | Not student1 || 3 | Not student1 || 4 | Not student1 || 5 | Not student1 |+------------+--------------+
For student_id=1 as the condition is false, we return the ELSE block return value "student1". For all other students, as the condition is true, we return "Not student1".
