Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.
share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select.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.

share|improve this answer
2  
It's 20,000 characters now. –  sfdcfox Jan 31 at 16:35
5  
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 Jan 31 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 Jan 31 at 17:23
1  
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 Jan 31 at 19:49
    
The above pastebin file has a bug, and i couldn't edit the comment. Here is the bug free code: pastebin.com/T7H7MKNU –  edgartheunready Feb 3 at 14:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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