How to filter non-existing records in SQL

  • aveek22

    SSC-Addicted

    Points: 484

    Comments posted to this topic are about the item How to filter non-existing records in SQL

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • Bob Gaydos

    Newbie

    Points: 4

    @aveek22, thanks for the article and other ways of looking at a familiar problem.  When you get around to taking a closer look at the execution plans, I'd like to see some scalability studies of the various solutions as well.

  • aveek22

    SSC-Addicted

    Points: 484

    Thanks @bob-56901. I'll add that to my to-do list. Will keep you posted of my upcoming posts on this topic.

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • John N Hick

    Ten Centuries

    Points: 1315

    Thanks, aveek22.

    I, too, use NOT EXISTS but I wonder if by using a co-related subquery  we are doing a performance no-no...

  • Chris Wooding

    SSCarpal Tunnel

    Points: 4343

    There is a very good article on this subject (including a discussion of the execution plans) on the link below.

    https://www.sqlservercentral.com/blogs/left-outer-join-vs-not-exists

  • aveek22

    SSC-Addicted

    Points: 484

    Thank you for sharing the link, @Chris-Wooding.

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • MMartin1

    One Orange Chip

    Points: 27502

    Thank you for the article. The NOT IN operation > I would steer clear of this for performance and the potential for it to blow up if there is an NULL in the customerID column for orders. For performance, just on about 1500 rows of customers and 1500+ of orders that I used on my machine, the NOT IN operation was about 3x slower than the left join and not exists operations.

    • This reply was modified 9 months ago by  MMartin1. Reason: less wording

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

Viewing 7 posts - 1 through 7 (of 7 total)

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