33

Oracle has a limit of 1000 items for its IN clause. Is there a similar limit in SOQL? The SF documentation on the IN clause does not mention any limit. To clarify the definition of an item, the following example has 2 items in its IN clause: 

SELECT Name FROM Widget__c
WHERE Name IN ('Ring', 'Flange')
  • the above code was taken from the linked SF documentation.
Improve this question

5 Answers 5

Reset to default
29

I think you are limited to the size of the query not exceeding 20,000 characters.

https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm

SOQL statements can't exceed 20,000 characters. For SOQL statements that exceed this maximum length, the API returns a MALFORMED_QUERY exception code ; no result rows are returned.

Maybe someone can weigh in on whether this applies to bound lists - if just comma-delimiting a bunch of IDs, one won't get more than about 1000 in there.

Edit: @sfdcfox has run an experiment and concluded bind variables do not contribute to query length.

Edit: It seems that when using bind variables, the number of items in an IN clause is determined by the amount of available heap space.

Improve this answer
7
  • 4
    It's 20,000 characters now.
    – sfdcfox ♦
    Commented Jan 31, 2014 at 16:35
  • 8
    If you bind to a list, you can easily get around the 20,000 character limit. For example, I created 6,000 assets (total number of characters for 6,000 18-character ID values would be 125999 characters, including commas and quotes), and then specifically tried to query them like this: map<id, asset> assets = new map<id, asset>([select id from asset limit 10000]); asset[] r = [select id, name from asset where id in :assets.keyset()]; -- this works, even though if you tried to make it a string, it would fail.
    – sfdcfox ♦
    Commented Jan 31, 2014 at 16:44
  • Since bound variables do not contribute to query length, the maximum number of items in a SOQL IN clause becomes limited by either the maximum number of items in a collection, or by available system memory. Is that correct?
    – edgartheunready
    Commented Jan 31, 2014 at 17:23
  • 2
    I was able to run a query with an in clause with a bound list of 1.5 million ids (duplicated a list of 1000 unique ids). Adding more that that caused heap space errors. So it looks like when using a bound list, the limit becomes heap space. (here is the code I used to come to this conclusion. I have about 1.5k task objects in the org i ran this code in. pastebin.com/9TEnc9Nz)
    – edgartheunready
    Commented Jan 31, 2014 at 19:49
  • 1
    as of v58, max len is 100,000
    – cropredy
    Commented Jun 28, 2023 at 23:49
11

I don't think the accepted answer is correct. While the TOTAL length of a SOQL query must be less than 20,000 characters, the WHERE clause can only be 4,000 characters. And the IN clause is part of that 4,000 characters.

Therefore, the actual maximum length of the IN clause is less than 4,000 characters (because the field name and the string ' IN ' contribute to the 4,000 characters.

https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm

Edit: The maximum length has been extended to 100,000 characters. But the IN clause is still less than that because the list of fields takes up some of those 100,000 characters.

As for the number of items allowed in the IN clause collection, that is determined by the total character count as well.

Improve this answer
4

The maximum length of SOQL statements is now 100 000 characters: https://help.salesforce.com/articleView?id=000333028&type=1&mode=1

Improve this answer
2

I tried using a list bind variable (a Map keyset) and as the size of the list grew to over 3300 entries eventually I hit the SF CPU time limit at around 2m 6s. Strangely, the logs showed that the query took a couple of seconds - the timestamp on the "SOQL_EXECUTE_END" log entry was only 2 seconds after the query start time - but the next log entry was 2m 6s later. The CPU limit was exceeded only .5s after that. I don't know what this means (yet) but this is a limit you can hit even if the character limit isn't a problem.

Improve this answer
-2

Much has changed since this question was first asked. Check the current SOQL limits here: https://developer.salesforce.com/docs/atlas.en-us.232.0.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm

Improve this answer
1
  • 1
    Link only answers are frowned upon, and the content behind the link does not answer the question.
    – Adrian Larson ♦
    Commented Dec 10, 2021 at 0:00

You must log in to answer this question.

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