SQL Tutorial SQL Advanced SQL Database SQL References

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



The SQL NULL functions (IFNULL(), ISNULL(), COALESCE() and NVL()) are used to provided alternate value of a column if it contains NULL value.

Example:

Consider a database table called Product with the following records:

ProductNamePriceStockQuantityOrderQuantity
Apple1.0010020
Banana1.2512030
Orange2.15105NULL
Watermelon3.507515

If the OrderQuantity is optional and can contain NULL values. The SQL code mentioned below will give NULL value.

SELECT *, Price * (StockQuantity + OrderQuantity) AS Inventory
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULLNULL
Watermelon3.507515315.0

To avoid such situations, the NULL function is used which provides alternative value to a column if it contains NULL value.

MySQL

The MySQL IFNULL() function or COALESCE() function can be used to provide an alternative value if column value is NULL:

SELECT *, Price * (StockQuantity + IFNULL(OrderQuantity, 0)) AS Inventory
FROM Product;

OR

SELECT *, Price * (StockQuantity + COALESCE(OrderQuantity, 0)) AS Inventory
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

SQL Server

The SQL Server ISNULL() function can be used to provide an alternative value if column value is NULL:

SELECT *, Price * (StockQuantity + ISNULL(OrderQuantity, 0)) AS Inventory
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

Oracle

The Oracle NVL() function can be used to provide an alternative value if column value is NULL:

SELECT *, Price * (StockQuantity + NVL(OrderQuantity, 0)) AS Inventory
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

MS Access

In MS Access, IIF() function along with ISNULL() function can be used to achieve the same result:

SELECT *, 
Price * (StockQuantity + IIF(ISNULL(OrderQuantity), 0, OrderQuantity)) AS Inventory
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0