1

What is the maximum number of rows, which can be queried inside a NOT IN clause? For example, let's say I have this query:

SELECT Id FROM Campaign WHERE Id NOT IN (
    SELECT xxx__campaign__c FROM xxx__data__c
)

Would the above query fail if an org has ten million records in the xxx__data__c object?

  • 1
    whatever its is ...(IN or Not IN) 10,000 rows only allowed. Otherwise you will end up with too many query rows – bharath May 22 '17 at 7:51
  • Check this out salesforce.stackexchange.com/questions/105609/… – bharath May 22 '17 at 7:56
  • Thanks, @bharath but there is nothing saying about the 10.000 row limit in a sub-query. I also tested it with more than 50.000 objects without any error. – Detached May 22 '17 at 8:51
  • 1
    @bharath Absolutely incorrect. Rows joined using Left Inner Join or Left Anti Join do not count against your query row limit. – Adrian Larson May 23 '17 at 12:25
3

A Left Anti Join will definitely work with over one million records. In fact, I just ran a query with a Left Anti Join on ~4.2 million records and it executed successfully, though it did take a noticeable amount of time to run.

One problem you might face is query selectivity. If you investigate the Query Plan for such queries, you will find that they cause a Table Scan. So while it seems unlikely you will hit an upper bound on the number of rows included in the join, querying against a large table (>100k) in your top-level query might throw non-selective query errors for inner or anti joins.

One more consideration is that while your query processing time doesn't count against the CPU limit, you might increase the chance of timeouts if you connect this sort of query to a webservice or REST endpoint.

| improve this answer | |
  • Thanks @adrian-larson. If I understand you correct (let's use my example) if there are only a few campaigns (around 1000) the query should be save? – Detached Jun 2 '17 at 11:19
  • 1
    @Detached Yes, that's certainly under 4.2M. – Adrian Larson Jun 2 '17 at 11:42

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.