CASE WHEN in MySQL

In this tutorial, we aim at understanding how to use the CASE WHEN statement in a MySQL database.

Businesses and organizations dealing with large amounts of data need to filter the data based on certain conditions. And if there are multiple conditions, it becomes difficult for the programmer to write an efficient query that can quickly retrieve data.

MySQL helps us perform this operation with the help of the CASE WHEN statement.

The CASE WHEN statement is useful and employed in all workplaces dealing with data filtering in MySQL. Let us see this method in action.

But before we begin, let us create a dummy dataset by creating a table, student_details with a few rows.

-- 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,"Preet","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table 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 above code would give the following output:

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

As we have our table set up, let us filter this data using the CASE WHEN statement.

CASE WHEN in MySQL

As mentioned above, the CASE WHEN statement helps us fetch values that meet the condition specified in its expression.

Here is the basic syntax of the CASE WHEN statement:

CASE
    WHEN condition_1 THEN output_1
    WHEN condition_2 THEN output_2
    ELSE output_3
END;

The aforementioned code returns the output_1 when the condition_1 is satisfied, output_2 when the condition_2 is satisfied, and output_3 when the neither of condition_1 and condition_2 are satisfied.

Now, let us filter the student_details table based on the stu_id. When the stu_id is less than or equal to three, we wish to print student with smaller id, and when the stu_id is greater than three, we print student with greater id.

We can perform this operation with the following code.

SELECT stu_id, stu_firstName,
CASE
    WHEN stu_id > 3 THEN 'student with greater id'
    ELSE 'student with smaller id'
END as case_result
FROM student_details;

The output of the aforementioned query is as follows.

stu_id	stu_firstName	case_result
1		Preet			student with smaller id
2		Rich			student with smaller id
3		Veron			student with smaller id
4		Preet			student with greater id
5		Hash			student with greater id
6		Sachin			student with greater id
7		David			student with greater id

As we can see in the aforementioned code block, students with stu_id greater than three get student with greater id as the case result. Otherwise, we get student with smaller id as the case result.

Thus, with the help of the CASE WHEN statement, we can efficiently go through different conditions and find matching data from a table in MySQL.