JIYIK CN >

In this article, we aim to understand how to use CASE WHENstatement in MySQL database.

Businesses and organizations that process large amounts of data need to filter data based on specific conditions. If there are multiple conditions, it is difficult for programmers to write efficient queries to retrieve data quickly.

MySQL helps CASE WHENus do this with the statement.

CASE WHENStatement is very useful in all workplaces dealing with MySQL data filtering. Let's see this method in action.

But before we start, let's student_detailscreate a dummy dataset by creating a table 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 containing the first and last names of students. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code will 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

Now that we have our table set up, let's CASE WHENfilter this data using a SQL statement.

MySQLCASE WHEN

As mentioned above, CASE WHENstatements help us get the values ​​that satisfy the conditions specified in its expression.

This is CASE WHENthe basic syntax of the statement:

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

The above code condition_1returns if is satisfied output_1, condition_2returns if is satisfied output_2, and returns if condition_1and are not satisfied .condition_2output_3

Now, let's stu_idfilter student_detailsthe table based on . stu_idWe want to print when is less than or equal to three, student with small idand stu_idwe print when is greater than three student with large id.

We can do this using 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 above 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 above code block, stu_idstudents with scores greater than 3 will get student with greater idas the case result. Otherwise, we will get id 较小的学生as the case result.

Therefore, CASE WHENwith the help of statement, we can effectively iterate through different conditions and find matching data from the table in MySQL.