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

SQL isnull (), NVL (), IFNULL () and COALESCE () functions are commonly used in 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

Assume that the "UnitsOnOrder" column is optional and may contain null values.

We have the following SELECT statements:
SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products
In the preceding example, if any "UnitsOnOrder" value is null, the result is invalid. Microsoft's ISNULL () function is used to specify how to handle null values. The NVL (), IFNULL () and Union () functions can also be used to achieve the same result. In this case, the null value is zero. Below, if "UnitsOnOrder" is NULL, it will not damage the calculation because ISNULL () returns a zero value if it is NULL: SQL Server/MS AccessSELECT ProductName, unitPrice * (UnitsInStock + ISNULL (UnitsOnOrder, 0 ))
FROM Products
 
Oracle
Oracle does not have the ISNULL () function. However, we can use the NVL () function to implement the same results SELECT ProductName, UnitPrice * (UnitsInStock + NVL (UnitsOnOrder, 0 ))
FROM Products
 
Mysql
 
MySQL does have an ISNULL () function. However, its works are a little different. Microsoft's ISNULL () function. In MySQL, we can use the IFNULL () function, just like this: SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL (UnitsOnOrder, 0 ))
FROM Products