IFNULL, NULLIF, and ISNULL usage in MySql

In mysql, isnull, ifnull, and nullif are used as follows: 1. If ISNULL (expr) is NULL, ISNULL () returns 1; otherwise, 0. 2. IFNULL (expr1, expr2) If expr1 is not NULL, the return value of IFNULL () is expr1; otherwise, the return value is expr2. IFNULL () returns numbers or characters.

In mysql, isnull, ifnull, and nullif are used as follows: 1. If ISNULL (expr) is NULL, ISNULL () returns 1; otherwise, 0. 2. IFNULL (expr1, expr2) If expr1 is not NULL, the return value of IFNULL () is expr1; otherwise, the return value is expr2. IFNULL () returns numbers or characters.

In mysql, isnull, ifnull, and nullif are used as follows:

1. ISNULL (Expr)
For exampleExprIf it is NULL, ISNULL () returns 1; otherwise, the return value is 0.

2. IFNULL (Expr1,Expr2)
Assume thatExpr1If not NULL, the returned value of IFNULL () isExpr1Otherwise, the returned value isExpr2. The returned value of IFNULL () is a number or string, depending on the context in which it is used.

This function is the same as nvl (null value Conversion Function) in oracle.

Select ifnull (null, 0 );
Two Parameters. If the first parameter is null, the second parameter is output.

Isnull (expr) usage:
If expr is null, the return value of isnull () is 1; otherwise, the return value is 0.
Mysql> select isnull (1 + 1 );
-> 0
Mysql> select isnull (1/0 );
-> 1
The comparison of null values with = is usually incorrect.

The isnull () function has the same features as the is null comparison operator. See the description of is null.

IFNULL (expr1, expr2) usage:

If expr1 is notNULL, Then the returned value of IFNULL () is expr1;
Otherwise, the returned value is expr2. The returned value of IFNULL () is a number or 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 of IFNULL (expr1, expr2) is one of the two expressions that is more "common", in the order of STRING, REAL, or
INTEGER. Assume that an expression-based table, orMySQLThe returned value of IFNULL () in a temporary table must be stored in the internal memory:
Create table tmp select ifnull (1, 'test') AS test;
In this example, the test column type is CHAR (4 ).
Usage of NULLIF (expr1, expr2:
If expr1
= Expr2 is true, then the returned value isNULLOtherwise, the returned value is expr1. This is the same as case when expr1 = expr2
THENNULLELSE expr1 END is the same.
Mysql> SELECT
NULLIF (1, 1 );

->NULL
Mysql> Select nullif (1, 2 );
-> 1
If the parameters are not equalMySQLThe obtained value is expr1.