Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Unknown NULLs Expand / Collapse
Author
Message
Posted Friday, August 14, 2009 12:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 2, 2009 8:46 AM
Points: 875, Visits: 313
kramaswamy (8/13/2009)
i think you just misinterpreted the answers. when he says "i is null", he means that the result of "If @i is null" evaluates to true, and so he's just printing "i is null".

to break down the question,

"Declare @i int"

@i is declared as an int, and not given an initial value. as such, when initialized, it defaults to a value of NULL.

"--Test #1
If @i is null
Print 'i is null'
Else
Print 'i is not null'"

test 1 is checking to see if the value of @i is null. in this case, since it was defaulted to null, test 1 evaluates to true. on the true case, it prints "i is null", so that's what is displayed.

"--Test #2
if @i = 0
Print 'i = 0'
Else
Print 'i <> 0'"

test 2 is checking to see if the value of @i is 0. since @i is null, it fails this check, as @i = 0 evaluates to "UNKNOWN" due to @i being null. so, the else case is used.

"--Test #3
If not @i = 0
Print 'i <> 0'
Else
Print 'i = 0'"

test 3 is checking to see if the value of @i is not 0. again, since @i evaluates to null, and null is not equal to zero, the statement returns false. and not false is true, so the first case is used, and "i <> 0" is printed.

ANSI_NULLS causes the script to return UNKNOWN when comparing any result against NULL.


Wait a minute! Doesn't that mean the answer is "i is null" followed by two "i <> 0" statements? Which would then mean the answer should be B if ANSI_NULLS is set on.

I'm confused, now.
Post #771218
Posted Friday, August 14, 2009 12:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 828, Visits: 1,702
Actually, you're right - I was wrong in what I wrote earlier.

When you have "IF NOT @i = 0", that evaluates into "IF NOT (UNKNOWN)", which results in FALSE.

IF (UNKNOWN) and IF NOT (UNKNOWN) both evaluate to false, because UNKNOWN is neither true nor false. that is - if ANSI NULLS are on.
Post #771229
Posted Friday, August 14, 2009 3:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 2, 2009 8:46 AM
Points: 875, Visits: 313
kramaswamy (8/14/2009)
Actually, you're right - I was wrong in what I wrote earlier.

When you have "IF NOT @i = 0", that evaluates into "IF NOT (UNKNOWN)", which results in FALSE.

IF (UNKNOWN) and IF NOT (UNKNOWN) both evaluate to false, because UNKNOWN is neither true nor false. that is - if ANSI NULLS are on.


Thanks for clarifying.
Post #771354
Posted Tuesday, August 25, 2009 1:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
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!
Post #777043
Posted Tuesday, August 25, 2009 1:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 828, Visits: 1,702
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 :P
Post #777049
Posted Tuesday, August 25, 2009 2:20 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
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 :P


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
Post #777072
Posted Wednesday, August 26, 2009 1:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:31 AM
Points: 1,109, Visits: 306
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 varchar(1)
SELECT COALESCE(@i, @j, 'Both Missing')


-------------------------------
Oh no!
Post #777329
Posted Tuesday, October 27, 2009 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 7,923, Visits: 9,649
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 OR (@a IS NULL AND @b 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 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
Post #809377
Posted Wednesday, October 28, 2009 3:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:31 AM
Points: 1,109, Visits: 306
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 OR (@a IS NULL AND @b 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 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!
Post #809777
Posted Friday, March 23, 2012 3:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:24 PM
Points: 3,098, Visits: 3,231
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,
SQL Server developer at Seavus
www.seavus.com
Post #1272156
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse