0

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!

3
  • Does below produce any result? select a.[Account:PersonContact:Id] from [Salesforce_Data_Event_DE] as a join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id] Jul 17 '20 at 12:26
  • Inside of you IN/NOT IN you are also joining (default is inner join) on Account_Salesforce_3 so it is possible there are records that match between Salesforce_Data_Event_DE and SurveyTaker__c_Salesforce but they do not exist on Account_Salesforce_3 - which would cause 0 results returned. Have you checked to see results returned if you remove the In/Not In statement? Jul 17 '20 at 13:58
  • @MateuszBartkowiak yes, it returns 1.4k rows @Gortonington every record in Salesforce_Data_Event_DE exists on Account_Salesforce_3 since it is a subset of the Account. I've checked to see results returned if I remove the IN/NOT IN statement and the query returns 36k rows. With both the IN and the NOT IN statements it returns 0 rows and it has no sense at all Jul 17 '20 at 14:24
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.