SQL NULL Functions

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

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 any of the "UnitsOnOrder" values ​​are NULL, the result is NULL.

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

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

Here we want the NULL value to be 0.

Next, 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 does not have an ISNULL () function. However, we can achieve the same result using the NVL () function:

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


MySQL also has functions similar to ISNULL (). However, it works a bit differently from 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