MySQL :: MySQL 5.5 Reference Manual :: 12.4 Control Flow Functions

MySQL 5.5 Reference Manual / Functions and Operators / Control Flow Functions

12.4 Control Flow Functions

Table 12.6 Flow Control Operators

Name	Description
`CASE`	Case operator
`IF()`	If/else construct
`IFNULL()`	Null if/else construct
`NULLIF()`	Return NULL if expr1 = expr2

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
```
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL
```
The return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, the result is returned as a decimal, real, or integer value.

Note

The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, “CASE Syntax”, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
```
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'
```
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.
The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows.

Expression Return Value
expr2 or expr3 returns a string string
expr2 or expr3 returns a floating-point value floating-point
expr2 or expr3 returns an integer integer

If expr2 and expr3 are both strings, the result is case sensitive if either string is case sensitive.

Note

There is also an IF statement, which differs from the IF() function described here. See Section 13.6.5.2, “IF Syntax”.

Expression	Return Value
`expr2` or `expr3` returns a string	string
`expr2` or `expr3` returns a floating-point value	floating-point
`expr2` or `expr3` returns an integer	integer

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

The default result value of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:


mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
mysql> DESCRIBE tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| test  | varbinary(4) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+

In this example, the type of the test column is VARBINARY(4).

NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
```
mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1
```
Note that MySQL evaluates expr1 twice if the arguments are not equal.

PREV HOME UP NEXT

User Comments

Posted by I W on July 12, 2005

Don't use IFNULL for comparisons (especially not for Joins)

(example:
select aa from a left join b ON IFNULL(a.col,1)=IFNULL(b.col,1)
)

It's terrible slow (ran for days on two tables with approx 250k rows).

Use <=> (NULL-safe comparison) instead. It did the same job in less than 15 minutes!!

Posted by john mullee on November 9, 2005

IFNULL is like oracle's NVL function (these should help people searching for NVL() ..)

Posted by Philip Mak on May 26, 2006

When using CASE, remember that NULL != NULL, so if you write "WHEN NULL", it will never match. (I guess you have to use IFNULL() instead...)

Posted by Marc Grue on June 24, 2006

You can ORDER BY a dynamic column_name parameter using a CASE expression in the ORDER BY clause of the SELECT statement:

CREATE PROCEDURE `orderby`(IN _orderby VARCHAR(50))
BEGIN

SELECT id, first_name, last_name, birthday
FROM table
ORDER BY
-- numeric columns
CASE _orderby WHEN 'id' THEN id END ASC,
CASE _orderby WHEN 'desc_ id' THEN id END DESC,
-- string columns
CASE _orderby WHEN 'first_name' THEN first_name WHEN 'last_name' THEN last_name END ASC,
CASE _orderby WHEN 'desc_first_name' THEN first_name WHEN 'desc_last_name' THEN last_name END DESC,
-- datetime columns
CASE _orderby WHEN 'birthday' THEN birthday END ASC,
CASE _orderby WHEN 'desc_ birthday' THEN birthday END DESC;

END

Since the CASE expression returns the "compatible aggregated type of all return values", you need to isolate each column type in a separate CASE expression to get the desired result.

If you mixed the columns like

CASE _orderby
WHEN 'id' THEN id
WHEN 'first_name' THEN first_name
...etc...
END ASC

.. both the id and first_name would be returned as a *string value*, and ids would be sorted as a string to '1,12,2,24,5' and not as integers to '1,2,5,12,24'.

Note that you don't need a "ELSE null" in the CASE expressions, since the CASE expression automatically returns null if there's no match. In that case, you get a "null ASC" in your ORDER BY clause which doesn't affect the sort order. If for instance _orderby is 'desc_first_name', the ORDER BY clause evaluates to:

ORDER BY null ASC, null DESC, null ASC, first_name DESC, null ASC, null DESC

Effectively the same as "ORDER BY first_name DESC". You could even add a new set of CASE expressions for a second order column (or more..) if you like.

Posted by Matthew Montgomery on July 3, 2006

An IF() clause will have a SIGNED integer return type if either expr2 or expr3 are integers. This is the case even when you directly CAST one of the expresions as UNSIGNED.

Default Behavior:

mysql> select CAST(1*-1 as UNSIGNED);

+------------------------+
| CAST(1*-1 as UNSIGNED) |
+------------------------+
|   18446744073709551615 |
+------------------------+

1 row in set (0.00 sec)

CAST expr2 as UNSIGNED:

mysql> SELECT IF((1 != 0), CAST(1*-1 as UNSIGNED), 1);

+-----------------------------------------+
| IF((1 != 0), CAST(1*-1 as UNSIGNED), 1) |
+-----------------------------------------+
|                                      -1 |
+-----------------------------------------+

1 row in set (0.02 sec)

CAST both expr2 and expr3 as UNSIGNED:

mysql> SELECT IF(1 != 0, CAST(1*-1 as UNSIGNED), CAST(1 as UNSIGNED));

+---------------------------------------------------------+ 
| IF(1 != 0, CAST(1*-1 as UNSIGNED), CAST(1 as UNSIGNED)) |
+---------------------------------------------------------+
|                                                      -1 |
+---------------------------------------------------------+

1 row in set (0.00 sec)

Solution:

If you know that both values that should be returned by the IF() clause should be (UN)SIGNED you can CAST() the entire IF() clause as such. Currently there appears to be no way to CAST one expression and not the other.

mysql> SELECT CAST(IF((1 != 0), 1*-1, 1) AS UNSIGNED);

+-----------------------------------------+
| CAST(IF((1 != 0), 1*-1, 1) AS UNSIGNED) |
+-----------------------------------------+
|                    18446744073709551615 |
+-----------------------------------------+

1 row in set (0.00 sec)

Posted by Mark Callaghan on November 4, 2006

In MySQL 4.0.26, 'select ifnull(FloatColumn, 1) from foo' returns expressions with type float, while 'create table bar as select ifnull(FloatColumn, 1) from foo' creates a table with a double column. This has been fixed in 5.1 so that the behavior matches the documentation -- the type of the expression returned by the select statement and the column created in the table are both double.

Posted by Robert Glover on February 13, 2007

There is a simple way to convert the following Oracle usage of decode into MySql. The reason for mentioning it here is that the conventional wisdom would be to convert the Oracle decode function into MySql Case statements.

Oracle version:

select BU, count(line_number) total,
sum(decode(RECERTIFY_FLAG,'Y',1,0)) needed,
sum(decode(RECERTIFY_FLAG,'N',1,0)) not_needed,
sum(decode(RECERTIFY_FLAG,'Y',0,'N',0,1)) not_processed
from isf.isf_analog_line group by bu order by bu

MySql version that gives same results:

select BU, count(line_number) total,
sum(FIND_IN_SET(RECERTIFY_FLAG,'Y')) needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,'N')) not_needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,' ')) not_processed
from isf.isf_analog_line group by bu order by bu

Posted by Christian Marcotte on August 31, 2007

Validate and Format a date:

Someone was commenting that there is no built-in date validation. I found that using:

LAST_DAY('2007-02-25') does a good job of it.
It returns NULL if the date is invalid and the date of the last day of the month if it is valid.

I love the fact that those functions will accept all sorts of
goofy / mixed date notation and it will give you a clean
YYYY-MM-DD one in return.

EX:
select last_day('2007:02%25');
or
select last_day('2007/02-25');
or even
select last_day('2007+02=25');

all return a nice clean:

+------------------------+
| 2007-02-28             |
+------------------------+

With that in mind, I now have a one query approach to both validate a user input date AND format it nicely:

SELECT IF(LAST_DAY('2007-02-25'),
CONCAT(YEAR('07-08-25'), '-', month('2007-08-25'), '-', day('2007-08-25')),
NULL
) AS my_valid_formatted_date;

it returns NULL if the date is invalid and YYYY-MM-DD if it is valid.

Posted by Dave Joyce on January 8, 2009

I was using a nested select statement and tried ifnull to force a return from one of the selects. If the select statement is an empty set then ifnull still returns null.

Example
select pra,(select phone from phones where prax=pra) from members where pra=1111;

if pra 1111 doesn't have a phone in phones then the return columns will be 1111 and null.

To force a return on the second select I used count(*)

select pra,(select if(count(*)=0,'no phone',phone) from phones where prax=pra) from members where pra=1111;

Now without a phone the returned values will be

1111 and 'no phone'

Posted by Marcin Szalowicz on May 6, 2009

there is one thing with the case statement it took me a while to figure this out...
when we do:
select name from employee order by
case "john"
when "john" then name
when "sam" then surrname
else id end

mysql will not work with that and will always use the last condition, in this example this will be "else id"...

to solve this we need to add brackets...

here is the solution:

select name from employee order by
(case "john"
when "john" then name
when "sam" then surrname
else id end)

after that everything will be ok

Posted by Milan Zdimal on July 6, 2010

Sometimes it's also useful to use IFNULL in situations where you need "append" a condition to the WHERE clause:

SELECT * FROM table WHERE IFNULL(NULL, field = 1)

Milan Zdimal

Posted by sebastian nielsen on September 12, 2010

Milan: How is it useful to run:

SELECT * FROM table WHERE IFNULL(NULL, field = 1)

instead of:

SELECT * FROM table WHERE field = 1

the IFNULL(NULL, field = 1) would always return field = 1.

But the IFNULL() is good in cases you want to do a insert and clamp a value to a value that is already in record, else set it at maximum.

For example this:

INSERT INTO users (type,class,username,userid,userip,usermobile,useremail,daysleft,pin1,pin2,pin3,active,schoolname,schoolsite) SELECT 'A','A','sebastian',1000,'192.168.0.1','none','[email protected]',(SELECT IFNULL(MIN(daysleft),30) from users WHERE userip='192.168.0.1' AND type='A'),'12345','12345','00000','N','none','none' FROM dual WHERE ((SELECT COUNT(*) FROM users WHERE userip='192.168.0.1' AND type='A' AND daysleft = 0) = 0);

The interesting part is this:
(SELECT IFNULL(MIN(daysleft),30) from users WHERE userip='192.168.0.1' AND type='A')

If theres a record in the table for the user's IP, upon registrering for a account, it will take the smallest daysleft found, else it will give the user 30 days in daysleft.

This is good for example if you want to give 30 days trial period, and still allow members in a household to register for their own accounts, but not allowing longer trial priod by registrering multiple own accounts.

In this case, if a household has a user with lets say 10 trial days left, and a another user in the household registers himself for a account, that user will too get 10 trial days left.
If theres no accounts on a specific household, the user will get a full 30 days trial period.

Posted by Joseph Edmonds on January 19, 2012

Note using the case syntax you can update many rows with one query. This can have a really dramatic postitive impact on performance if you have a large number of updates to do.

For a nice PHP function to handle this see here:
http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/

Posted by Mike Jorgenstam on February 28, 2014

This does not work to validate that all actually fall within as some can have > 1 five while still being within 20 sum and then discards second statement after OR

Thus, MySQL reads the passed if clause in the statement from LEFT TO RIGHT and not from RIGHT TO LEFT as some say...

-- This fails
IF ((@r7q0+@r7q1+@r7q2+@r7q3+@r7q4+@r7q5) < 20) OR
OR (@r7q5 > 1) THEN
SET @res = 0;
END IF;

-- This works
IF ((@r7q6+@r7q7+@r7q8+@r7q9+@r7q10) > 0) OR (@r7q5 > 1) THEN
SET @res = 0;
END IF;

-- This would work in some cases then
IF (@r7q5 > 1) OR
((@r7q0+@r7q1+@r7q2+@r7q3+@r7q4+@r7q5) < 20) THEN
SET @res = 0;
END IF;