SQL NULLFunctions

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

Look at the "Products" table below:

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

Suppose "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 any "UnitsOnOrder" value is NULL, 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 an 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 similar to ISNULL(). However, it works a bit differently from Microsoft's ISNULL() function.

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

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

Or we can use the COALESCE() function, as shown below:

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

Other Extensions