Update a field as 'TRUE' if exist in the other data extensions and 'FALSE' otherwise

Question

I have a data extension A with a field "Exist_in_SF". I already have records residing in DE A, and I want to update the records in the same data extension with "TRUE" value in "Exist_in_SF" field if DE A's email address data exists in Lead/Contact objects, and otherwise "FALSE". However, the SQL that I wrote is not updating the "Exist_in_SF" field properly, and I am quite stuck. What should be the modification I should take in my SQL?

Below is my current SQL codes:

SELECT A.[EmailAddress],
       CASE WHEN
           (A.[EmailAddress] = SF.[Email]) THEN 'True'
           ELSE 'False'
           END as [Exists_in_SF]

from [Data Extension A] A

INNER JOIN (SELECT l.[email]
                   FROM   ent.[lead_salesforce] l
                   GROUP  BY l.[email]
                   UNION
                   SELECT c.[email]
                   FROM   ent.[contact_salesforce] c
                   GROUP  BY c.[email]) SF
ON A.[EmailAddress] = SF.[email]

Thanks in advance!

Answer 1

You don't mention how it is not updating the "Exist_in_SF" field properly, but I guess there are records not being updated anything.

This is because you are using INNER JOIN which it will pick up records that have matching email values in both tables while excluding the email records in DE A not existing in the combined one between Contact and Lead. So, these ones will not be updated.

Solution: Try to change from INNER JOIN to LEFT JOIN