SQL NULL Functions

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

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

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.


We have 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 we want to treat NULL values.


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


In this case we want NULL values to be zero.


Below, if "UnitsOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if the 


value is NULL:


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 does have an ISNULL() function. However, it works a little bit different from Microsoft's ISNULL() 


function.


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

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

or we can use the COALESCE() function, like this:

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

Dont Miss Another Post Connect With Us ! Enter your email address:

Tweet

Posted in: SQL Tutorial