Nisarg Upadhyay
CASE expression in ORDER BY exmple

Learn MySQL: Control Flow functions

March 19, 2021 by

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:

  1. The IF functions
  2. The CASE expression
  3. The IFNULL function
  4. 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:

The following query inserts the database in tblStudent table:

The following query inserts dummy data in tblMarks table:

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:

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

Output 1

Control flow function: example IF function

Query 2

Output 2

Example 2 of IF function

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

Output

Select query for tblStudent

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:

The output is the following:

Print Not Applicable using IF function

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:

Print Not Applicable using IF and equal to operator function

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:

  1. Count of a student whose grade is A+. These students should be categorized as Brilliant Students
  2. Count of the student whose grade is B+. These students should be categorized as Average Students
  3. 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

Count of student using IF Function

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.

  1. The simple CASE expression
  2. 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:

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.

  1. If the student’s grade is A+, then he should be considered an Intelligent
  2. If the student’s grade is B+, then he should be considered as an Average Student
  3. 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:

Output

Control Flow function CASE example

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

Output

CASE expression in ORDER BY exmple

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:

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.

  1. If the student’s surname is Chauhan, then the father’s name must be Hasmukh Chauhan
  2. If the student’s surname is Bhatt, then the father’s name must be Jivan Bhatt
  3. If the student’s surname is Upadhyay, then the father’s name must be Lalshankar Upadhyay

Query

Output

Searched case expression example 1

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:

Output

Searched case expression example 2

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

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

Output

Control flow function IFNULL example

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

Example

Query

Output

Control flow function NULLIF example 1

Query

Output

Control flow function NULLIF example 2

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

Learn MySQL: Querying data from MySQL server using the SELECT statement
Learn MySQL: What is pagination
Learn MySQL: Sorting and Filtering data in a table
Learn MySQL: Add data in tables using the INSERT statement
Learn MySQL: Create and drop temp tables
Learn MySQL: Delete and Update Statements
Learn MySQL: The Basics of MySQL Stored Procedures
Learn MySQL: The Basics of MySQL Views
Learn MySQL: An overview of MySQL Binary Logs
Learn MySQL: An overview of the mysqlbinlog utility
Learn MySQL: Run multiple instances of MySQL Server on Windows 10
Learn MySQL: MySQL String Functions
Learn MySQL: Control Flow functions
Learn MySQL: Install MySQL server 8.0.19 using a noinstall Zip archive

Nisarg Upadhyay
MySQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. 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

790 Views