kramaswamy (8/25/2009)
SQAPro (8/25/2009)
Glad to see this get covered. I know this tripped me up early on in the process of learning SQL..A more experienced person (our DBA) explained it to me thusly
"null is never equal to, less than, greater than, or to anything." "ANY attempt to compare NULL with anything else, will fail"
in other words, pardon the pun but, 'null is beyond compare.'
This was followed by "if you need to do such a comparison in a case where some of the 'values' may be null, then learn to use ISNULL"
ISNULL is your friend!
'Course that's dependent upon ANSI_NULLS 😛
And since you might not always know or be in control of the ANSI_NULLS setting, then better to just use isnull. That also allows you to control how you treat a null, or substitute some standard value for it. (presuming you don't use 'is null' earlier to give nulls special treatment)
IF ISNULL(@i, 0) = 0 -- treat nulls as zero
IF ISNULL(@i, -1) = 0 -- treat nulls as NON-zero