Post reply

Not In

  • March 29, 2012 at 5:11 am

    #1466282

    Hi, I don't agree with you(noticed that others made same comment).

    This is a typical ansi setting issue if it will work as explained or not.

    Try same query setting "ansi_nulls on" and then set it to "off".

    Question was not given with enough info about ansi_settings... 🙁

    Topic is relevant, though...(!)

    Rgrds

  • October 10, 2012 at 3:50 am

    #1547552

    Good question...

    come to know that values in NOT IN works as AND not OR

    i resposed to worng option 🙁

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 5, 2015 at 6:33 am

    #1767734

    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.

    That`s better explanation.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • October 11, 2015 at 11:13 am

    #1832721

    Agree with vk-kirov's explanation. I tested the statement with the other two values and I believe Null is the issue here. You slimy Null. :crazy:

Viewing 4 posts - 46 through 48 (of 48 total)

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