How is the difference between ifnull and nullif functions used in mysql? I thought it was actually reversed. Otherwise, let's take a look at the usage of these two functions.
IFNULL (expr1, expr2)
If expr1 is not NULL, 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 or MySQL must store the returned value of IFNULL () in a temporary table in internal memory:
Create table tmp select ifnull (1, 'test') AS test;
In this example, the test column type is CHAR (4 ).
NULLIF (expr1, expr2)
If expr1 = expr2 is true, the return value is NULL. Otherwise, the return value is expr1. This is the same as case when expr1 = expr2 then null else expr1 END.
Mysql> select nullif (1, 1 );
-> NULL
Mysql> select nullif (1, 2 );
-> 1
Note: If the parameters are not equal, the value obtained by MySQL twice is expr1.