Developer

Differences between Jet's IsNull() and T-SQL's COALESCE() functions

Null values are handled differently by Access when compared to SQL Server. Get a hands-on explanation of the appropriate null functions for each database and improve your application's reliability.


By Susan Sales Harkins and Doris Manning

Failure to contemplate possible null values and handle them properly can lead to runtime errors, or even worse, bad data—and you really don't want your users and clients to experience either at your expense.

Null values present a special problem for Access and SQL Server developers. Although Access is frequently used as a SQL Server front end through linked tables or ADP files (Access project), the two systems don't use the same functions to handle null values. Knowing the appropriate functions can mean the difference between a happy client and a past client.

Handling null values in an Access MDB file
Jet supports the following functions when looking for null values in an MDB database:
  • Use Is Null in criteria expressions and SQL WHERE clauses.
  • Use the IsNull() function with Visual Basic for Applications (VBA).

Once you encounter a null value, use the Nz() function, the IIF() function, and the VBA If statement to handle it. The IIF() function is good for query and criteria expressions. Use VBA's If statement in a module, and the Nz() function will work anywhere.

Suppose you want to find Null Region values in the Northwind Employees table. To do so, you could use either of these expressions:
Reg: Nz([Region],"N/A")
Reg: IIf(IsNull([Region]),"N/A",[Region])


Both expressions will return the string N/A when a Null value is encountered in the Region field, as shown in Figure A.

Figure A
Usurp null values and return something more meaningful.


There's really no reason to use IIF() unless you want to return something other than the checked value itself when that value isn't null. For instance, instead of returning the Region value, you might want to return Washington. In this case, the Nz() function won't work. Instead, use the following IIF() function:
IIF(IsNull([Region), "N/A", "Washington)

(You'd use the If statement in a VBA module to return the same results.)

When working in a VBA module, you might use Is Null in a SQL WHERE clause to find null values or Nz() to trap them before they can return an error. For instance, the following procedure would filter the Employees form's recordset by including only those records where the Region value is null:
DoCmd.OpenForm "Employees",,, "Region Is Null"

Good code doesn't ignore null values, it handles them. For example, the procedure in Listing A displays each employee's Region value. If no value exists, the message is confusing at best, as shown in Figure B. The dialog box on the left in Figure B shows the procedure when you don't handle possible null values in the Region field; the figure to the right shows the result of accommodating those null values. If no Region value exists, the Nz() function returns the generic not applicable string N/A instead of returning nothing at all.

Figure B
Not accommodating null values can cause confusion.


Handling null values with Transact-SQL
You can't find or handle nulls exactly the same way in SQL Server or an Access ADP file because both use Transact-SQL (T-SQL), not Jet SQL. Whether you're upsizing an MDB or simply using Access as a front end to SQL Server tables, you must deal with nulls differently than you do when working in an MDB file.

T-SQL doesn't support the Nz() function. You must use ISNULL() instead. In addition, T-SQL is more flexible than Jet where null values are concerned:
  • The NULLIF() function returns a null value when both expressions are equivalent or have the same result. Use this function when you want to eliminate values from an aggregate function.
  • The COALESCE() function returns the first non-null value (or expression) among its arguments. This function eliminates the need to include numerous Is Null and Is Not Null checks before evaluating expressions.

Jet doesn't have an equivalent for either NULLIF() or COALESCE().

About NULLIF()
T-SQL's NULLIF() function doesn't identify null values the way ISNULL() does. Instead, NULLIF() returns a null value when both values (or expressions) are equivalent. Use this function in the following form, where expression is a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators—in other words, most any valid expression:
NULLIF(expression, expression)

When both expressions are equivalent, NULLIF() returns a null value of the same type as the first expression (such as NUMERIC NULL, REAL NULL, FLOAT NULL, and so on). When the two expressions aren't equivalent, NULLIF() returns the data type of the first expression.

Suppose you want to return the average discount value applied to a particular order. As long as all items in the order are discounted, you have no problem. However, if one or more items aren't discounted, you may want to consider using NULLIF() to get a more accurate average.

Order 10251 has this problem; the order contains three items but only two were discounted. (You'll find this order in the NorthwindCS demo file that comes with Access.) For example, the following view returns the value 0.0333333338300387:
SELECT OrderID, AVG(Discount) AS AvgDiscount
FROM dbo.[Order Details]
GROUP BY OrderID
HAVING (OrderID = '10251')


This is because the AVG() function considers all three discount values, even the zero value. Adding the NULLIF() function, as shown in Figure C, returns the value 0.0500000007450581. Since the AVG() function ignores null values, the NULLIF() function eliminates the zero value as an operand.

Figure C
Add a NULLIF() function to eliminate values from an aggregate function.


About COALESCE()
T-SQL's COALESCE() function lets you specify a number of expressions and COALESCE() will return the first non-null value from the group. Use the following form where expression is any data type and can equal most any valid expression:
COALESCE (expression [,…n])

The function doesn't limit the number of arguments, but they must all be of the same data type; the function returns the same data type as expression. When all arguments are NULL, COALESCE() returns NULL.

Remember the earlier MDB example that displays N/A when the Region value's null? Using COALESCE() provides a better solution (remember, COALESCE() isn't available in an MDB file). Instead of displaying the string N/A, the following view displays the employee's Country value:
SELECT TOP 100 PERCENT LastName, FirstName, COALESCE (Region, Country) AS Area
FROM dbo.Employees
ORDER BY LastName, FirstName


Figure D shows the results. If the Region value is null, COALESCE() returns the corresponding Country value instead.

Figure D
Use COALESCE() to return something other than a null value.


Heading off nulls
Regardless of the part you play in the development of a database, one thing's for certain—fail to handle null values and your responsibilities may come to an abrupt end. Knowing how to find and handle null values is the key, and for better or worse, you can't use the same tactics in both an MDB and an ADP file (or SQL Server). Be prepared by knowing how to find null values in both systems.

Editor's Picks