SOQL For Loops

SOQL for loops iterate over all of the sObject records returned by a SOQL query.

The syntax of a SOQL for loop is either:

1for (variable : [soql_query]) {
2    code_block
3}

1for (variable_list : [soql_query]) {
2    code_block
3}

Both variable and variable_list must be of the same type as the sObjects that are returned by the soql_query. As in standard SOQL queries, the [soql_query] statement can refer to code expressions in their WHERE clauses using the : syntax. For example:

1String s = 'Acme';
2for (Account a : [SELECT Id, Name from Account
3                  where Name LIKE :(s+'%')]) {
4    // Your code
5}

The following example combines creating a list from a SOQL query, with the DML update method.

1// Create a list of account records from a SOQL query
2List<Account> accs = [SELECT Id, Name FROM Account WHERE Name = 'Siebel']; 
3
4// Loop through the list and update the Name field
5for(Account a : accs){
6   a.Name = 'Oracle';
7}
8
9// Update the database
10update accs;

SOQL For Loops Versus Standard SOQL Queries

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records, SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of SOAP API. Developers can avoid the limit on heap size by using a SOQL for loop to process query results that return multiple records. However, this approach can result in more CPU cycles being used. See Total heap size.

Queries including an aggregate function don't support queryMore. A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop.

For fine-grained control over the results of a SOQL query, consider using Apex cursors. See Apex Cursors.

SOQL For Loop Formats

SOQL for loops can process records one at a time using a single sObject variable, or in batches of 200 sObjects at a time using an sObject list:

The single sObject format executes the for loop's <code_block> one time per sObject record. Consequently, it’s easy to understand and use, but is grossly inefficient if you want to use data manipulation language (DML) statements within the for loop body. Each DML statement ends up processing only one sObject at a time.
The sObject list format executes the for loop's <code_block> one time per list of 200 sObjects. Consequently, it’s a little more difficult to understand and use, but is the optimal choice if you must use DML statements within the for loop body. Each DML statement can bulk process a list of sObjects at a time.

For example, the following code illustrates the difference between the two types of SOQL query for loops:

1// Create a savepoint because the data should not be committed to the database
2Savepoint sp = Database.setSavepoint(); 
3
4insert new Account[]{new Account(Name = 'yyy'), 
5                     new Account(Name = 'yyy'), 
6                     new Account(Name = 'yyy')};
7
8// The single sObject format executes the for loop once per returned record
9Integer i = 0;
10for (Account tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
11    i++;
12}
13System.assert(i == 3); // Since there were three accounts named 'yyy' in the
14                       // database, the loop executed three times
15
16// The sObject list format executes the for loop once per returned batch
17// of records
18i = 0;
19Integer j;
20for (Account[] tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
21    j = tmp.size();
22    i++;
23}
24System.assert(j == 3); // The lt should have contained the three accounts
25                       // named 'yyy'
26System.assert(i == 1); // Since a single batch can hold up to 200 records and,
27                       // only three records should have been returned, the 
28                       // loop should have executed only once
29
30// Revert the database to the original state
31Database.rollback(sp);

The break and continue keywords can be used in both types of inline query for loop formats. When using the sObject list format, continue skips to the next list of sObjects.
DML statements can only process up to 10,000 records at a time, and sObject list for loops process records in batches of 200. Consequently, if you’re inserting, updating, or deleting more than one record per returned record in an sObject list for loop, it’s possible to encounter runtime limit’s errors. See Execution Governors and Limits.
You may 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.
1for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) 2 FROM Account WHERE Id IN ('<ID value>')]) { 3 List<Contact> contactList = acct.Contacts; // Causes an error 4 Integer count = acct.Contacts.size(); // Causes an error 5 // Note: If JSON.serialize() is used here on acct, the resulting JSON won't have the complete set of Contacts 6}

To avoid getting this exception, use a for loop to iterate over the child records, as follows.

1for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
2                    FROM Account WHERE Id IN ('<ID value>')]) { 
3    Integer count=0;
4    for (Contact c : acct.Contacts) {
5        count++;
6    }
7}

In this example, if JSON.serialize() is used on acct, only the records that have been retrieved so far will be returned and serialized. Because the Apex SOQL for-loop mechanism is designed to minimize the amount of heap usage by keeping only a subset of the record data in memory, the complete sObject and any subquery sObjects will not be available to obtain complete serialization.

Note

Apex Developer Guide

SOQL For Loops

SOQL For Loops Versus Standard SOQL Queries

SOQL For Loop Formats