The Ifnull, Nullif and IsNull usage in Mysql are detailed _mysql

Today used in MySQL IsNull only found him and MSSQL or a little different, now a simple summary:

The use of Isnull,ifnull,nullif in MySQL is as follows:

Use of IsNull (expr):

If expr is null, then 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

It is usually wrong to use the null value of =.

The IsNull () function has some of the same attributes as the IS null comparison operator. See description for IS null.

Use of Ifnull (EXPR1,EXPR2):

If EXPR1 is not NULL, then 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); 
-> 
mysql> SELECT ifnull (1/0,10); 
-> 
mysql> SELECT 
ifnull (1/0, ' yes '); 

The default result value for Ifnull (EXPR1,EXPR2) is one of the more "generic" in two expressions, in the order of string, real, or
INTEGER. Suppose a table based on an expression, 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).

Use of Nullif (EXPR1,EXPR2):

If Expr1

= Expr2 is set up, the return value is NULL, otherwise the return value is EXPR1. This and case when expr1 = Expr2
The THEN NULL ELSE expr1 End is the same.

Mysql> SELECT 
nullif (1,1); 
-> NULL 
mysql> SELECT nullif (1,2); 
-> 1 '

If the arguments are not equal, then MySQL evaluates to two times expr1.

The above is a small set to introduce the MySQL in the Ifnull, Nullif and isnull usage details, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!