2

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?

Improve this question
4
  • 1
    whatever its is ...(IN or Not IN) 10,000 rows only allowed. Otherwise you will end up with too many query rows
    – bharath
    Commented May 22, 2017 at 7:51
  • Check this out salesforce.stackexchange.com/questions/105609/…
    – bharath
    Commented May 22, 2017 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.
    – noBillSide
    Commented May 22, 2017 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 ♦
    Commented May 23, 2017 at 12:25

1 Answer 1

Reset to default
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
2
  • 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?
    – noBillSide
    Commented Jun 2, 2017 at 11:19
  • 1
    @Detached Yes, that's certainly under 4.2M.
    – Adrian Larson ♦
    Commented Jun 2, 2017 at 11:42

You must log in to answer this question.

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