Sometimes you are executing some complex queries in MySQL and queries have some conditional statements so at that time Control Flow function is very useful for you. Control Flow functions are the simple way to handle the conditions in MySQL queries.
MySQL has several Control Flow Functions:
1) CASE
2) IF
3) IFNULL
4) NULLIF
Control function returns value based on each row processed by the query executed. Control functions can be used on SELECT, WHERE, ORDER BY and GROUP BY statements.
1) CASE
CASE is one of the control flow function where this is just like the switch case in most programming languages.
SYNTAX:
The syntax of the NULLIF function is as follows:
1 2 3 4 5 6 7 | CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE |
Another syntax of CASE function is:
1 2 3 4 5 6 7 | CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE |
This is the syntax which you find on the official page of MySQL so here I am sharing the syntax but let me make it simpler for you.
1 2 3 4 5 6 7 | CASE WHEN expression1 = expression2 THEN NULL ELSE expression1 END; |
If the value = compare-value, it returns the result in the first case. If the corresponding result is not defined, it will return result specified after the operator ELSE. If part of the ELSE in the expression is absent, returns NULL
EXAMPLE:
1 2 3 4 5 6 | // Below will return zero SELECT CASE 0 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE 'no one' END; // Below will return true SELECT CASE WHEN 5>2 THEN 'true' ELSE 'false' END; |
2) IF
If() function is a very handy control flow function in MySQL as returns a value based on a condition.This function accepts three parameters as expression, if expression one is true then it will return the second parameter otherwise it will return the third parameter.It is one type of ternary operator.
SYNTAX:
The syntax of the IF function in MySQL is as follows:
1 2 3 | IF (expr1, if_true_expr,if_false_expr) |
Example:
1 2 3 4 5 6 | SELECT IF(1<5,'yes','no'); // OutPut yes SELECT IF(1>5,'yes','no'); //OutPut no |
NOTE: Keep in mind IF() function is different than the IF statement.
If expr1 is true with expr1 <> 0 and expr1 <> NULL, then IF() function will return expr2, otherwise it will return expr3. IF() function returns a numeric or string depending on the context used.
3) IFNULL
This function accepts two parameters as an expression.If expression one is not NULL then it will return the first expression otherwise it will return the second expression. Arguments can be literal values or expressions.
SYNTAX:
1 2 3 | IFNULL (expr1, expr2) |
EXAMPLE:
1 2 3 4 5 6 | SELECT IFNULL(1,0); // OutPut 1 SELECT IFNULL(NULL,10); // OutPut 10 |
NOTE: It is better to avoid the use of the IFNULL function in the WHERE clause as it degrades the query performance. If you want to check whether a value is NULL or not, you can use IS NULL in the WHERE clause.
Default return value of IFNULL is STRING, REAL, or INTEGER in MySQL 4.0.6 and later.If you want to return a value as TRUE or FALSE condition rather than NULL, you can use the IF() function.
4) NULLIF
This function takes two parameters as an expression. It will return NULL if expr1=expr2 will return TRUE otherwise it will return expr1.
SYNTAX:
The following illustrates the syntax of the IFNULL function:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT NULLIF(expr1,expr2); // Return NULL SELECT NULLIF(5,5); // Return 10 SELECT NULLIF(10,4); SELECT NULLIF(1,1) // OutPut NULL because 1 is equal 1. |
You can also handle the division by zero error. If ERROR_FOR_DIVISION_BY_ZERO mode enabled in the MySQL, it will generate an error when a division by zero occurred.
Let’s understand it with the example as follows:
EXAMPLE:
1 2 3 | SELECT 1/0 |
This statement will generate a division by zero error, but you can handle such error with simply using the NULLIF as follows
1 2 3 | SELECT 1/NULLIF(0,0); |
Above statement will output the NULL value without generating any error.
Note: MySQL evaluates expr1 twice if the arguments are not equal.
Do let me know if you have ever used MySQL Control Flow Functions? How MySQL Control Flow Functions useful to you?
