Control Flow Functions in MySQL
Overview
Control flow functions in MySQL are used to execute specific parts of a query depending on specific conditions. These functions are capable of handling both single and multiple circumstances. The Boolean expression serves as the foundation for the control flow function. It controls and manages table data as needed. MySQL's most popular control functions are IF, CASE, NULLIF, and COALESCE. We will see and learn each of them with examples. The control function returns a value for each row processed by the query.
Introduction
Control flow functions in MySQL are used to manipulate and govern the flow of data and logic in MySQL scripts and stored procedures. IF-THEN-ELSE, CASE, WHILE, FOR, GOTO, and TRY-CATCH are some examples of control flow functions in MySQL. These functions allow us to execute specific parts of code based on conditions, loop through data sets, move to specific lines or labels, and manage errors or exceptions. These functions can be used by developers to create sophisticated and automated scripts that successfully manipulate large amounts of data and perform complex operations.
IF() Function
The IF() function in MySQL is the most common among control flow functions in MySQL. It verifies the condition and returns a particular value if the condition is TRUE or another specified value if the condition is FALSE. It is often used in MySQL statements like SELECT, UPDATE, and INSERT to assign values to columns or variables based on a condition.
The syntax for the IF() function is as follows:
The "condition" will be evaluated first, and "output_if_true" will be returned if it's true, and "output_if_false" if the condition is false.
Let's look at the following query for a better understanding:
This query selects the salary column from the "users" table and uses the IF() function to create a new column called "Category". If the "Salary" column is greater than or equal to 2000, the value "Taxable" is returned; otherwise, the value "Non-Taxable" is returned.
Note:
The IF() function in MySQL performs conditional computations based on a condition. It is a powerful tool that can be nested within other functions to construct complex conditional statements. However, it is not supported by all SQL databases, in which case, other conditional functions like CASE or COALESCE can be used.
CASE
CASE statement in MySQL is used as an alternative to multiple nested IF statements. It allows you to perform different actions based on different conditions.
Below is the syntax for the CASE statement.
Let's look at the following example for a better understanding:
This query will work exactly like the IF statement, i.e., salary column from the "users" table, and uses the CASE() function to create a new column called "Category". If the "Salary" column is greater than or equal to 2000, the value "Taxable" is returned; otherwise, the value "Non-Taxable" is returned.
Above was an example of a simple case statement. Now, Let's look at Searched Case statement.
Searched Case
A searched CASE statement in MySQL allows the evaluation of multiple Boolean conditions instead of one contrary to that of IF(). It evaluates conditions and gives the result of the first condition that evaluates to true.
Let's look at the example of Searched Case:
In this query, the Searched CASE statement evaluates multiple conditions based on the salary of users and returns an appropriate result. If the salary is between 2000 and 2500, the value "Intern" is returned; if the salary is between 2500 and 5000, the value "Junior Employee" is returned; if the age is greater than 5000, the value "Senior Employee" is returned. If none of the conditions are met, the value "Unknown" is returned.
Finally, the CASE statement is a powerful MySQL function for performing conditional reasoning and manipulating data depending on certain requirements. It can be used to simplify difficult queries and deliver useful results in a variety of ways.
NULLIF()
In MySQL, the NULLIF() function is used to evaluate two expressions. The function returns NULL if the two expressions are equal. The function returns the first expression if the expressions are not equal.
Below is the syntax for the NULLIF() function:
Let's look at one query for a better understanding.
The NULLIF() function in this query is used to return null values in the "salary" column of the "users" database as 0.
Overall, the NULLIF() is a valuable function for dealing with scenarios in which we need to compare expressions and return NULL if they are equal.
COALESCE()
In MySQL, the COALESCE() function returns the first non-null value in a collection of expressions. It accepts two or more inputs and returns the first non-null value. It yields null if all of the arguments are null. The function is frequently used in MySQL queries to provide default values for nulls or to manage missing data.
Below is the syntax for the COALESCE() function:
Let's look at a query for a better understanding.
The output of this query will be Scalar as this is the first non null value. COALESCE() can be used in a variety of other MySQL queries. It is a useful function that can help you work more efficiently with null data and simplify complex searches.
GREATEST() and LEAST()
GREATEST() and LEAST() are among the Control Flow Functions in MySQL functions that return the largest and smallest numbers from a list of expressions.
Below is the syntax for GREATEST() function:
Let's look at a query for a better understanding.
The output of the above query will be 43 as its largest among all.
Similar is the syntax of LEAST() function, You can see it below:
Following is query for LEAST() function:
The output of the above query will be 12 as it's the smallest among all.
Finally, the GREATEST() and LEAST() MySQL procedures can be used to find the highest and minimum values from a group of input expressions. These functions are quite useful in a variety of contexts, such as determining the highest and lowest pay among employees, as demonstrated in the preceding example.
Conclusion
- Control flow functions in MySQL are used to execute specific parts of a query depending on specific conditions.
- The MySQL IF() function enables you to perform one of two actions based on a conditional test.
- The MySQL CASE() function enables you to perform one of several possible actions based on a conditional statement.
- In MySQL, the COALESCE() function returns the first non-null result in a list of values.
- GREATEST() and LEAST() returns the largest and smallest numbers from a list of expressions.
- NULLIF() function is used to evaluate two expressions, It gives NULL if the two expressions are equal and gives the first expression if the expressions are not equal.