SQL NULL function

SQL ISNULL (), NVL (), Ifnull (), and COALESCE () functions

Take a look at the "Products" table below:

p_id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Computer	699	25	15
2	Printer	365	36
3	Telephone	280	159	57

If "UnitsOnOrder" is optional, and can contain NULL values.

We use the following SELECT statement:

SELECT productname,unitprice* (Unitsinstock+unitsonorder) from products

In the example above, if there is a "unitsonorder" value of NULL, then the result is null.

Microsoft's ISNULL () function is used to specify how NULL values are handled.

NVL (), Ifnull (), and coalesce () functions can also achieve the same result.

Here, we want the NULL value to be 0.

The following, if "UnitsOnOrder" is null, is not conducive to the calculation, so if the value is null then ISNULL () returns 0.

SQL Server/ms Access

SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0)) from products

Oracle

Oracle does not have the ISNULL () function. However, we can use the NVL () function to achieve the same result:

SELECT productname,unitprice* (UNITSINSTOCK+NVL (unitsonorder,0)) from products

Mysql

MySQL also has a function like ISNULL (). But the way it works is a little different from Microsoft's ISNULL () function.

In MySQL, we can use the ifnull () function, just like this:

SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0)) from products

Or we can use the coalesce () function, just like this:

SELECT productname,unitprice* (Unitsinstock+coalesce (unitsonorder,0)) from products

SQL NULL function