0

I have a query in Marketing Cloud which searches for records in my Contact_Salesforce DE. I have a condition where it excludes records if the values are within a certain range, what I've noticed is it will exclude those records but also exclude any records with NULL values even though it's not been explicitly added to the WHERE condition.

Here is what my query looks like

SELECT c.ID, c.Email, c.FirstName as FirstName, c.LastName as LastName
from Contact_Salesforce c
JOIN Account_Salesforce a ON a.Id = c.AccountId
WHERE ((a.Type <> 'Intern'
AND a.Type <> 'Contractor'
AND a.Type <> 'School Intern'
AND a.Type <> 'Internal User')
OR a.Type IS NULL)

You can see the only way I've found around this is by creating a rule set and explicitly looking for records where Type IS NULL.

Can anyone explain why the query doesn't include null values if I don't add a.Type IS NULL?

3

1 Answer 1

5

Makes sense, because null is an absence of a value.

I'd write it like this (assuming the missing part of your query isn't material to your question):

SELECT 
  c.ID
, c.Email
, c.FirstName
, c.LastName
from Contact_Salesforce c
INNER JOIN Account_Salesforce a ON a.Id = c.AccountId
where 
a.Type is null 
or a.Type not in ('Intern', 'Contractor', 'School Intern', 'Internal User')

I'd explicitly make it an INNER JOIN, since that's the default if you don't specify it.

3
  • 1
    Hi Adam, thanks for the tips... Is this the expected behavior? I would have assumed NULL would still be queried if its not included in the a.Type not in line. As we're effectively saying give me back all records dont DONT it this criteria.. NULL should still be considered. Oct 26, 2020 at 14:36
  • 1
    Yeah, it's tricky. I've been burned by this a few times and have tried to get in the habit of specifying null conditions separately. Oct 26, 2020 at 14:59
  • 3
    @deploymentfailure I wrote an article that goes into it if you want more info gortonington.com/… Oct 26, 2020 at 16:16

You must log in to answer this question.

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