SOQL Where ID in Clause Limitation

Question

I am dealing with some huge data and while querying I need to by pass many of them. So I am keeping ID in one List and passing them in the SOQL. Example:

List<Id> idlist = new List<Id>();
[Select Id, Name from object1__c where id not in : idList]

idList will have huge set of IDs which I dont want to query.

So i was wondering is there any limit for number of Id which we can use in where clause ? I mean how big my idList can have values? please suggest. I have not received any errors yet but I am being more cautious before putting this code in action.

Answer 1

Using inline binding circumvents the normal character restrictions of an SOQL statement; as long as you don't exceed the heap size limit, you can include as many values as you like in the list. Here's an execute anonymous script that will query for 161,448 (non-existent) unique Id values:

id[] idlist = new id[0];
String[] keys = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'.split('');
for(integer a = 0; a < 42; a++) {
    for(integer b = 0; b < 62; b++) {
        for(integer c = 0; c < 62; c++) {
            idlist.add('001000000000'+keys[a]+keys[b]+keys[c]);
        }
    }
}
// Following query will run fine with 161k Id values
Account[] accounts1 = [select id from account where id = :idlist];
// As will this one
Account[] accounts2 = database.query('select id from account where id = :idlist');

This code essentially generates a query that would require 3,500,000 SOQL characters (minimum), yet doesn't run into any particular limits.

Eventually, depending on what you're doing, you'll run into either heap limits, maximum payload size limits, etc.

When practical, sort your results by Id or CreatedDate, and use that as a simple filter instead. You can read more about doing that in this answer.

Answer 2

Here you have 2 important limits:

1. The maximum characters allowed in a SOQL query is 20K.
2. The maximum characters allowed in the where clause is 4K.

You can see the reference here: https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm

Answer 3

Actually there is no particular limit on the List size it depends on the heap size allocation for synchronous traction you can have upto 6MB where as for asynchronous it is 12MB. So if the heap size limit exceeds then you might have issues otherwise it will work.

But make sure if that list is used for DML operations then not more 10K records should be there because it might throw the governor limit issue for DML operation.

Answer 4

That is great information @sfdcfox. I took that information and discovered a limitation in the SOAP API.

If anyone is executing this query through SOAP API, the query method has a 20,000 character limit on the query that you can execute. This just means that you'll need to split up your list before you query and then add each result to your final list of objects.