SQL NULL Functions

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

Look at the accompanying "Products" table:

Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 Optimum Nutrition Serious Mass Weight Gainers 10.45 12 15
2 Whey Protein 32.56 10  
3 Optimum Nutrition Serious Mass Weight Gainers 15.67 9 6

Suppose that the "UnitsOnOrder" section is discretionary, and may contain NULL values.

We have the accompanying SELECT statement:

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

In the precedent above, if any of the "UnitsOnOrder" values are NULL, the outcome is NULL.

Microsoft's ISNULL() work is utilized to determine how we need to treat NULL values.

The NVL(), IFNULL(), and COALESCE() capacities can likewise be utilized to accomplish the equivalent result.

In this case we need NULL qualities to be zero.

Below, if "UnitsOnOrder" is NULL it won't hurt the figuring, on the grounds that ISNULL() restores a zero if the esteem is NULL:

MS Access

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

SQL Server

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


Oracle does not have an ISNULL() work. Be that as it may, we can utilize the NVL() capacity to accomplish the equivalent result:

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


MySQL has an ISNULL() work. Be that as it may, it works somewhat not quite the same as Microsoft's ISNULL() function.

In MySQL we can utilize the IFNULL() work, as this:

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

or we can utilize the COALESCE() work, as this:

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