6.3.1.4 Control Flow Functions

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else 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'

In 4.0.6 and above the default result value of IFNULL(expr1,expr2) is the more 'general' of the two expressions, in the order STRING, REAL or INTEGER.The difference to earlier MySQL versions are mostly notable when you create a table based on expressions or MySQL has to internally store a value from IFNULL() in a temporary table.

CREATE TABLE foo SELECT IFNULL(1,"test") as test;

In MySQL 4.0.6 the type for column 'test' is CHAR(4) while in earlier versions you would get BIGINT.

NULLIF(expr1,expr2)

If expr1 = expr2 is true, return NULL else return expr1. This is the same as CASE WHEN x = y THEN NULL ELSE x END:

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

Note that expr1 is evaluated twice in MySQL if the arguments are not equal.

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2, else 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 expr2 or expr3 is explicitely NULL then the result type of the IF() function is the type of the not NULL column. (This behaviour is new in MySQL 4.0.3). expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation:

mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1

In the first case above, IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0). This may not be what you expect. In the second case, the comparison tests the original floating-point value to see whether it is non-zero. The result of the comparison is used as an integer. The default return type of IF() (which may matter when it is stored into a temporary table) is calculated in MySQL Version 3.23 as follows:

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

If expr2 and expr3 are strings, then the result is case-insensitive if both strings are case-insensitive. (Starting from 3.23.51)

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, which is true. If there was no matching result value, then the result after ELSE is returned. If there is no ELSE part then NULL is returned:

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 type of the return value (INTEGER, DOUBLE or STRING) is the same as the type of the first returned value (the expression after the first THEN).

User Comments

If you have a password field in your table which is MD5 encrypted, you can use the following SQL to verify if a user supplied password matches the MD5:

SELECT IF( password = MD5('$password' ), 'yes', 'no' ) FROM my_user_table WHERE username = '$userid'

If the password matches, a 'yes' will be returned.

Tested with MySQL 3.23.47 on Linux

Cheers

Nico

This is an addition to the previous comment.
You can check for a multiple conditions in IF or CASE statement.

SELECT IF( password = MD5('$password') AND ip = $ip ), 'yes', 'no' ) FROM my_user_table WHERE username = '$userid'

How to enable result in CASE synthax? Can I use "AS" there?

Of course you can, just add AS after END, here is an example:
select case when id_tipo=2 then "gral" when id_tipo=3 then "dpto" end as hola from usuario where id_tipo=2 or id_tipo=3

It appears that the IF control does not allow use of SELECT statements. For example, I cannot get something like IF ('SELECT table1.something from table1,table2 where table1.a=table2.a',1,0) to work.

SELECT within IF is not possible, use IF within SELECT:

SELECT table1.something, IF(table1.a=table2.a',1,0) as table1and2 from table1,table2

or like this:

SELECT table3.something, IF(table1.a=table2.a',1,0) as table1and2 from table1,table2,table3 where table3.something = table1.something

If you are searching data and you want to show the result of the search with one column above the other you can use the following (difficult to explain, just read the example):

keyword is "John"

SELECT id, lastname, firstname, IF(lastname LIKE '%John%', 0, 1) as ordervalue FROM customers WHERE lastname LIKE '%John%' OR firstname LIKE '%John%' ORDER BY ordervalue, lastname, firstname;

Now you will first get all the entries where the lastname contains "John" (Johnson) alphabetically listed and then the entries where the firstname contains "John".

Add your own comment.