SQLTeam.com | Weblogs | Forums

Need some help in understanding the exclusion logic in a query

I am working with the following query:

SELECT A.ACTIVITY_NAME 
       ,PS.CUSTOMER_FLAG       
       ,C.CUSTOMER_EMAIL
FROM   TABLE_CUSTOMER_ACTIVITY A 
       INNER JOIN TABLE_CUSTOMER_PROFILE PS 
               ON A.CONTACT_ID = PS.CONTACT_ID 
       INNER JOIN TABLE_CUSTOMER C
               ON A.CONTACT_ID = C.CONTACT_ID 
WHERE PS.CUSTOMER_GROUP='1'
AND NOT EXISTS (SELECT * 
                       FROM   TABLE_CUSTOMER_ARCHIVE B
                       WHERE  PS.CONTACT_ID  = B.CONTACT_ID )

Can anyone explain what is happening in this part of the query?

AND NOT EXISTS (SELECT * 
                       FROM   TABLE_CUSTOMER_ARCHIVE B
                       WHERE  PS.CONTACT_ID  = B.CONTACT_ID )

I understand that it is doing an exclusion meaning, selecting all contacts based on the criteria and who do not exist in the TABLE_CUSTOMER_ARCHIVE but PS.CONTACT_ID = B.CONTACT_ID means inner join of TABLE_CUSTOMER_ARCHIVE and TABLE_CUSTOMER_PROFILE? meaning contacts who exists in TABLE_CUSTOMER_ARCHIVE as well as TABLE_CUSTOMER_PROFILE?

meaning contacts who exists in TABLE_CUSTOMER_ARCHIVE as well as TABLE_CUSTOMER_PROFILE?

No. Customers who exist in TABLE_CUSTOMER_PROFILE but do not exist in TABLE_CUSTOMER_ARCHIVE.

Hi @ScottPletcher

I see what you are saying about Customers who exist in TABLE_CUSTOMER_PROFILE but do not exist in TABLE_CUSTOMER_ARCHIVE.

Then in the following last 3 lines:

AND NOT EXISTS (SELECT * 
                       FROM   TABLE_CUSTOMER_ARCHIVE B
                       WHERE  PS.CONTACT_ID  = B.CONTACT_ID )

What this means?
WHERE PS.CONTACT_ID = B.CONTACT_ID

To my understanding, this looks redundant unless i am miunderstanding?

That is just the comparison to determine if a row exists for not. It could also have been A.CONTACT_ID = B.CONTACT_ID, since A.CONTACT_ID and PS.CONTACT_ID are known to be the same.

In another context, it could be a hard-coded comparison, like this:
NOT EXISTS(SELECT 1 FROM TABLE_CUSTOMER_ARCHIVE B WHERE B.CONTACT_ID = 5)

Or to a variable:
NOT EXISTS(SELECT 1 FROM TABLE_CUSTOMER_ARCHIVE B WHERE B.CONTACT_ID = @CONTACT_ID)

Hi @ScottPletcher Thank you for your response.

I think, I am still not following.

I personally would have done like this:

AND NOT EXISTS (SELECT * 
                       FROM   TABLE_CUSTOMER_ARCHIVE)

Is my approach different or wrong? Am I missing anything here?

Not at all. All perfect.