The page explains how to handle NULL values in SQL using different functions across various database systems. Key points:

• Problem: When columns (like "UnitsOnOrder") contain NULL, any arithmetic operation involving NULL returns NULL.
• Solution: Use special functions to replace NULL with a specified value (often 0), ensuring calculations work as intended.
• Functions by System:
  • MySQL: IFNULL() and COALESCE() return an alternate value if the expression is NULL.
  • SQL Server: ISNULL() and COALESCE() serve the same purpose.
  • Oracle: NVL() or COALESCE() are used to substitute NULLs.
  • MS Access: IsNull() determines if a value is NULL and can be paired with IIF() for substitution.

Example queries show how to use each function within a SELECT statement. There's also a quiz question to test understanding of SQL Server's approach to NULL values.[1]

1

SQL NULL Functions

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

Look at the following "Products" table:

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.

Look at the following SELECT statement:

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

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.

Solutions

MySQL

The MySQL [IFNULL()](https://www.w3schools.com/sql/func_mysql_ifnull.asp) function lets you return an alternative value if an expression is NULL:

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

or we can use the [COALESCE()](https://www.w3schools.com/sql/func_mysql_coalesce.asp) function, like this:

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

SQL Server

The SQL Server [ISNULL()](https://www.w3schools.com/sql/func_sqlserver_isnull.asp) function lets you return an alternative value when an expression is NULL:

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

or we can use the [COALESCE()](https://www.w3schools.com/sql/func_sqlserver_coalesce.asp) function, like this:

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

MS Access

The MS Access [IsNull()](https://www.w3schools.com/sql/func_msaccess_isnull.asp) function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):

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

Oracle

The Oracle NVL() function achieves the same result:

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

or we can use the COALESCE() function, like this:

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