what is the Limit for subquery in SOQL?

Question

List<Account> acc=[select Name , (Select LastName from contacts) from Account];

How many maximum contacts can be reterived here?

Answer 1

There is no such limit for inner query. 50000 records per transaction will be applied here.

For example your org contain only 2 Account and each have 10 Contacts.

If you execute this with sub Query like below

Select Id, Name, (Select Id, Name from Contacts) From Account

Then the total number of Query rows will be 22 (2+10+10).

Answer 2

So my experience is that it varies on the number of fields retrieved in the subquery and what you are doing with the retrieved data.

Short answer: 200 Contacts for the code below:

for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts)
                    FROM Account WHERE Id IN ('<ID value>')]) {
    if (acct.Contacts.size()>0){
       doSomethingWithContacts(acct.Contacts);
    }

}

There's a somewhat cryptic comment in the SFDC documentation here: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm

"You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set. For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception."

I've tested this a bunch and have found that the actual limit varies by the number of fields retrieved in the subquery. I have moved away from subqueries quite a bit after running into this exception and have done a second query and build a map to get the same results. 200 records in a database doesn't seem line "a large set" to me. Doing a for loop on the inner list does help but it never let me get to something I would be more likely to rate "a large set of child records".

Answer 3

Total number of records retrieved by SOQL queries:50,000

Total number of records retrieved by Database.getQueryLocator:10,000

In a SOQL query with parent-child relationship subqueries, each parent-child relationship counts as an extra query. These types of queries have a limit of three times the number for top-level queries. The row counts from these relationship queries contribute to the row counts of the overall code execution.

Answer 4

As such there is no specific limit for the number inner query records in SOQL.

However, there is one catch to the 200 sub query limit that that it causes an error while assignment directly from acc[i].contacts to any contacts list.

Although that can be handled using a for loop for assignment .