IN/NOT IN functions not working in Marketing Cloud SQL query Activity

Question

I'm running into this strange issue without being able to understand why it is happening. I want to run this simple query:

select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] not in (select s.Account__c from SurveyTaker__c_Salesforce as s)

the result is 0 rows. The problem is that, if I run the exact same query with the IN function:

select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] in (select s.Account__c from SurveyTaker__c_Salesforce as s)

the result is still 0 rows. If I remove the WHERE condition, I can find more or less 36k rows as a result. If I run the nested query separately:

select s.Account__c from SurveyTaker__c_Salesforce as s

the result is more or less 150k rows.

I cannot explain why this is happening and I would like to ask you if there is a possible explanation for this issue since NOT IN function is used in many queries on my Business Unit and if something changed I will have to rework them all.

Of course, if I write the query this way it works fine:

select s.Account__c
from SurveyTaker__c_Salesforce as s
inner join [Salesforce_Data_Event_DE] as a on a.[Account:Id] = s.[Account__c]

and the result is about 1.4k rows.

Do you know if something changed about the NOT IN and IN functions? Or can you notice a mistake I've made on the query?

Thank you, Francesco

---

I will try to explain myself better:

select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]

this query result is 36k rows. Now I want to exclude from these 36k records all those who are inside another DE (SurveyTaker__c_Salesforce), so I run the following query:

select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] not in (select s.Account__c from SurveyTaker__c_Salesforce as s)

the result is now 0 rows. So we can think that everyone in the 36k is in the DE called SurveyTaker__c_Salesforce. At this point, I would expect that, if I run the query with the IN statement the result will be of 36k since everyone is into this DE:

select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] in (select s.Account__c from SurveyTaker__c_Salesforce as s)

also this query gives as a result 0 rows.

Thus the IN/NOT IN statement is not working properly or I've made a mistake I still cannot find!

Answer 1

Debugging tSQL without working of the same dataset is difficult and hence I can only give a guess.

However, based on your description my guess, even if it is unlikely, is that the following return NULL values which could explain what you see:

SELECT s.Account__c 
FROM   SurveyTaker__c_Salesforce AS s

An IN statement will be parsed identically to field=val1 or field=val2. Having a NULL value results in field=null which will not work. A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values.

You can explicitly exclude NULL by using IS NOT NULL as below.

SELECT s.Account__c 
FROM   SurveyTaker__c_Salesforce AS s
WHERE  s.Account__c IS NOT NULL

Or you can try to use NOT EXISTS instead

SELECT A.[Account:PersonContact:Id]
FROM   [Salesforce_Data_Event_DE] A
JOIN   Account_Salesforce_3 AS B ON A.[Account:Id] = B.[Id]
WHERE  NOT EXISTS (
           SELECT S.Account__c 
           FROM SurveyTaker__c_Salesforce S
           WHERE A.[Account:Id] = S.Account__c
       )

If this is working, the reason is because of a NULL return. Here is an article to read about the three-valued-logic and why SQL behaves like this.

In a nutshell:

A WHERE clause must evaluate as true in order to be returned but this is not possible with NOT IN when NULL is present.

'B' NOT IN ('X','Y',NULL) is equivalent to 'B' <> 'X' AND 'B' <> 'Y' AND 'B' <> NULL

Result:

'B' <> 'X' = True
'B' <> 'Y' = True 
'B' <> NULL = Unknown

True AND True AND Unknown evaluates as Unknown and hence you see 0 results.