Skip to content

IF

Grammar Description

IF() can be used as an expression or as a process control statement in a stored procedure.

Grammar Structure

> IF(expr1,expr2,expr3)
  • If expr1 is TRUE (expr1 <> 0 and expr1 IS NOT NULL), the result returns expr2; otherwise, it returns expr3.

— If there is only one in expr2 or expr3 that is explicitly NULL, the result type of the If() function is of a type that is a non-NULL expression.

  • The default return type of IF() (which may have an impact when stored in a temporary table) is calculated as follows:

  • If expr2 or expr3 generates a string, the result is a string.

  • If expr2 and expr3 are both strings, if one of the strings is case-sensitive, the result is also case-sensitive.

  • If expr2 or expr3 generates a floating point value, the result is a floating point value.

Example

mysql> SELECT IF(1>2,2,3);
+-----------------------+
| if(1 > 2, 2, 3) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT IF(1<2,'yes','no');
+-------------------------+
| if(1 < 2, yes, no) |
+-------------------------+
| yes |
+-------------------------+
1 row in set (0.00 sec)

CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL);
INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0);

mysql> select if(u=1,st,st) s from t1 order by s;
+------+
| s |
+------+
| A |
| AA |
| BBB |
| a |
| a |
| aa |
| aaa |
+------+
7 rows in set (0.00 sec)

mysql> select if(u=1,st,st) s from t1 where st like "%a%" order by s;
+------+
| s |
+------+
| a |
| a |
| aa |
| aaa |
+------+
4 rows in set (0.00 sec)

limit

The functions IF parameters BIGINT and VARCHAR are not supported yet.