How does MySQL CASE work?

The MySQL CASE works like a switch statement. The syntax of CASE is as follows −

Case 1 − Compare Statement

Case
when anyCompareStatement then value1
when anyCompareStatement then value2
.
.
N
else anyValue
end as anyVariableName;

Case 2 − Conditions

The second syntax can be used when you are selecting only one column. The syntax is as follows −

case yourColumnName
when condition1 then result1
when condition1 then result2
.
.
N
else anyValue
end;

To understand the above concept, let us use select statement.

Case 1

The query is as follows −

mysql> select
   -> case when 45 < 55 then '55 is greater than 45'
   -> when 10!=11 then '10 is not equal to 11'
   -> else 'Default case'
   -> end as Result;

Output

+-----------------------+
| Result                |
+-----------------------+
| 55 is greater than 45 |
+-----------------------+
1 row in set (0.00 sec)

Case 2

Let us create a table to understand the second syntax.

The query to create a table is as follows −

mysql> create table CaseDemo
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.93 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into CaseDemo values(1,'John');
Query OK, 1 row affected (0.43 sec)

mysql> insert into CaseDemo values(2,'Sam');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from CaseDemo;

Output

+------+------+
| Id   | Name |
+------+------+
|    1 | John |
|    2 | Sam  |
+------+------+
2 rows in set (0.00 sec)

Here is the second query of CASE that can be used to work with only one value. The query is as follows −

mysql> select case name
-> when 'John' then 'His name is John'
-> when 'Sam' then 'His name is Sam'
-> else -1
-> end as Result
-> from CaseDemo;

Output

+------------------+
| Result           |
+------------------+
| His name is John |
| His name is Sam  |
+------------------+
2 rows in set (0.00 sec)