Post reply

NOT IN

  • July 27, 2015 at 11:06 pm

    #398814

    Comments posted to this topic are about the item NOT IN

  • July 27, 2015 at 11:08 pm

    #1815296

    Good question & new piece of information for me, so thanx a lot.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • July 28, 2015 at 1:07 am

    This was removed by the editor as SPAM

  • July 28, 2015 at 2:55 am

    #1815315

    Also a good illustration of why NOT IN can be very risky. Using NOT EXISTS instead gives the result that would probably be intended in this scenario:

    SELECT Value

    FROM #tmpNewValue new

    WHERE NOT EXISTS (

    SELECT 1 FROM #tmpOldValue old

    WHERE old.Value = new.Value);

  • July 28, 2015 at 5:40 am

    #1815350

    Thanks for the question.

    Good day to all.

    ---------------
    Mel. 😎

  • July 28, 2015 at 5:40 am

    #1815351

    Agree.

    Richard Warr (7/28/2015)

    Also a good illustration of why NOT IN can be very risky. Using NOT EXISTS instead gives the result that would probably be intended in this scenario:

    SELECT Value

    FROM #tmpNewValue new

    WHERE NOT EXISTS (

    SELECT 1 FROM #tmpOldValue old

    WHERE old.Value = new.Value);

    ---------------
    Mel. 😎

  • July 28, 2015 at 6:40 am

    #1815376

    Here's a nice, straightforward -- but perhaps fuller -- explanation of what's going on here if you're still puzzled:

    https://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/[/url]

    EDIT: URL corrected for the web-friendly version of the page instead of print preview.

    Rich

  • July 28, 2015 at 6:43 am

    #1815377

    This behavior is a lot easier to understand if you think about NOT IN as a series of 'not equal' statements logically ANDed together:

    SELECT Value

    FROM #tmpNewValue

    WHERE Value != 3

    AND Value != 7

    AND Value != Null

    AND Value != 6

    Since any evaluation with Null is undetermined (which gets logically interpreted as false), the whole expression evaluates to false and no rows are returned.

  • July 28, 2015 at 6:55 am

    #1815383

    Alternative solutions:

    SELECT Value

    FROM #tmpNewValue

    WHERE Value not in(

    SELECT Value FROM #tmpOldValue where Value is not null

    )

    SELECT Value

    FROM #tmpNewValue

    WHERE Value <> all(

    SELECT Value FROM #tmpOldValue where Value is not null

    )

    Gerald Britton, Pluralsight courses

  • July 28, 2015 at 7:04 am

    #1815385

    NULL can cause grief if you're not careful.

  • July 28, 2015 at 9:07 am

    #1815453

    If the question could have been asked to check the returned value:

    SELECT Value

    FROM #tmpOldValue

    WHERE Value NOT IN (SELECT Value FROM #tmpNewValue)

    The answer would have been := 6

    Thanks.

  • July 28, 2015 at 9:28 am

    #1815459

    SQL-DBA-01 (7/28/2015)

    If the question could have been asked to check the returned value:

    SELECT Value

    FROM #tmpOldValue

    WHERE Value NOT IN (SELECT Value FROM #tmpNewValue)

    The answer would have been := 6

    Please, lets not start discussing all the possible answers if the question was different.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 28, 2015 at 10:29 am

    #1815490

    Easy... Thanks, Sergey!

  • July 28, 2015 at 10:35 am

    #1815492

    This was a good question that makes one really think about null value.

  • July 28, 2015 at 1:36 pm

    #1815548

    sestell1 (7/28/2015)

    This behavior is a lot easier to understand if you think about NOT IN as a series of 'not equal' statements logically ANDed together:

    Perfect way to restate the original point.

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

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

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