Finding Nulls Before They Do Damage
Any expression that encounters a null can return bad data. For instance, Figure 1
shows the results of comparing a null to a null. This statement has the potential to return no records, depending on your ANSI_NULLS
|Figure 1. Results of Null-to-Null Comparison: By ANSI standards, any comparison that includes a null returns null.|
option is off (False) by default. That means that any null-to-null comparison returns True instead of null. When this option is on (True), any null-to-null comparison returns null. This particular default violates ANSI standards, but it prevents errors that might otherwise occur. How you set this option is a matter of preference, but knowing how it determines null evaluation is important.
The following statement turns the option on so you can see the potential for trouble:
SET ANSI_NULLS ON
WHERE ManagerID = NULL
If you peruse the records, you'll find one record with a null value in the ManagerID
column. However, executing the preceding statement returns an empty result setin other words, it doesn't return the existing record. Even worse, instead of a traditional error, you probably wouldn't catch the "error" exemplified by the empty result set.
|Figure 2. IS NULL to Find Null Values: Regardless of the ANSI_NULLS setting, IS NULL catches null values.|
The correct way to find null values, regardless of this option's setting, is to use IS NULL
, as follows:
WHERE ManagerID IS NULL
As you can see in Figure 2
, you don't need to know the ANSI_NULLS
option's setting. In addition, you guarantee that the statement will work correctly should someone reset that option.
Using IS NULL, you can easily find null values in a specific column, but searching several or all columns is a bit more complicated. At first, you might consider a complex statement using numerous OR clauses, but that isn't necessary. Simply concatenate fields using the following syntax:
WHERE nulcol1 + nulcol2 + ... IS NULL
You can mix and match the columns or concatenate them all. Figure 3
shows the results of concatenating three fields:
WHERE Suffix + EmailAddress + Phone IS NULL
|Figure 3. Results of Concatenating Three Fields: You can mix and match columns or concatenate them all.|
You still have to explicitly reference each column that you're checking, but this method saves a bit of typing. Don't try to concatenate columns that aren't nullable, as doing so will return an error. This method works only with nullable fields.
While this behavior can come in handy, it also can return unexpected results. For instance, the following statement concatenates first, last, and middle names:
SELECT FirstName + ' ' + MiddleName + ' ' + LastName
What Figure 4
shows might be a surprise: The ANSI_NULLS
setting makes no difference in this case. If any name value is null, the resulting value is null. As shown in Figure 5
, you can avoid this problem using ISNULL()
SELECT FirstName + ' ' + ISNULL(MiddleName, '') + LastName
Figure 4. ANSI_NULLS Setting Makes No Difference: Concatenating can return null values.
Figure 5. ISNULL() to Catch Nulls: Use ISNULL() to catch null values.
You don't have to wrap every column in ISNULL(); if column properties require a value, there's no possibility of a null. (FirstName and LastName aren't nullable.) The ISNULL() function replaces any null value with a specified replacement value using the following syntax, where checkexpression is the value or column you're checking for null values and replacementvalue is the value you're using to replace nulls:
In this case, the only nullable column in the example is MiddleName
. Be aware that records with no middle name value will end up with two space characters between the first and last names.
|Figure 6. ISNULL() Function Displays "None": Give users more meaningful information when they encounter null values.|
SQL Server's CONCATENATE_NULL_YIELDS_NULL
setting will affect the results of any expression that concatenates null values. The default is on (True), which means concatenating a null returns null. When turned off (False), SQL Server ignores the null, returning the concatenated results of the other non-null values. Similarly to the earlier IS NULL
eliminates unexpected results should someone reset this option.
Even when a null value does no damage, it doesn't provide much value to users. They would benefit from more meaningful data. When viewing data, users might guess at what NULL means, but do you really want them to? Instead of displaying NULL (Figures 3 and Figure 4), the ISNULL() function in the following statement displays "None" as shown in Figure 6:
SELECT Contact, ISNULL(Suffix, 'None')
That, the user understands.