I have two data extensions that I need to join and filter. One stores member data for a product, the other stores opt-in status for all products. The DE with opt-in status can have multiple instances of the same person, one for each product they're opted in to. I need to find people who are in the members table who either don't have a record that associated with the product or who are associated with the product but have opted out.
I've tried several different methods of finding this result. Pulling records that are opted out of the product gives me too low of a number because it's not accounting for people who aren't associated with the product at all. Pulling records who aren't associated with the product or are opted out of the product gives me too high of a number because it's pulling in the instances of people who are associated with multiple other products.
At this point, I have two queries. One will find the primary keys for all records that are opted in to the product and store them in a data extension called "Subscribed Emails." It runs just fine and returns about 6.6 million records. The query below should just find primary keys in the members table that don't exist in the subscribed emails table. However, it keeps timing out. The members table has around 13 million records in it. Is it possible that comparing 13 million and 6.6 million records is too much work? What can I do to simplify this query even further?
select m.AccountID from members m where not exists ( select AccountID from [Subscribed Emails] )