RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Accommodating Null Values in SQL Server : Page 2

The key to working with null values properly is to accommodate them consistently. Learn a few tricks that will help you do just that.


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 setting.

Figure 1. Results of Null-to-Null Comparison: By ANSI standards, any comparison that includes a null returns null.
The ANSI_NULLS 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:

USE AdventureWorks
SELECT EmployeeID 
FROM HumanResources.Employee
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 set—in 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:
USE AdventureWorks
SELECT EmployeeID 
FROM HumanResources.Employee
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:

SELECT list|* 
FROM table
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:
USE AdventureWorks
FROM Person.Contact
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:

Use AdventureWorks
SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Contact
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() as follows:
Use AdventureWorks
SELECT FirstName + ' ' + ISNULL(MiddleName, '') + LastName
FROM Person.Contact

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:

ISNULL(checkexpression, replacementvalue)
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 example, ISNULL() 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:

USE AdventureWorks
SELECT Contact, ISNULL(Suffix, 'None') 
FROM Person.Contact
That, the user understands.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date