Post reply

Not In

  • February 29, 2012 at 8:35 pm

    #391690

    Comments posted to this topic are about the item Not In

    Igor Micev,My blog: www.igormicev.com

  • February 29, 2012 at 8:36 pm

    #1453441

    Nice question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 29, 2012 at 10:54 pm

    #1453455

    Good Question. Thanks 😀

  • March 1, 2012 at 12:38 am

    #1453464

    Nice question, although the 'explanation' is confusing and does not explain anything.

    To find the real cause of the observed behavior, we can do a simple conversion. The statement

    s.Stat NOT IN (NULL, 'Idle')

    is equal to

    (s.Stat <> NULL) AND (s.Stat <> 'Idle')

    The comparison of any value with a NULL results to UNKNOWN, hence the result of the NOT IN condition is UNKNOWN for every row in the Statuses table (UNKNOWN, but not FALSE, as erroneously stated in the explanation). This is why the result set is empty.

  • March 1, 2012 at 1:04 am

    #1453468

    Good question that demonstrates the pitfalls of NULL values in IN.

    I have to agree with vk-kirov that the explanation is wrong. The result of the query has nothing to do with mismatch in data types, but is caused by the query processor converting the NOT IN statement to a series of <> operators.

    Just to prove that is has nothing to do with non-matching data types, but everything to do with the NULL value:

    create table #table(id int)

    insert into #table

    values

    (1),

    (2),

    (3),

    (4),

    (5),

    (null)

    declare @notin int

    -- The data type of @notin and column [id] matches, but still the query returns no rows.

    -- The execution plan shows why (the <> comparison with the NULL valued @notin variable)

    select * from #table

    where id not in (@notin,1)

  • March 1, 2012 at 1:13 am

    #1453470

    + 1.

    Nice question.

  • March 1, 2012 at 1:18 am

    #1453472

    Hi,

    I agree with you.

    From the reference

    "If the value of test_expression/subquery is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE" - so the result UNKNOWN seems to be covered (or explained) by FALSE.

    Alright, next time I'll try not to miss some things like this one.

    Thanks

    Igor Micev,My blog: www.igormicev.com

  • March 1, 2012 at 1:18 am

    #1453473

    Hi,

    I agree with you.

    From the reference

    "If the value of test_expression/subquery is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE" - so the result UNKNOWN seems to be covered (or explained) by FALSE.

    Alright, next time I'll try not to miss some things like this one.

    Thanks

    Igor Micev,My blog: www.igormicev.com

  • March 1, 2012 at 1:18 am

    #1453474

    Hi,

    I agree with you.

    From the reference

    "If the value of test_expression/subquery is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE" - so the result UNKNOWN seems to be covered (or explained) by FALSE.

    Alright, next time I'll try not to miss some things like this one.

    Thanks

    Igor Micev,My blog: www.igormicev.com

  • March 1, 2012 at 1:20 am

    #1453475

    No, I met this occasionally. It's inspired by practice.

    Igor Micev,My blog: www.igormicev.com

  • March 1, 2012 at 1:26 am

    #1453478

    vk-kirov (3/1/2012)

    Nice question, although the 'explanation' is confusing and does not explain anything.

    To find the real cause of the observed behavior, we can do a simple conversion. The statement

    s.Stat NOT IN (NULL, 'Idle')

    is equal to

    (s.Stat <> NULL) AND (s.Stat <> 'Idle')

    The comparison of any value with a NULL results to UNKNOWN, hence the result of the NOT IN condition is UNKNOWN for every row in the Statuses table (UNKNOWN, but not FALSE, as erroneously stated in the explanation). This is why the result set is empty.

    Thank you - makes much more sense!

  • March 1, 2012 at 1:50 am

    #1453484

    Good question, I got it right, perhaps more by intuition than fully understanding, so thanks for the explanations that others have given.

  • March 1, 2012 at 2:23 am

    #1453494

    Good question - it reminds me why I do not like nulls

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • March 1, 2012 at 2:51 am

    #1453507

    you can also show the explanation is wrong by changing the where clause to

    where s.Stat not in (cast(Null as varchar(20)), 'Idle')

    Whenever using Nulls in where clauses, comparisons should use "is null" or "is not null" rather than (explicit or implicit) "=" or "<>".

    Incidentally, if ansi_nulls is set Off then the first 3 rows would be returned.

  • March 1, 2012 at 2:54 am

    #1453509

    thanks for the question, and as usual the more interesting discussion that follows. 😀

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

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