How to filter non-existing records in SQL

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

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

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

  • Thanks, aveek22.

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

  • 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

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

  • 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 5 years, 1 month ago by  MMartin1. Reason: less wording

    ----------------------------------------------------

  • A report user would still want to see not only what items sold, but what did not sell all in the same view. So doing a right join (sales events to products table) would accomplish the whole job. Substitute a zero for a null (isnull etc...) in the results

     

    ----------------------------------------------------

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

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