SOQL Queries in Apex

Salesforce developers rely heavily on SOQL queries in Apex to interact with database. Whether you are a complete newbie or seasoned coder, mastering SOQL is crucial for building efficient and scalable applications.

Syntax of SOQL query in Apex

nameOfSObject variableName = [SOQL_Query];
/*OR*/
List<nameOfSObject> variableName = [SOQL_QUERY];
/*OR*/
Map<Id, nameOfSObject> variableName = new Map<Id, nameOfSObject>([SOQL_QUERY]);

Though, salesforce does not restrict you to use a sObject variable to collect returned records from SOQL but, it is advisable to use collection variables or collection of sObject variables to store returned records from SOQL.

Retrieve field values from SOQL query in Apex

Use dot (.) operator to retrieve field values.

Syntax

variableName.fieldName

Example

List<Opportunity> oppList = [SELECT Id, Name, StageName 
                             FROM Opportunity];
//Access the first element of oppList 
System.debug('1st element '+oppList[0].Name+' '+oppList[0].StageName);
//for loop to iterate over all values present in oppList and print Name as well as Stage
for(Opportunity opp : oppList){
    System.debug('Name: '+opp.Name+' '+'Stage: '+opp.StageName);
}

Binding Variables in SOQL

You can use Apex variables in SOQL using a colon (:). These variables are called bind variables. You can use binding variable after WHERE clause and in LIMIT and OFFSET clause.

String Stage = 'Closed Won';
//Query all opportunities with closed won stage.
List<Opportunity> oppList = [SELECT Id, Name, Amount 
                             FROM Opportunity
                             WHERE StageName =: Stage];
System.debug('oppList size '+oppList.size());
for(Opportunity opp : oppList){
    System.debug('Name '+opp.Name+' '+'Amount '+opp.Amount);
}

Handling Aggregate Queries in Apex

AggregateResult object is used to store the results of aggregate queries. AggregateResult is a read-only object. Please checkout SOQL functions to learn about aggregate queries.

The get() method uses alias, fieldname or expri when no alias is provided. i in expri represents order of aggregated fields without alias.

Syntax

List<AggregateResult> variableName = [Aggregate_SOQL_query];
variableName[0].get('fieldName/alias/expri');

Example

String Stage = 'Closed Won';
List<AggregateResult> agrList = [SELECT MIN(Amount) minAmount, MAX(Amount) 
                                 FROM Opportunity
                                 WHERE StageName =: Stage];
System.debug('agrList size '+agrList.size());
for(AggregateResult agr : agrList){
    System.debug(agr.get('minAmount')+' '+agr.get('expr0'));
}

SOQL For Loop

SOQL for loop can process one record at a time using single sObject, or in chunk of 200 using sObject list. It uses efficient chunking with call to query or querymore methods of SOAP API. It is used to limit heap size.

Syntax

for(sObjectName variableName : [SOQL_Query]){
    //logic here
}
/*OR*/
for(List<sObjectName> variableName : [SOQL_Query]){
   //logic here
}

sObject list format is optimal choice if you must use DML statement within for loop because, this format executes code within for loop once for 200 sObject records

Example of SOQL for loop

String Stage = 'Closed Won';
for(Opportunity opp : [SELECT Id, Name 
                       FROM Opportunity 
                       WHERE StageName =: Stage]){
                           System.debug('Opp>>> '+Opp);
                       }
System.debug('CPU time in milliseconds >>> '+Limits.getCpuTime());
System.debug('heap size in bytes>>> '+Limits.getHeapSize());

Output

Example of List Assignment for loop

String Stage = 'Closed Won';
List<Opportunity> oppList = [SELECT Id, Name 
                       	     FROM Opportunity 
                       	     WHERE StageName =: Stage];
for(Opportunity opp : oppList){
   System.debug('Opp>>> '+Opp);
}
System.debug('CPU time in milliseconds >>> '+Limits.getCpuTime());
System.debug('heap size in bytes>>> '+Limits.getHeapSize());

Output

Between SOQL for loop and List Assignment for loop which one should we choose? after going through the output of both approaches, we can conclude that SOQL for loop is suitable when heap size is priority and List assignment for loop keep CPU cycles on priority.

Best practices for using SOQL in Apex

Use Collection Variables to store results of SOQL query

This approach will help you to avoid following exceptions List has no rows for assignment to sObject and List has more than one row for assignment to sObject exception. This exception occurs when you use single sObject variable to store result of SOQL query.

Avoid SOQL queries inside Loops

Writing SOQL inside loop will exhaust governor limit of 100 SOQL queries in a transcation.

Use Selective Filters

Use Indexed fields such as Id , Name , CreatedDate etc in WHERE clause of SOQL query. This will help us to improve performance and avoid unexpected runtime exceptions.

Query only required fields

It reduces the amount of data retrieved, which improves efficiency and avoid heap limit exceptions.

Use SOQL for Loops for large data volumes

It will process query results in batches under the hood, reducing memory usage and avoiding heap exceptions.

Whether you’re writing simple queries or complex logics, mastering SOQL queries in apex is essential for salesforce development. Follow best practices, handle exceptions gracefully, and optimize performance using SOQL for loops.