Back to Top

Control Flow Functions in MySQL

control-flow-function-mysql

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:

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:

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.

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:

// 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:

IF (expr1, if_true_expr,if_false_expr)

Example:

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:

IFNULL (expr1, expr2)

EXAMPLE:

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:

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:

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

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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to Count Number of Columns in MySQL

Posted on 6 years ago

Zinal

How to clean HTML content in PHP?

Posted on 5 years ago

Bhumi

Hello world

Updated 1 month ago

Nick Carter

In depth Understanding of Laravel Core

Updated 3 months ago

Bhumi

ECMAScript 6 New Features – 2

Updated 1 year ago

Bhumi

The Reader’s Poll – June 2015

Posted on 4 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 5 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 6 years ago

Bhumi