How to correctly exclude records when tables are connected with one to many relationship

Transact-SQL
1

Overview: I am working with two tables that are connected with one-to-many relationship
(i.e. one record from Contacts table can have many rows / records in CustomerExclusion table

Contacts
CustomerID CustomerName Country
1 Alfreds Futterkiste Germany
2 Ana Trujillo Emparedados y helados Mexico
3 Antonio Moreno Taquería Mexico
4 Around the Horn UK
5 Berglunds snabbköp Sweden
6 Blauer See Delikatessen Germany
7 Blondel père et fils France
8 Bólido Comidas preparadas Spain
9 John Germany
CustomerExclusion
CustomerID exclusion_type Flag
1 BadAddress Y
1 Optout Y
3 NoCall Y
4 BadEmail Y
5 BadAddress Y
6 BadAddress Y
6 Optout Y
9 BadAddress N

Desired output: I want records from Contacts table AFTER excluding records with exclusion_type = 'BadAddress' or 'OptOutOnly' (any one OR BOTH) and flag = 'Y' for each exclusion_type

Questions: So far, I have tried 3 different approach (1 & 2 are giving me desired output while 3 is not).

  • Which one is the correct approach?
  • What is the difference in each approach?
  • In terms of optimization (better speed), which one is better approach?

Approach 1:

select c.* from Contacts c
WHERE Country IS NOT NULL
AND NOT EXISTS
	(
		SELECT 1
		FROM CustomerExclusion e
		WHERE e.CustomerID = c.CustomerID
			AND e.exclusion_type IN ('BadAddress','OptOutOnly') and e.flag = 'Y'
	)

Approach 2:

select c.* from Contacts c
WHERE Country IS NOT NULL
AND NOT EXISTS
	(
		SELECT CustomerID
		FROM CustomerExclusion e
		WHERE e.CustomerID = c.CustomerID
		AND e.exclusion_type IN ('BadAddress','OptOutOnly') and e.flag = 'Y'
	)

Approach 3:

select c.* from Contacts c
WHERE Country IS NOT NULL
AND NOT EXISTS
	(
		SELECT CustomerID
		FROM CustomerExclusion e
		WHERE e.exclusion_type IN ('BadAddress','OptOutOnly') and e.flag = 'Y'
	)
2

Hi

Look at the execution plan

Also put test data
Small medium Large

Mostly depends on your use case scenario

3

hi

approach 4 which may help in performance

SELECT 
  c.*
FROM 
  ( select * from Contacts  where Country is not null  ) c   
     LEFT JOIN 
  ( select * from CustomerExclusion where exclusion_type in ('BadAddress','OptOutOnly') and flag = 'Y') d 
      ON c.CustomerID = d.CustomerID
WHERE 
   d.CustomerID IS NULL