I'm trying to have this query pull all subscribers into a data extension who are not within another data extension. I'm not sure why it's not working.

Select Status, Name, FirstName, LastName, MiddleName, Title, Email, Phone, MobilePhone, Rating, Subscription_Type__c,
Newsletter__c, Marketing_Emails__c, OwnerId, Website, Company, Industry, NumberOfEmployees, LeadSource, Street, City,
PostalCode, State, Country, CreatedById, LastModifiedById, Id 
From Lead_Salesforce 
WHERE LeadSource = 'Sample Request' AND Email NOT IN (
  SELECT Email 
  FROM Contact_Salesforce

  • Is it erroring, or just not excluding? Also, is there a chance that a record could be in there that has multiple Email values attached to it? Like primary keys would allow ID 123 to have 2 different entries in the DE and each one has an email of a different value, meaning it would be negative on one match, but positive on another - allowing the record to enter the final DE? May 8 at 14:22
  • No errors, I'm getting 0 includes. There should be thousands.
    – Beck
    May 8 at 14:35
  • I tried to initially create a filter for this but it would not let me compare two different data extensions.
    – Beck
    May 8 at 14:46

1 Answer 1


Email is ambiguous since you're not aliasing anything. Here's how I'd write it:

, l.Name
, l.FirstName
, l.LastName
, l.MiddleName
, l.Title
, l.Email
, l.Phone
, l.MobilePhone
, l.Rating
, l.Subscription_Type__c
, l.Newsletter__c
, l.Marketing_Emails__c
, l.OwnerId
, l.Website
, l.Company
, l.Industry
, l.NumberOfEmployees
, l.LeadSource
, l.Street
, l.City
, l.PostalCode
, l.State
, l.Country
, l.CreatedById
, l.LastModifiedById
, l.Id
From Lead_Salesforce l
and l.LeadSource = 'Sample Request'
and not exists (

  select top 1 c.*
  from Contact_Salesforce c
  where c.email = l.email 


  • 1
    What he said ^ (once again too quick for me) May 8 at 14:47
  • I get 0 records. There should be about a thousand.
    – Beck
    May 8 at 17:52
  • I take that back. It worked! Thank you! I updated the query but didn't actually RUn it. hahaha
    – Beck
    May 8 at 17:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .