How to filter non-existing records in SQL

Question

How to filter non-existing records in SQL

aveek22

Mr or Mrs. 500

Points: 509
More actions
November 4, 2019 at 12:00 am

#3689000

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/

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

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

Bob Gaydos Newbie Points: 4 More actions · Answer 1

@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 Mr or Mrs. 500 Points: 509 More actions · Answer 2

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: 1320 More actions · Answer 3

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: 4530 More actions · Answer 4

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 Mr or Mrs. 500 Points: 509 More actions · Answer 5

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: 27782 More actions · Answer 6

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 4 years, 6 months ago by MMartin1. Reason: less wording

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

MMartin1 One Orange Chip Points: 27782 More actions · Answer 7

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

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