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?
Thanks @FaithChurchill for the confirmation.
and thanks @ScottPletcher for your help!