What is MySQL CASE statement?
MySQL CASE statement is a similar switch case where if any condition is ‘true’ then that respective value is returned and it will stop reading further. If all the case conditions are false, then the else part value is returned.
The syntax for CASE Statement.
CASE
WHEN condition_1 THEN value_1
WHEN condition_2 THEN value_2
WHEN condition_n THEN value_n
ELSE default_value
END;
We will see an example of a CASE statement by using the ‘students’ table which has values as follows.
We will assign the grade value to each student based on the marks out of 300. The query will be as follows.
SELECT
roll_num,
name,
concat(cast(marks as char), ‘ / 300’) marks,
marks / 3 AS marks_percentage,
CASE
WHEN marks / 3 >= 70 THEN ‘Distinction’
WHEN marks / 3 < 70 AND marks / 3 >= 60 THEN ‘First Class’
WHEN marks / 3 < 60 AND marks / 3 >= 50 THEN ‘Second Class’
WHEN marks / 3 < 50 AND marks / 3 >= 40 THEN ‘Third Class’
ELSE ‘Failed’
END as Grade
FROM
students;
Output:
If we do not add the else condition in the CASE statement then a null value will be returned if no condition is true.
Then the output will be as follows.
We can also add the case statement on varchar type as follow.
SELECT
roll_num,
name,
marks,
class,
CASE
WHEN gender = ‘M’ THEN ‘Male’
WHEN gender = ‘F’ THEN ‘Female’
ELSE ‘Other’
END as gender
FROM
students;
Output:
We can also use the CASE statement in the ORDER BY clause. For the below example, we will order the records where the length of the name is 3, 4, 5, and ‘default’ we will order them as 1,2,3,4 respectively.
SELECT
*
FROM
students
ORDER BY
CASE
WHEN length(name) <= 3 THEN 1
WHEN length(name) > 3 AND length(name) <= 4 THEN 2
WHEN length(name) > 4 AND length(name) <= 5 THEN 3
ELSE 4
END;
Output:
