Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Loops in MySQL
3.
Case statement in MySQL 
4.
How to use a MySQL case statement?
4.1.
Simple Case Expression
4.2.
Searched Case Expression
5.
Why do we need a CASE when we have a WHERE clause?
6.
Where can we use a case statement?
6.1.
With Inline Comparator
6.2.
With expression in the WHEN clause
6.3.
With UPDATE statements
7.
Frequently Asked Questions
7.1.
What is MySQL?
7.2.
What is MySQL case statement?
7.3.
How to write a MySQL case statement?
7.4.
Why do we need a CASE when we have a WHERE clause?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

MySQL: Case Statement

gp-icon
DBMS - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

Introduction

Hey Ninjas, since data is very important in this modern world, database management also becomes equally important. One such open-source Database Management System is MySQL. You all would have already read about the basics of MySQL; if not, you can visit MySQL Introduction. Today, we will learn about the MySQL case statement in this article.

Introduction

Let us begin our journey to discover the MySQL case statements by first learning about loops in MySQL. 

Loops in MySQL

Loops in mysql

Just like in other programming languages, loops are used in MySQL. We use loops to execute a set of statements once or more times, depending on the condition. The statements inside the loops are executed repeatedly until the loop is terminated.

The syntax for the loops in MySQL is

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

In the above block of code, the begin_label and end_label are the optional labels at the start and end of the loop, respectively. The statement_list is the set of statements that are repeated until the loop terminates. Each statement in the statement_list is separated by a semicolon “;”, which acts as an ending Delimeter for each statement. We use “LOOP” at the start and “END LOOP” at the end of each loop.

Let us understand with an example:

CREATE PROCEDURE Sample()
	BEGIN
DECLARE x INT;
	SET x = 1;
	sample_loop: LOOP
	SET x = x + 1;
	select x;
	IF x = 5 THEN
    	LEAVE sample_loop;
    END IF;
    END LOOP sample_loop;
SELECT x;
END

On calling sample and writing:

CALL Sample();

We get:

1, 2, 3, 4, 5

Loops in MySQL use many statements for flow control like ITERATE, IF, CASE, WHILE, REPEAT, etc.

In this article, we will study in detail the MySQL CASE statements.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Case statement in MySQL 

Case 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.

roll_no name percentage grade
01 Ajay 68 C
02 Aman 73 B
03 Arun 31 F
04 Badri 86 A
05 Manpreet 78 B
06 Ramesh 52 D
07 Shyam 95 A+

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:

roll_no name percentage grade pass_class
01 Ajay 68 C THIRD CLASS
02 Aman 73 B SECOND CLASS
03 Arun 31 F FAIL
04 Badri 86 A FIRST CLASS
05 Manpreet 78 B SECOND CLASS
06 Ramesh 52 D FAIL
07 Shyam 95 A+ DISTINCTION

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:

roll_no name percentage grade pass_class
01 Ajay 68 C THIRD CLASS
02 Aman 73 B SECOND CLASS
03 Arun 31 F FAIL
04 Badri 86 A FIRST CLASS
05 Manpreet 78 B SECOND CLASS
06 Ramesh 52 D FAIL
07 Shyam 95 A+ DISTINCTION

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:

roll_no name percentage grade
01 Ajay 68 7.0
02 Aman 73 8.0
03 Arun 31 0
04 Badri 86 9.0
05 Manpreet 78 8.0
06 Ramesh 52 0
07 Shyam 95 10.0

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 problemsinterview 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!

Previous article
MySQL Backup And Restore
Next article
MySQL vs Oracle
Guided path
Free
gridgp-icon
DBMS - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass