SQL functions Nullif, NULL, ISNULL, coalesce

Source: Internet
Author: User
Tags null null

nullif function Nullif (expression1,expression2): Given two parameters Expression1 and Expression2, if two parameters are equal, NULL is returned, otherwise the first argument is returned. Equivalent to: case when Expression1=Expression2 then NULL ELSE Expression1. For example, select Nullif (1,1) returns Null,select Nullif (1,2) returns 1. There is a practical application, such as preventing release of 0 operations, you can use a/nullif (b,0), it is not afraid of B is 0, of course, in addition to 0 operations can also be judged by other means. Null NULL is a magical thing that represents a null value, an unknown value, and any number with which subtraction returns NULL. ISNULL function ISNULL (expression1,expression2): Given two parameters Expression1 and Expression2, if Expression1 is null, then return Expression2,  Otherwise, return Expression1. Equivalent to: case when Expression1 isNULL then Expression2 ELSE Expression1. For example, select ISNULL (NULL,1) returns 1,select ISNULL (1,2) returns 1. There is an actual application that can be substituted with a default value for null values, such as select ISNULL (email,'email not filled in') fromTable1, all emails are null, with'email not filled in'to replace. Coalesce function Coalesce (Expression1,expression2,expression3,......)      : Accepts a series of expressions or columns, returning the first non-null value. For example, select COALESCE (Null,null,4, Null,null,5), then return, if the parameters are null, then the error will be.

SQL functions Nullif, NULL, ISNULL, coalesce

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.