Today used in the MySQL IsNull only found him and MSSQL in a little difference, now briefly summarize:
The usage of isnull,ifnull,nullif in MySQL is as follows:
Usage of IsNull (expr):
If expr is null, then the return value of IsNull () is 1, otherwise the return value is 0.
Mysql> Select IsNull (+);
0
Mysql> Select IsNull (1/0);
1
The comparison of NULL values using = is usually wrong.
The IsNull () function has some of the same characteristics as the IS null comparison operator. See the description for is null.
Usage of ifnull (EXPR1,EXPR2):
If EXPR1 is not NULL, the return value of Ifnull () is expr1;
Otherwise its return value is EXPR2. The return value of Ifnull () is either a number or a string, 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 for
Ifnull (EXPR1,EXPR2) is one of the more "common" in two expressions, in the order of string, real, or
INTEGER. Suppose a case of an expression-based table is, or MySQL must store the return value of Ifnull () in a temporary table in the internal memory:
create table tmp select ifnull (1, ' Test ') as test;
In this example, the type of the test column is CHAR (4).
nullif (EXPR1,EXPR2) usage:
if expr1
= expr2 is established, the return value is NULL , otherwise the return value is Expr1. This and case when expr1 = expr2
THEN NULL else expr1 end are the same.
MySQL > select
Nullif ( );
, NULL
mysql> SELECT nullif;
1
If the arguments are not equal, the MySQL two-time value is expr1.
Ifnull, Nullif, and isnull usage in MYSQL