Somewhere way back in the beginning of everything database, you learn that nulls are bad. Nulls cause problems with queries. Your chosen language doesn't work well with nulls. Nulls cause little puppies to die. All that stuff.

As I was transitioning through stage 4 of the 19-stage developer life-cycle (Peanut) I grew terrified of nulls, and viewed them with some kind of morbid curiosity.

Then I discovered Code Complete by Steve McConnell. This moved me into stage 5 (Wally) and I started to realise there is no black or white in software development. Goto is not necessarily evil, and null's not all that bad either.

Not a value

The first thing you've got to remember is (and this is the crux of all the cool stuff you can do with null), null is not a value. It's a state. What does that mean exactly? Well, null means that no value has been assigned to that particular column for that particular row.

This makes it extraordinarily difficult for null to participate in any other kind of operation, as the rules for null in any operation are pretty much impossible to define. Which leads us to:

Anything and null is null

This is as absolutely straight forward as it sounds. Memorise this, and you're golden.

Based on what we already know about null (that it's not a value), how would you evaluate this:

26 + NULL = ??

Well, what do you think it would be? The answer is there is no answer. Null is not a value, so how would you go about adding it to 26?

So the other rule to remember is; anything and null is null. That goes for string concatenation, math, or the New Hebrides.

How is this even remotely fun?

It's not, really. But if you think outside of the box for a second you can achieve some pretty nifty things.

Lets imagine you have a requirement to format an address in a specific way. If the address has a unit number, you have to format it like so:

Unit 3, 26 Smith Street

If there's no unit, you have to format it:

26 Smith Street

And now you've got to do all that in a set operation (i.e., no cursors for you...). So you can't build up a temporary string and output that. Null to the rescue.

   1: select
   2:     isnull('Unit ' + UnitNumber + ', ', '') +
   3:     isnull(StreetNumber + ' ', '') +
   4:     isnull(StreetName + ' ', '') +
   5:     isnull(StreetType, '')
   6: from
   7:     CustomerAddress
   8: where
   9:     AddressID = blahblahblah...

How does this work? Well, the trick is in null. If the UnitNumber column is null, then the expression 'Unit ' + UnitNumber will resolve to null. After that, the ISNULL function takes care of swapping the null for an empty string.

So the null wipes out the text preceding it, forcing it to null. Nifty trick, isn't it? See if you can bend your mind with a few other uses for null. Lookup the null related functions (ISNULL, NULLIF etc) in SQL Server Books Online.