Handling NULL Values in SQL

NULL in SQL represents the absence of a value. When performing calculations with columns that might contain NULL values, you'll often get a NULL result, even if other parts of the calculation have valid numbers. Several functions provide ways to handle NULL values gracefully.

Handling NULLs in Calculations

Let's say we have a 'Products' table with some columns that might contain NULL values:

Products Table (Sample Data)

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15
2	Mascarpone	32.56	23	3
3	Gorgonzola	15.67	9	20

Notice that the 'UnitsOnOrder' column is optional and may contain NULL values.

The following query attempts to calculate the total value of the stock (UnitPrice * (UnitsInStock + UnitsOnOrder)). If 'UnitsOnOrder' is NULL for any product, the entire calculation will result in NULL for that product.

Problem Query


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

Output


ProductName     | Result
------------------------
(Results will be NULL for any product with NULL in UnitsOnOrder)

Solutions for Handling NULLs

Here are ways to handle NULL values in calculations, using different database systems' functions:

MySQL: IFNULL() and COALESCE()

MySQL's IFNULL() replaces NULL with a specified value. COALESCE() does the same but can handle multiple values.

MySQL IFNULL()


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

MySQL COALESCE()


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

Output


ProductName     | Result
------------------------
(Correct results even if UnitsOnOrder is NULL, using 0 as default)

SQL Server: ISNULL() and COALESCE()

SQL Server's ISNULL() replaces a NULL value, while COALESCE() can handle multiple values.

SQL Server ISNULL()


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

SQL Server COALESCE()


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

Output


ProductName     | Result
------------------------
(Correct results even if UnitsOnOrder is NULL, using 0 as default)

MS Access: IIF() and IsNull()

MS Access uses IIF() for conditional logic and IsNull() to check for NULL values.

MS Access


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

Output


ProductName     | Result
------------------------
(Correct results even if UnitsOnOrder is NULL, using 0 as default)

Oracle: NVL() and COALESCE()

Oracle uses NVL() or COALESCE() for handling NULLs.

Oracle NVL()


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

Oracle COALESCE()


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

Output


ProductName     | Result
------------------------
(Correct results even if UnitsOnOrder is NULL, using 0 as default)