SQL NULL Functions
SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions
Look at the following "Products" table:
|1||Optimum Nutrition Serious Mass Weight Gainers||10.45||12||15|
|3||Optimum Nutrition Serious Mass Weight Gainers||15.67||9||6|
Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.
We have the following SELECT statement:
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:
Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:
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:
or we can use the COALESCE() function, like this: