MySQL: Case Statement

Case statement in MySQL 

The MySQL case statements are the expressions used for flow control; they help with conditional operations like adding if-else logic. MySQL case statement works similarly to that in other programming languages. There are one or more than one conditions that the MySQL case statement has to go through. The MySQL case statement returns the value as soon as a condition is true. It also means that further reading of conditions is also stopped after that. You can understand it as the simple version of nested if-else statements in different programming languages like Java, C++, etc.

How to use a MySQL case statement?

Let us see the syntax for the MySQL case statement to understand how to use a case statement. They can be two types:

• Simple Case Expression
• Searched Case Expression

Simple Case Expression

Here, we have a value along with the case, which we are comparing and getting different results. The syntax for the simple case expression is:

CASE value
	WHEN value1 THEN result1
	[WHEN value2 THEN result2 …]
	[ELSE else_result]
END

Searched Case Expression

In searched case expression, we have different conditions specified in the WHEN clause, which decides the results. The condition true will govern what will be given as the output. The syntax is:

CASE
	WHEN expression1 THEN result1
	[WHEN expression2 THEN result2 …]
	[ELSE else_result]
END

Why do we need a CASE when we have a WHERE clause?

We can get the same desired output through the where clause, which we find through the case statement. But in the where clause, we can execute only one set of conditions at a time, so to check for multiple sets of conditions, we will have to write multiple statements. This becomes a tedious job. Whereas if we use a case statement, we can check for multiple sets of conditions at once. 

Let us see an example to understand it better. For example, suppose we have a table consisting of the marks of the students and a pass-fail column. Now, we have to fill the pass-fail column according to marks. If the marks are above 33, then PASS or else FAIL.

If we write using a case statement:

UPDATE marks
SET pass-fail = CASE
	WHEN marks > 33 THEN PASS
	ELSE FAIL
END

If we want the same output which we will get from the above block of code, using the where clause, we will have to execute two statements one by one.  

UPDATE marks
SET pass-fail = PASS
WHERE marks > 33 

We must execute a statement separately for the FAIL output.

UPDATE marks
SET pass-fail = FAIL
WHERE marks <= 33 

So, through the above example, we can understand that it becomes easier to write using the MySQL case statement.

Where can we use a case statement?

We can use use the MySQL case statement in many places. Some of the most prominent ones are we can use MySQL case statements with

• Inline Comparator
• Expression in WHEN clause
• UPDATE statements
   

We will provide an example to understand each of the use of MySQL case statements. Let us assume we have a marks table for a class containing roll_no, name, percentage, and grade.

With Inline Comparator

Now, using the case with an inline comparator, through grade, we can set values for another column, namely pass_class. 

If the grade is A+, then distinction. 

If it’s an A, then first class.

If it’s B, then second class. 

If it’s C, then just pass.

If it is any lower than C, then fail. 

The code will look like

SELECT roll_no, name, percentage, grade, 
CASE grade
	WHEN 'A+' THEN 'DISTINCTION'
	WHEN 'A' THEN 'FIRST CLASS'
	WHEN 'B' THEN 'SECOND CLASS'
	WHEN 'C' THEN 'THIRD CLASS'
	ELSE 'FAIL'
END AS pass_class
FROM marks

Output:

With expression in the WHEN clause

By using the MySQL class statements with the expressions in the when clause, we can achieve the same output as above. We can get a new column named pass_class based on the value of the percentage.

If the percentage is between 91 to 100, then DISTINCTION.

If the percentage is between 81 to 90, then FIRST CLASS.

If the percentage is between 71 to 80, then SECOND CLASS.

If the percentage is between 61 to 70, then THIRD CLASS.

If the percentage is less than 61, then FAIL.

The code will look like this:

SELECT roll_no, name, percentage, grade, 
CASE 
	WHEN percentage >= 91 THEN 'DISTINCTION'
	WHEN percentage >= 81 AND percentage <= 90 THEN 'FIRST CLASS'
	WHEN percentage >= 71 AND percentage <= 80 THEN 'SECOND CLASS'
	WHEN percentage >= 61 AND percentage <= 70 THEN 'THIRD CLASS'
	ELSE 'FAIL'
END AS pass_class
FROM marks

Output:

With UPDATE statements

We can update the table by using the MySQL class statements with UPDATE statements. For example, suppose in the above table, the grading system has changed to grade points, and we have to update it. Let the rules be:

If the percentage is between 91 to 100, then 10.0.

If the percentage is between 81 to 90, then 9.0.

If the percentage is between 71 to 80, then 8.0.

If the percentage is between 61 to 70, then 7.0.

If the percentage is less than 61, then 0.

The code for this transformation will be:

UPDATE marks
SET grade = CASE
	WHEN percentage >= 91 THEN ‘10.0’
	WHEN percentage >= 87 AND percentage <= 90 THEN '9.0'
	WHEN percentage >= 71 AND percentage <= 80 THEN '8.0'
	WHEN percentage >= 61 AND percentage <= 70 THEN '7.0'
	ELSE '0'
END

Updated Table:

Through the above examples, we understood the prominent uses of MySQL case statements.

Frequently Asked Questions

What is MySQL?

MySQL is an open-source relational database management system. The SQL in the name stands for Structured Query Language, signifying that it is table-structured.

What is MySQL case statement?

The MySQL case statements are the expressions used for flow control; they help with conditional operations like adding if-else logic.

How to write a MySQL case statement?

We can write the MySQL case statement in two ways: simple case expression, in this expression, the case-comparator is provided inline. The other one is searched case expression. In this expression, the case comparators are provided with the individual when statements.

Why do we need a CASE when we have a WHERE clause?

It becomes easier to write using the MySQL case statement. In the where clause, we can execute only one set of conditions at a time, so to check for multiple sets of conditions, we will have to write multiple statements.  Whereas if we use a case statement, we can check for multiple sets of conditions at once. 

Conclusion

In this blog, we have discussed the MySQL case statements. We first learned what a case statement is, then continued with how to write it and where to use it. Check out our articles if you think this blog has helped you enhance your knowledge and want to learn more. Visit our website to read more such blogs. 

1. MySQL Introduction 
2. MySQL Features 
3. MySQL Data Types 
4. SQL vs. MYSQL 
5. Update Query in MySQL

For placement preparations, you must look at the problems, interview experiences, and interview bundles. Enroll in our courses and refer to the mock tests and problems available; look at the Problem Sheets,  interview experiences, and interview bundle for placement preparations. You can also book an interview session with us.  

Consider our paid courses, however, to give your career a competitive advantage!

Happy Coding!