Unknown NULLs

  • Comments posted to this topic are about the item Unknown NULLs

  • An entertaining question, but the formatting codes are all over it, making it difficult to read. I've seen this a few times now, so assume its a problem with posting the questions.

    Steve Hall
    Linkedin
    Blog Site

  • True - quite difficult to extract the actual content. Spose we could have waited for it on email...

    I mistakenly interpreted 'not @i = 1' as being the same as '@i 0' - too early in the morning for me I think...

    -------------------------------
    Oh no!

  • this was like, an entire lecture's worth of material in the database course i just finished taking 😛 made it a bit easier to figure out, lol

  • I believe SSC changed the code tags awhile back - I wonder if it happens to be that the question was submitted before the change and published afterwards.

  • Yep, code tags changed. formatting corrected.

  • hi, i'm new with sql server, i answered A and i got a "wrong answer". The thing is, you are using a "@i is null" not a "@i = NULL". I've read the following article:

    http://msdn.microsoft.com/en-us/library/aa259229(SQL.80).aspx

    and in one part it says: "For a script to work as intended, regardless of the ANSI nulls database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that may contain null values."

    so, the answer would be always A 'cos you're using a "is null" not a " = NULL"

    am i wrong? maybe i didn't understand the article.

    thanks!

  • So what's the correct method ? ANSI_NULLS should be ON or OFF ? By default I set it to ON on all distributed queries and when creating store procedures and manipulating indexes.

    SET ANSI_NULLS ON

    i is null

    i 0

    i = 0

    SET ANSI_NULLS OFF

    i is null

    i 0

    i 0

    SQL DBA.

  • 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.

    as for your question Sanjay, i'd suggest that you always use ANSI_NULLS on, so that you can avoid running into any problems when doing null comparisons.

  • Indeed. ANSI NULLS disabling is just a workaround I think for backwards compatibility with something.

    Essentially an 'IF A = B' statement is saying that is (A = B) returns true, then do whatever the IF suggests. so...

    With ANSI NULLS ON :

    NULL = NULL returns UNKNOWN so 'IF NULL = NULL will go down the ELSE route

    NULL NULL returns UNKNOWN so 'IF NULL NULL will go down the ELSE route

    NULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF route

    NULL IS NOT NULL returns FALSE so 'IF NULL IS NOT NULL will go down the ELSE route

    The difference with ANSI NULLS OFF is simply that the = operator and the 'IS' operator are treated the same. Instead of being treated as an unspecified value, NULL is treated as a 'value of NULL' if you see what I mean. Therefore

    With ANSI NULLS OFF :

    NULL = NULL returns TRUE so 'IF NULL = NULL will go down the IF route

    NULL NULL returns FALSE so 'IF NULL NULL will go down the ELSE route

    NULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF route

    NULL IS NOT NULL returns FALSE so 'IF NULL IS NOT NULL will go down the ELSE route

    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)

    Check the execution plan though, sometimes the longhand approach is considered more efficient if performance is important.

    -- Kev

    -------------------------------
    Oh no!

  • 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.

  • 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.

  • 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.

  • 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!

  • 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 😛

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply