IsNull, ifnull, and nullif function usages in MySQL

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.

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, 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)

If EXPR1=EXPR2 is true, the return value is NULL, otherwise the return value is EXPR1. This and

Case if EXPR1=EXPR2 then NULL

ELSE

Expr1

End is the same.

Mysql>select Nullif (a);

->null

Mysql>select Nullif ();

->1

If the arguments are not equal, the MySQL two-time value is expr1.

Source: https://jingyan.baidu.com/article/6b182309516bc5ba58e159f0.html

First, if function
Expression: IF (EXPR1,EXPR2,EXPR3)
Satisfies the condition expr1 that condition Expr1 returns true
Then EXPR2 or EXPR3 returns false
When used as a sort condition after order by
eg
1. SELECT IF (1=1,2,3); Then output 2
2.SELECT if (1=2,2,3); output 3
SELECT * FROM Test_table ORDER by if (ISNULL (Idcard), 1,0)
Second, ifnull function
Expression: Ifnull (EXPR1,EXPR2)
If the EXPR1 value is null, the output EXPR2
If the EXPR1 value is not empty, the output EXPR1 itself

eg
1.SELECT ifnull (NULL, "2"); Output 2
2.SELECT ifnull (3, "2"); output 3

Third, is not NULL function
SELECT * FROM test where name is not null;
Iv. usage of isnull (expr):
If expr is null, then the return value of IsNull () is 1, otherwise the return value is 0.

Source: http://blog.csdn.net/cc_yy_zh/article/details/52700478

From for notes (Wiz)

IsNull, ifnull, and nullif function usages in MySQL