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:

Another syntax of CASE function is:

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.

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:

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:

Example:

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:

EXAMPLE:

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:

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:

This statement will generate a division by zero error, but you can handle such error with simply using the NULLIF as follows

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.

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

Most Popular Posts

MySQL coalesce() function

Posted on 12 years ago

Bhumi

To parse CSV Data in PHP

Posted on 12 years ago

Bhumi

To learn MySQL PACK_KEYS with example

Posted on 9 years ago

Bhumi

How to save image in MySQL Database

Posted on 12 years ago

Bhumi