In this article, we are going to learn about the most common control flow functions. The control flow function evaluates the condition specified in it. The output generated by them can be a true, false, static value or column expression. We can use the control flow functions in the SELECT, WHERE, ORDER BY, and GROUP BY clause. Following are the most common functions:
- The IF functions
- The CASE expression
- The IFNULL function
- The NULLIF function
In this article, we are going to learn the IF function and CASE expressions that are very important control flow functions in MySQL. Moreover, I am including an overview of the IFNULL and NULL of functions.
Demo Setup
To understand the function in detail, I have created a sample database named SchoolDB. I have created two tables named tblStudent and tblMarks in the SchoolDB database.
The following script creates the database and tables:
|
1 2 3 |
create database SchoolDB; create table `SchoolDB`.`tblStudent` (ID int auto_increment primary key, StudentName varchar(250), ClassRoom char(1), Grade char(2)); create table `SchoolDB`.`tblMarks`(ID integer auto_increment primary key, student_id int, subject varchar(50), Marks int); |
The following query inserts the database in tblStudent table:
|
1 2 3 4 5 6 7 8 9 10 11 |
insert into tblStudent (StudentName,ClassRoom,Grade) values ('Nisarg Upadhyay','A','A+'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Nirali Upadhyay','A','A+'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Bharti Upadhyay','A','B+'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Dixit Upadhyay','B','A+'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Hemant Bhatt','B','D-'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Usha Bhatt','A','A+'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Shailja Chauhan','B','D'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Deep Chauhan','A','B+'); insert into tblStudent (StudentName,ClassRoom,Grade) values ('Omkar Bhatt','C','A+'); insert into tblStudent (StudentName,ClassRoom,Grade) values (Rajesh Bhatt',NULL,NULL); insert into tblStudent (StudentName,ClassRoom,Grade) values (Vinesh Patel',NULL,NULL); |
The following query inserts dummy data in tblMarks table:
|
1 2 3 4 5 6 7 |
insert into tblMarks (student_id, subject, Marks ) values (3,'Maths',100); insert into tblMarks (student_id, subject, Marks ) values (1,'SS',80); insert into tblMarks (student_id, subject, Marks ) values (1,'Science',90); insert into tblMarks (student_id, subject, Marks ) values (2,'SS',45); insert into tblMarks (student_id, subject, Marks ) values (2,'Maths',50); insert into tblMarks (student_id, subject, Marks ) values (2,'Science',35); insert into tblMarks (student_id, subject, Marks ) values (3,'Science',35); |
Now, first, let us understand the IF function.
An overview of the IF() function
The IF function returns the value based on the condition specified within the function. The syntax of the IF function is the following:
|
1 |
SELECT IF(condition, if_condition_true, if_condition_false) |
In the syntax:
- Condition is a condition specified in the IF function
- If_condition_true is the value returned by the function
- If_condition_false is the value returned by the function
The IF function evaluates the values returned by the SELECT statement with the specified condition. If the function evaluates as TRUE, then it returns the value specified in if_condition_true. If the function evaluates as FALSE, then it returns the value specified in the if_condition_false.
Let us understand the concept using examples.
The simple IF example
When the input string matches with each other, then print TRUE else, print FALSE. We can use the IF function in the SELECT statement. The query should be written as follows:
Query 1
|
1 |
mysql> select if('nisarg'='nirali','True','False') as 'Simple IF statement'; |
Output 1
Query 2
|
1 |
mysql> select if('nisarg'='nirali','True','False') as 'Simple IF statement'; |
Output 2
Print specific text in the output
For the demonstration, I have added two rows in the tblStudent with NULL values in grade and class columns. For the reporting purpose, instead of showing NULL, we want to show the Not available in the query output.
Query
|
1 |
mysql> Select studentname grade, classroom from tblstudent; |
Output
As you can see in the above image, the values of the grade and class column for Rajesh and Vinesh are NULL. Now, to display Not Available, the query should be written as follows:
|
1 2 3 4 |
Select studentname, IF(classroom IS NULL, 'Not Applicable',classroom) as 'Classroom', IF(grade IS NULL, 'Not Applicable',grade)as 'Grade' from tblstudent; |
The output is the following:
Use IF to display specific output
Suppose you want to display the name and grade based on the following condition:
- Print Brilliant Student for the student who has an A+ grade, else prints Average Student.
The SELECT statement must be written as follows:
|
1 |
mysql> select studentname , IF(Grade='A+','Brilliant Student','Average Student') as 'Student Grade' from tblstudent; |
Use IF statement with the COUNT function
Suppose you want to get the total number of students with A+, B+, and D- grades. The condition is:
- Count of a student whose grade is A+. These students should be categorized as Brilliant Students
- Count of the student whose grade is B+. These students should be categorized as Average Students
- Count of the student whose grade is D-. These students should be categorized as Weak Students
To generate the result set, we can use the IF function to evaluate the condition and then use the COUNT function to get the number of students with their respective grade.
Query
|
1 2 3 4 5 |
SELECT COUNT(IF(Grade='A+',1,NULL)) 'Brilliant Student', COUNT(IF(Grade = 'B+', 1, NULL)) 'Average Student', COUNT(IF(Grade = 'D-', 1, NULL)) 'Weak Student' FROM tblstudent; |
Now, let us understand the CASE expression.
An overview of MySQL CASE expression
The CASE statement is one of the most common control flow expressions of MySQL. The case expression is used to implement the IF ELSE logic in the query. The CASE expression can be used in the SELECT, WHERE, and ORDER BY clause. There are two variants of the CASE expression.
- The simple CASE expression
- The searched CASE expression
The simple CASE expression
The simple CASE expression matches the input with the values for equality and returns the corresponding result.
Following is the syntax of the simple CASE expression:
|
1 2 3 4 5 6 7 |
Select Case expression when static_value_1 then output_1 when static_value_2 then output_2 .. Else else_output End |
In the syntax, the case matches the expression with static_value_1, static_value_2 … and returns the corresponding output_1, output_2… values. If the input values do not match with any expression, then it returns the else_output. Let us understand the concept with an example.
Example 1
Suppose you want to populate the list of the students, their grades, and grade description. The query output must show the grade description based on the following condition.
- If the student’s grade is A+, then he should be considered an Intelligent
- If the student’s grade is B+, then he should be considered as an Average Student
- If the student’s grade is D+ or D- then he should be considered a Weak Student
In the case statement, we can specify an operator as well. I have specified an OR operator to ensure that the student whose grade is D+ or D should be considered a Weak student.
The query is the following:
|
1 2 3 4 5 6 |
select studentname, grade, case grade when 'A+' then 'Intelligent' when 'B+' then 'Average Student' when 'D' or 'D-' then 'Weak student' end as 'grade description' from tblstudent; |
Output
Example 2
Suppose we want to sort the description of the grades in ascending order. As I mentioned, the CASE statement can be used in the ORDER BY clause. To sort the results, we must specify the case statement after the ORDER BY clause.
Query
|
1 2 3 4 5 6 7 8 9 10 11 |
select studentname, grade, case grade when 'A+' then 'Intelligent' when 'B+' then 'Average Student' when 'D' or 'D-' then 'Weak student' end as 'grade description' from tblstudent where grade is not null order by case grade when 'A+' then 'Intelligent' when 'B+' then 'Average Student' when 'D' or 'D-' then 'Weak student' end asc |
Output
Now, let us understand the searched CASE expression.
The Searched CASE expression
The searched case expression evaluates the expression specified in the WHEN clause and returns the corresponding value. The syntax is the following:
|
1 2 3 4 5 6 7 |
Select Case when expression_1 then output_1 when expression_2 then output_2 .. Else else_expression_output End |
In the syntax, the case matches the input values with expression_1, expression_2 … and returns the corresponding output_1, output_2… values. If the input values do not match with any expression, then it returns the else_expression_output.
- Note: If you have a specified character string in the case expression, then the output returned by the case expression is a character string. Similarly, if you have specified a numeric value in the case expression, the output returned by the case expression is a decimal, an integer, or a real value
Examples of the searched case expression
Following are the examples of the searched case expression.
Example 1
Suppose we want to populate the name of the parents based on the name of the surname of the student. The query must populate the values of studentname, grade, and classroom columns. The condition for the CASE expression is following.
- If the student’s surname is Chauhan, then the father’s name must be Hasmukh Chauhan
- If the student’s surname is Bhatt, then the father’s name must be Jivan Bhatt
- If the student’s surname is Upadhyay, then the father’s name must be Lalshankar Upadhyay
Query
|
1 2 3 4 5 6 7 8 |
select studentname, case when studentname like '%Chauhan%' Then 'Hasmukh Chauhan' when studentname like '%Bhatt%' then 'Jivan Bhatt' when studentname like '%Upadhyay%' then 'Lalshankar Upadhyay' end as ParentName, classroom,grade from tblstudent where classroom is not null; |
Output
Example 2
Suppose we want to populate the total subjects studied by the student. To populate the value of the subject, we are joining tblStudent and tblMarks using left join. The condition for the CASE expression is following:
- If the count of the subject studied by the student is zero, then the query should display Details not available else; it must display the count of the subject. This example demonstrates the use of the COUNT() function in the CASE expression
The query is the following:
|
1 2 3 4 5 6 |
select Studentname, case when count(subject)=0 then 'Details Unavailable' else count(subject) end as 'SubjectsTaken' from tblstudent a left join tblMarks b on a.ID=b.student_id group by student_id, studentname order by b.student_id desc; |
Output
The IFNULL function
The IFNULL function is another common control flow function of MySQL. If the specified expression is NULL then the NULLIF function returns the specific value, else it returns the expression.
Syntax
|
1 |
SELECT IFNULL(NULL, expr2); |
In the syntax, if the value of the expr1 is NULL then it returns expr2 else it returns a value of expr1.
Example
Suppose when the query finds the NULL value for the grade column, we want to print details not available else we want to print the value of the column. This can be achieved by using the CASE expression, but we are using the IFNULL function.
Query
|
1 |
select studentname, IFNULL(grade, 'Not available') as grade from tblStudent |
Output
The NULLIF function
The NULLIF function compares the expressions specified in the function. If the values of the expressions are equal then it returns the NULL else it returns the first expression.
Syntax
|
1 |
SELECT NULLIF(expr1, expr2); |
Example
Query
|
1 |
Select NULLIF(‘Nisarg’,’Nisarg’) |
Output
Query
|
1 |
Select NULLIF(‘Nisarg’,’Nirali’) |
Output
Summary
In this article, we have learned the most common control flow functions of MySQL. These functions can be used to manipulate the output generated by the SQL query. The CASE and IF are the conditional expressions and functions that are used to display the output based on the condition specified in the query.
Table of contents
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022