Salesforce SOQL Basics

Unlike other SQL languages, you can’t specify * for all fields. You must specify every field you want to get explicitly. If you try to access a field you haven’t specified in the SELECT clause, you’ll get an error because the field hasn’t been retrieved.

You don’t need to specify the Id field in the query as it is always returned in Apex queries, whether it is specified in the query or not. For example: SELECT Id,Phone FROM Account and SELECT Phone FROM Account are equivalent statements. The only time you may want to specify the Id field if it is the only field you’re retrieving because you have to list at least one field: SELECT Id FROM Account. You may want to specify the Id field also when running a query in the Query Editor as the ID field won’t be displayed unless specified.


Instead of using the equal operator (=) for comparison, you can perform fuzzy matches by using the LIKEoperator. For example, you can retrieve all accounts whose names start with SFDC by using this condition:WHERE Name LIKE 'SFDC%'. The % wildcard character matches any or no character. The _ character in contrast can be used to match just one character.


Account[] acctsWithContacts = [SELECT Name, (SELECT FirstName,LastName FROM Contacts)
                               FROM Account 
                               WHERE Name = 'SFDC Computing'];
// Get child records
Contact[] cts = acctsWithContacts[0].Contacts;
System.debug('Name of first associated contact: ' 

             + cts[0].FirstName + ', ' + cts[0].LastName);



Contact[] cts = [SELECT Account.Name FROM Contact 
                 WHERE FirstName = 'Carol' AND LastName='Ruiz'];
Contact carol = cts[0];
String acctName = carol.Account.Name;

System.debug('Carol\'s account name is ' + acctName);

The examples in this section are based on standard objects. Custom objects can also be linked together by using custom relationships. Custom relationship names end with the __r suffix. For example, invoice statements are linked to line items through the Line_Items__r relationship on the Invoice_Statement__c custom object.

Querying Record in Batches By Using SOQL For Loops

With a SOQL for loop, you can include a SOQL query within a for loop. The results of a SOQL query can be iterated over within the loop. SOQL for loops use a different method for retrieving records—records are retrieved using efficient chunking with calls to the query and queryMore methods of the SOAP API. By using SOQL for loops, you can avoid hitting the heap size limit.

insert new Account[]{new Account(Name = 'for loop 1'), 
                     new Account(Name = 'for loop 2'), 
                     new Account(Name = 'for loop 3')};

// The sObject list format executes the for loop once per returned batch
// of records
Integer i=0;
Integer j=0;
for (Account[] tmp : [SELECT Id FROM Account WHERE Name LIKE 'for loop _']) {
    j = tmp.size();
    i++;
}
System.assertEquals(3, j); // The list should have contained the three accounts
                       // named 'yyy'
System.assertEquals(1, i); // Since a single batch can hold up to 200 records and,
                       // only three records should have been returned, the 

                       // loop should have executed only once