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:

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:

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:

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:

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:

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: