SQL NULL function
SQL ISNULL (), NVL(), IFNULL(), and COALESCE() functions
Take a 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.
Let's use the following SELECT statement:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products
In the example above, if the "UnitsOnOrder" value is NULL, the result is NULL.
Microsoft's ISNULL() function is used to specify how NULL values are handled.
NVL(), IFNULL(), and COALESCE() functions can achieve the same results.
Here, we want the NULL value to be 0.
Below, if "UnitsOnOrder" is NULL, the calculation is not affected because IFULL() returns 0 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 also has ISNULL()-like functions. But it works a little 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