Ifnull, Nullif, and isnull usage in MYSQL

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.