The CASE Statement in MySQL

In this tutorial, we aim at learning how to use the CASE statement in MySQL.

When the first condition is met, the CASE statement proceeds through the criteria and returns a value (like an IF-THEN-ELSE statement). When a condition is true, the program will stop reading and return the result.

It will return the value in the ELSE clause if none of the criteria are true. In case that there is no ELSE portion in the code, the program returns NULL.

The syntax of the case statement can be given as:

case when condition then result_1 else result_2 end;

Let us try to learn more about this statement with an example.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The aforementioned code would give the following output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now, let us aim at printing Yes along with the student’s last name when the student’s first name is Preet; otherwise, we print No.

the CASE Statement in MySQL

As seen in the syntax before, the CASE statement in MySQL requires a condition. This works similar to an IF..ELSE.. statement.

We can make use of the following code example to get the desired result in MySQL:

SELECT stu_lastName,
CASE stu_firstName
    WHEN 'Preet' THEN 'Yes'
    ELSE 'No'
END
AS RESULT
FROM student_details;

The aforementioned code gets the last name of each student along with a new column named result. This result column has Yes if the first name of the student is Preet.

The output of the above code can be visualized as follows.

stu_lastName	RESULT
Sanghavi		Yes
John			No
Brow			No
Jos				No
Shah			No
Parker			No
Miller			No

Similarly, we can use the CASE statement to leverage the data and meet our requirements. An alternative to the CASE statement is the IF statement in MySQL.

Thus, with the help of this tutorial, we can now successfully implement the CASE statement in MySQL.