SQL NULL function

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

Please see the "Products" table below:

##2Mascarpone32.5623##3If "UnitsOnOrder" is optional and can contain NULL values.

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15

Gorgonzola	15.67	9	20

We use the following SELECT statement:

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)

FROM Products

In the above example , if any "UnitsOnOrder" value is NULL, then the result is NULL.

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

The NVL(), IFNULL() and COALESCE() functions can also achieve the same result.

Here, we want the NULL value to be 0.

Below, if "UnitsOnOrder" is NULL, it will not affect the calculation, because if the value is NULL, 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 achieve the same result using the NVL() function:

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))

FROM Products

MySQL

MySQL also has functions similar to ISNULL(). However, it works a bit differently than Microsoft's ISNULL() function.

In MySQL, we can use the IFNULL() function as follows:

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

Or we can use the COALESCE() function as follows:

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products