Dealing with Nulls

April 3, 2001

This is a nuts-and-bolts discussion of some potential issues with nulls. While BOL has extensive documentation, it doesn't give you a concise list of "gotchas." These are some of the things you should watch for when working with existing objects.

Watch your DML, particularly with inequality

Either provide for nullability in every statement where it is a potential issue or have a look at the database to see if you should be addressing it. For instance, if you have a table with 900,000 rows, 899,990 of which already have the desired value, you might use the following statement:

update t
set col3 = 'Y'
where col3 <> 'Y'
or col3 is null

If col3 isn't nullable, then the last line is superfluous. If it is nullable, you'd best include is null or risk not changing a row or two with a null in that column. Based on your data, you may be justified omitting a search condition and changing every row in the table.

There are many ways to determine if a field is nullable: the Enterprise Manager, sp_columns, information_schema.columns, or go fishing in syscolumns. Programmatically, you can use ADO's adFldIsNullable attribute.

Variables

Variables can be a major problem because they're often used in multiple expressions. They may evaluate to null; i.e., during division by zero, or they may inadvertently never be set.

My employer's senior management commissioned an Income Statement that allocates very specific corporate expenses into several components of Cost of Sales. I keep the sum of these expenses in a variable and use it in successive queries. It initially looked like this:

Click here for code example 1.

This worked well until our Finance people started running it on just part of the company. Since there were no corporate expenses, @nonGA was null and so was every expression that used it. Fortunately, the effect, while disconcerting, wasn't subtle and I quickly fixed it.

Click here for code example 2.

ISNULL replaces a null with a specified value. In this case, zero. Be careful that ISNULL does not hide problems with your queries. They may be returning null because of logic or typing errors. Speaking of typing errors, did you notice the distinction between the ISNULL function and the IS NULL predicate?

Consider the functions available to you

Beside ISNULL, you have NULLIF, COALESCE, and CASE at your disposal. CASE is a jack-of-all-trades. The others are more specialized.

Suppose Stephen Wynkoop is feeling generous and wants to see the effect of doubling his columnists' hits on a specific date. Further, everyone, even columnists with no hits that day, gets at least 10.

He'll probably left join his columnist table with his hit table. Because many rows will return null hits, he can't just multiply them by two. Same for the columnists with one to four hits. They would get fewer than 10 when doubled. Here's what he might do:

select c.full_name, case when h.hits > 5 then h.hits * 2 else 10 end
from columnists c left join hitstats h on h.columnist_id = c.id
    and h.hit_date = 'April 1, 2001' -- arbitrary date for illustration
order by 2 desc

Columnists with no hits, or fewer than 5, are swept up by else and given 10.

COALESCE returns the first non-null argument given it. It can be more concise than CASE.

NULLIF(arg1, arg2) returns null if its two arguments are equal, and arg1's value if not. It is most useful when combined with other functions.

Say a college sends solicitations to its alumni. Part of the solicitation is a reminder of how much they contributed last time. Their table doesn't allow nulls so years without contributions have the value zero.

select alum, coalesce(nullif(contrib_2000,0),nullif(contrib_1999,0),nullif contrib_1998,0),0)
from contributions

If the alumnus's 2000 contribution is zero, NULLIF will convert it to null and COALESCE will move on to the next year. Otherwise, NULLIF will return the year's contribution and COALESCE will stop there. The last zero is a catchall for alumni who have not contributed in those years. If the last zero is omitted, those alumni will return null.

Aggregates

Aggregates (SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP) are straightforward as they ignore null values. So does COUNT. The only issue you should ever have is running the function against a column that has all null values; i.e., one that was just added to the table and not yet populated. The server will return null.

COUNT(*) counts all rows, including rows with nulls. The college can use this distinction to analyze its 2000 contributions. Remember that their table doesn't allow nulls.

select count(*) from contributions                     -- count of all alumni in the table
select count(nullif(contrib_2000,0)) from contributions -- count of alumni who contributed in 2000
select avg(contrib_2000) from contributions            -- average contribution from all alumni
select avg(nullif(contrib_2000,0)) from contributions   -- average from alumni who contributed

SELECT notes

  1. For GROUP BY and DISTINCT, null values are considered equal.
  2. For ORDER BY, nulls are considered the lowest possible value; they are at the beginning of an ascending list.
  3. ANSI compatibility has considerable effects on the results of evaluating nulls. For instance, whether "= null" will work. See below.

ANSI compatibility

I have to refer you to your version's (or versions') BOL for the gory details. Some things to keep in mind are:

  1. Your server's version. 6.5 and below behave differently than later versions.
  2. The database's compatibility level (sp_dbcmptlevel).
  3. The database options (sp_dboption).
  4. How you connect to the server. ODBC and OLE DB give different default ANSI settings than DB-Library.
  5. The settings can be changed session-by-session; they're not necessarily uniform or under the control of the dbo or sa.
  6. Depending on the server version, the behavior of stored procedures can depend upon either what the settings were when the procedure was compiled or what they are when the procedure is run.







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers