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)

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.

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

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.

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

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

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

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

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

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

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

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

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

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

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