Following up on the previous article SQL – The Power of NULLs, here are some of the things to keep in mind when you do decide to use NULL in your work.
By far, one of the most common mistakes is to assume that <> (not equal) or even = (equal) logic covers NULLs. That is completely incorrect in both cases; NULL is neither equal to anything nor is it not equal to anything, including itself. Consider the following code:
[syntax_prettify]SELECT CASE WHEN 1<>NULL THEN 1 WHEN NULL=NULL THEN 0 END[/syntax_prettify]
Of course the end result will be neither- NULL :)
What about this one: will WHEN help us be able to do this comparison on the fly?
[syntax_prettify]SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END[/syntax_prettify]
Just a little- the ELSE logic will kick in covering “all other” possibilities, but the CASE logic still relies on = and <> just the same way- NULL is still nothing, it still never rings TRUE.
So what are we left with? Well, you can always try using ISNULL (the great counterpart of NULLIF) and COALESCE in more advanced cases. Either way, remember- NULLs can help you do really interesting things with your DB and your queries, you just need to be careful, that’s all.