MySQL :: MySQL 5.1 with Maria Reference Manual :: 11.3 Control Flow Functions

MySQL 5.1 with Maria Reference Manual :: 11 Functions and Operators :: 11.3 Control Flow Functions

Section Navigation [Toggle]

11.3. Control Flow Functions

Table 11.5. 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 default 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, then 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 12.8.6.2, “CASE Statement”, 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 12.8.6.1, “IF Statement”.

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 CHAR(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.

Previous / Next / Up / Table of Contents

User Comments

Posted by I W on July 12 2005 3:52pm

[Delete] [Edit]

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 11:12pm

[Delete] [Edit]

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

Posted by Philip Mak on May 26 2006 5:14am

[Delete] [Edit]

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 12:03pm

[Delete] [Edit]

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 10:16am

[Delete] [Edit]

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 2:19am

[Delete] [Edit]

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 3:07pm

[Delete] [Edit]

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 12:51am

[Delete] [Edit]

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 10:40pm

[Delete] [Edit]

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 Szałowicz on May 6 2009 7:43am

[Delete] [Edit]

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

Add your own comment.

Top / Previous / Next / Up / Table of Contents