Latest web development tutorials

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

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

Look at the following "Products" table:

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

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

We use the following SELECT statement:

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

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

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

NVL (), IFNULL () and COALESCE () function can also achieve the same results.

Here, we hope NULL value is 0.

Now, 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 did not ISNULL () function. However, we can use the NVL () function to achieve the same result:

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


MySQL also has similar ISNULL () function. But it works with Microsoft's ISNULL () function is a bit different.

In MySQL, we can use 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