SQL NULL Functions

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

Consider the following “Products” table:

Imagine that the “UnitsOnOrder” column is optional and may contain NULL values.

Review the following SELECT statement:

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

In the provided example, if any of the “UnitsOnOrder” values are NULL, the result will also be NULL.

MySQL

The MySQL IFNULL() function allows you to specify an alternative value if an expression evaluates to NULL.

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

Alternatively, we can utilize the COALESCE() function, as shown here:

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

SQL Server

The SQL Server ISNULL() function allows you to specify an alternative value when an expression evaluates to NULL.

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

Alternatively, we can utilize the COALESCE() function, demonstrated as follows:

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

MS Access

The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise it returns FALSE (0).

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

Oracle

The Oracle NVL() function accomplishes the same outcome.

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

Alternatively, we can utilize the COALESCE() function in the following manner:

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