SQL ISNULL (), NVL (), ifnull () and coalesce () functions

Source: Internet
Author: User

SQL ISNULL (), NVL (), ifnull () and coalesce () functions, which are often used in our SQL statements, let's take a look at the example tutorial.

p_id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 Jarlsberg 10.45 16 15
2 Mascarpone 32.56 23
3 Gorgonzola 15.67 9 20


Suppose that the "UnitsOnOrder" column is optional and may contain null values.

We have the following SELECT statement:
Select productname,unitprice* (unitsinstock+unitsonorder)
from Products
In the above example, if any " UnitsOnOrder "values are empty, and the results are ineffective. Microsoft's IsNull () function is used to specify how we want to handle null values. The NVL (), Ifnull () and Union () functions can also be used to achieve the same result. In this case, we want the null value to be zero. Below, if "UnitsOnOrder" is null it does not damage the calculation because IsNull () returns a value of 0 if Null:sql server/ms accessselect productname,unitprice* (Unitsi Nstock+isnull (unitsonorder,0))
from Products
 
Oracle
Oracle does not have the ISNULL () function. However, we can use the NVL () function to implement the same result select Productname,unitprice* (UNITSINSTOCK+NVL (unitsonorder,0))
from Products
 
MySQL
 
MySQL does have a isnull () function. However, its work is somewhat different from that of Microsoft's IsNull () function. We can use the Ifnull () function in MySQL, just like this: SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0))
from the products

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.