MySQL Control flow function
The control flow function uses values or operands for logical operations. These functions can works on single and multiple conditions. The control flow function is based on the Boolean expression. It controls and manages table data as per requirement. The Boolean expressions mean 0 (null) value and 1(not null) value. The below table shows the type of the control flow function.
| MySQL IF statement | The IF statement shows the true condition of the control flow function. |
| MySQL IFNULL statement | The IFNULL statement shows null or not null of the data. |
| MySQL NULLIF statement | The NULLIF statement works on the not null value of the data. |
| MySQL CASE statement | This statement works on multiple conditions. |
MySQL IF statement
The first condition is necessary to display the result. The other condition is optional in the "IF" statement. The IF statement shows the true condition of the control flow function.
Example of the IF statement
Example: the "IF" statement with true condition example shows below.
Execute the below statement that shows the first condition is true.
mysql> SELECT IF(7 = 7, 'equal', 'not_equal');Output
The above output image shows equal output because when the condition becomes true, the query shows the first expression like equal.
Example: the "IF" statement with false condition example shows below.
Execute the below statement that shows the first condition is false.
mysql> SELECT IF(7 = 13,'equal','not_equal');Output
The above output image shows not equal output because when the condition becomes false, the query shows a second expression like not equal.
MySQL IFNULL statement
The IFNULL statement shows the null or not null result of the control flow function. If the first condition is not null, it returns the first condition. If the first condition is null, then it returns the second condition. This statement contains string and numeric values.
Examples of the IFNULL statement
The "IFNULL" statement with true condition example shows below.
Execute the below query to understand the working procedure of numerical values and their output.
mysql> SELECT IFNULL(1, 0);Output
The above image shows the first value of the condition. If the first value is not null, then MySQL displays these values. The above query shows one output.
MySQL NULLIF statement
The IF statement shows the true condition of the control flow function. The first condition shows equal to the second condition, then it returns the first condition value. The first condition shows the "not equal" to the second condition then the query returns the second condition value.
Examples of the NULLIF statement
Example: the "NULLIF" statement with true condition example shows below.
Execute the below query to understand the working procedure of NULLIF and their output.
mysql> SELECT NULLIF(1, 0);Output
The above image shows the first value of the condition. If the first value shows the "not equal" to the second value, MySQL displays the first value.
Example: the "NULLIF" statement example shows below.
mysql> SELECT NULLIF (1, 1);Output
The above returns the above output. If the first value is equal to the second value, then MySQL displays the NULL value. The above query shows the NULL output.
MySQL CASE statement
The case statement works as "if else" and "if then else" statements. This statement works on multiple conditions in a single query. If the first condition is true, then returns the first condition. If the first condition is false, then it uses other conditions.
Example: the "CASE" statement with numerical and string value example shows below.
Execute the below query to get the required value using a case statement. Here, you can use string values with the "CASE" and their conditions.
mysql> SELECT CASE 'MON' WHEN 'SUN' THEN 'SUNDAY' WHEN 'MON' THEN 'MONDAY' ELSE 'OTHERS' END;Output
The above image displays string values with given conditions. The first row of the table shows the case statement with value. The second row displays the output value. The output table shows the "MONDAY" value.