August 25, 2009 at 2:20 pm
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
August 26, 2009 at 1:42 am
SQAPro (8/25/2009)
...snip...
ISNULL is your friend!
Or the ANSI standard COALESCE for the ANSI pedants among us :o) Has the added advantage of being able to take more than two operands. It will simply return the first non-null one. I believe ISNULL is limited to two.
declare @i char(1), @j-2 varchar(1)
SELECT COALESCE(@i, @j-2, 'Both Missing')
-------------------------------Oh no!
October 27, 2009 at 9:01 am
Kevin Gill (8/14/2009)
The main impact of this that I've come across is that if you have two variables and both are null, the only complete equality check with ANSI NULLS on is :IF @a = @b-2 OR (@a IS NULL AND @b-2 IS NULL)
or shortened using my favourite TSQL function, to
IF COALESCE(@a,-1) = COALESCE(@b,-1)
-- Kev
Although the version with coalesce does work when both @a and @b-2 are null, it will also deliver TRUE is one of them is null and the other is -1; so it isn't something you should use.
Tom
October 28, 2009 at 3:54 am
Tom.Thomson (10/27/2009)
Kevin Gill (8/14/2009)
The main impact of this that I've come across is that if you have two variables and both are null, the only complete equality check with ANSI NULLS on is :IF @a = @b-2 OR (@a IS NULL AND @b-2 IS NULL)
or shortened using my favourite TSQL function, to
IF COALESCE(@a,-1) = COALESCE(@b,-1)
-- Kev
Although the version with coalesce does work when both @a and @b-2 are null, it will also deliver TRUE is one of them is null and the other is -1; so it isn't something you should use.
Sorry I was considering it as a given that you do not coalesce them to anything that they can possibly be - for example if it's a quantity then negative numbers are safe, if it's a date then the beginning or end of time would generally be safe. My example was just a simplified version where you might be comparing foreign keys which map to identity columns and thus can never validly be negative.
Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...
-------------------------------Oh no!
March 23, 2012 at 3:54 pm
I run your question with
SET ANSI_NULLS ON and SET ANSI_NULLS OFF, and the result is same. I use SQL Server 2012
?
hm
?
Igor Micev,My blog: www.igormicev.com
March 23, 2012 at 5:03 pm
Kevin Gill (10/28/2009)
My example was just a simplified version where you might be comparing foreign keys which map to identity columns and thus can never validly be negative.
Identity columns can easy be negative. Create them with a negative seed, or with a negative increment, or both is one way. setting identity insert on and placing explicit negative values is another. They are quite common when a large range is needed (bigger than 2 billion) - an identity column starting with seed 2**-31 and increment +1 gives the maximum possible range of values.
Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...
OK, maybe it should have been "it isn't something you should use unless you are absolutely certain you can pick a null-replacement value that is guaranteed to be safe". But remember that Murphy's law applies, so that most people will sometimes choose an "absolutely guaranteed safe" value that isn't safe at all.
Tom
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply