SQL ISNULL (), NVL (), ifnull () and coalesce () functions

SQL ISNULL (), NVL (), ifnull () and coalesce () functions, which are often used in our SQL statements, let's take a look at the example tutorial.

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 above example, if any " UnitsOnOrder "values are empty, and the results are ineffective. Microsoft's IsNull () function is used to specify how we want to handle null values. The NVL (), Ifnull () and Union () functions can also be used to achieve the same result. In this case, we want the null value to be zero. Below, if "UnitsOnOrder" is null it does not damage the calculation because IsNull () returns a value of 0 if Null:sql server/ms accessselect productname,unitprice* (Unitsi Nstock+isnull (unitsonorder,0))
from Products
 
Oracle
Oracle does not have the ISNULL () function. However, we can use the NVL () function to implement the same result select Productname,unitprice* (UNITSINSTOCK+NVL (unitsonorder,0))
from Products
 
MySQL
 
MySQL does have a isnull () function. However, its work is somewhat different from that of Microsoft's IsNull () function. We can use the Ifnull () function in MySQL, just like this: SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0))
from the products