Not In

  • Comments posted to this topic are about the item Not In

    Igor Micev,My blog: www.igormicev.com

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

  • Good Question. Thanks 😀

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

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

  • + 1.

    Nice question.

  • 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

  • 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

  • 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

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

    Igor Micev,My blog: www.igormicev.com

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

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

  • 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

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

  • 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