SOQL: Salesforce Object Query Language – In Absolute Detail

SOQL (Salesforce Object Query Language) is a powerful language specifically designed to query data stored in the Salesforce database. It’s syntactically similar to standard SQL (Structured Query Language) but is tailored for the unique architecture and data model of Salesforce. Understanding SOQL is fundamental for any developer working with the Salesforce platform, as it’s the primary way to retrieve and manipulate data programmatically.

Key Characteristics of SOQL

• Targeted Queries: SOQL allows you to query data from a single Salesforce object at a time (including standard and custom objects).
• Structured Retrieval: You specify the fields you want to retrieve, apply filtering conditions, sort results, and even traverse relationships between objects.
• Integration with Apex: SOQL queries are embedded directly within Apex code using square brackets [].
• Governor Limits Awareness: SOQL queries are subject to Salesforce governor limits (e.g., the number of queries in a transaction, the number of rows returned) to ensure efficient resource utilization in the multi-tenant environment.
• Relationship Traversal: SOQL provides a powerful way to query related records through parent-child and child-parent relationships using dot notation.

SOQL Query Structure

A basic SOQL query follows this general structure:

SELECT fieldList FROM objectName [WHERE conditionExpression] [ORDER BY fieldName [ASC|DESC] [NULLS FIRST|LAST]] [LIMIT integer] [OFFSET integer] [FOR UPDATE]

Let’s break down each clause in detail:

1. SELECT fieldList

This clause specifies the fields you want to retrieve from the specified object. You can select:

• Specific Fields: A comma-separated list of field API names (e.g., Name, Email, AccountNumber__c).
• Relationship Fields: To access fields on related objects using dot notation (e.g., Account.Name to get the name of the related Account).
  • Parent-to-Child: Use the child relationship name (e.g., Account.Contacts.FirstName to get the first names of all related Contacts). Child relationships often have a plural name.
  • Child-to-Parent: Use the parent object’s API name followed by a dot and the field name (e.g., Contact.Account.Name to get the name of the parent Account).
• Aggregate Functions: To perform calculations on a set of records (e.g., COUNT(), SUM(Amount), AVG(Rating), MIN(CreatedDate), MAX(LastModifiedDate)). When using aggregate functions, you often need a GROUP BY clause.
• COUNT(): Returns the total number of records matching the criteria.

  SELECT COUNT() FROM Account WHERE Industry = 'Technology'

• SUM(fieldName): Returns the sum of the values in the specified numeric field.

  SELECT SUM(Amount) FROM Opportunity WHERE StageName = 'Closed Won'

• AVG(fieldName): Returns the average of the values in the specified numeric field.

  SELECT AVG(Rating) FROM Lead WHERE LeadSource = 'Web'

• MIN(fieldName): Returns the minimum value of the specified field.

  SELECT MIN(CreatedDate) FROM Case WHERE Status = 'New'

• MAX(fieldName): Returns the maximum value of the specified field.

  SELECT MAX(LastModifiedDate) FROM Account

• Typeof: Used for polymorphic relationships (e.g., Task.Who) to select fields based on the actual type of the related object.
• FIELDS(ALL): Selects all accessible fields for the object. Use with caution due to performance implications and potential for including fields you don’t need.
• FIELDS(STANDARD): Selects all standard fields for the object.
• FIELDS(CUSTOM): Selects all custom fields for the object.

2. FROM objectName

This clause specifies the Salesforce object you want to query (e.g., Account, Contact, MyCustomObject__c). You can only specify one primary object in the FROM clause of a standard SOQL query.

3. WHERE conditionExpression (Optional)

This clause filters the records based on specified conditions. You can use:

• Comparison Operators: =, !=, >, <, >=, <=.

  SELECT Id, Name FROM Account WHERE Industry = 'Technology'

  SELECT Id, Email FROM Contact WHERE CreatedDate > LAST_N_DAYS:30

• Logical Operators: AND, OR, NOT.

  SELECT Id, Name FROM Account WHERE Industry = 'Technology' AND AnnualRevenue > 1000000

  SELECT Id, Name FROM Account WHERE Industry = 'Technology' OR BillingCountry = 'USA'

• IN and NOT IN: To check if a field’s value is (or is not) in a list of values.

  SELECT Id, Name FROM Account WHERE Industry IN ('Technology', 'Finance')

  SELECT Id, Name FROM Account WHERE BillingCountry NOT IN ('USA', 'Canada')

• LIKE: To perform pattern matching using wildcards (% for zero or more characters, _ for exactly one character).

  SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'

  SELECT Id, ProductCode__c FROM Product__c WHERE ProductCode__c LIKE 'PROD_0?'

• NULL and NOT NULL: To check if a field has a null value.

  SELECT Id, Description FROM Account WHERE Description = NULL

  SELECT Id, Description FROM Account WHERE Description != NULL

• Date Functions: Various functions to compare date and datetime fields (e.g., TODAY, YESTERDAY, LAST_N_DAYS:n, THIS_WEEK).

  SELECT Id, Name FROM Opportunity WHERE CloseDate = TODAY

  SELECT Id, Name FROM Lead WHERE CreatedDate < LAST_YEAR

• Subqueries: To filter results based on the results of another SOQL query.

  SELECT Id, Name FROM Contact WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = 'Technology')

• Relationship Conditions: To filter based on fields of related objects.

  SELECT Id, Name FROM Account WHERE EXISTS (SELECT Id FROM Contacts WHERE AccountId = Account.Id AND FirstName LIKE 'John%')

4. ORDER BY fieldName [ASC|DESC] [NULLS FIRST|LAST] (Optional)

This clause sorts the query results based on the specified field. You can specify:

• ASC: Ascending order (default).
• DESC: Descending order.
• NULLS FIRST: Null values appear before non-null values.
• NULLS LAST: Null values appear after non-null values (default).

SELECT Id, Name FROM Account ORDER BY Name ASC

SELECT Id, CloseDate FROM Opportunity ORDER BY CloseDate DESC NULLS LAST

5. LIMIT integer (Optional)

This clause restricts the number of records returned by the query.

SELECT Id, Name FROM Account LIMIT 10

6. OFFSET integer (Optional)

This clause specifies the starting row offset into the result set. It’s often used for pagination.

SELECT Id, Name FROM Account ORDER BY Name LIMIT 10 OFFSET 20 // Returns records 21-30

7. FOR UPDATE (Optional)

This clause is used for record locking. It locks the records returned by the query for the duration of the transaction, preventing other processes from making changes until the transaction is committed. Use with caution as it can lead to performance issues if not managed properly.

List<Account> accountsToUpdate = [SELECT Id, Name FROM Account WHERE Name LIKE 'Temp%' LIMIT 5 FOR UPDATE];
// Perform updates on these accounts
update accountsToUpdate;

8. GROUP BY fieldNameList [HAVING condition] (Optional)

This clause groups the result set based on the values of one or more specified fields, often used in conjunction with aggregate functions. The optional HAVING clause filters the grouped results based on a condition.

SELECT Industry, COUNT() FROM Account GROUP BY Industry

SELECT BillingCountry, AVG(AnnualRevenue) FROM Account GROUP BY BillingCountry HAVING AVG(AnnualRevenue) > 500000

9. WITH SECURITY_ENFORCED (Optional)

This clause enforces object and field-level security permissions of the running user on the query results. It’s crucial for ensuring data privacy and security.

List<Account> securedAccounts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology' WITH SECURITY_ENFORCED];

Using SOQL in Apex

As mentioned earlier, SOQL queries are embedded directly within Apex code using square brackets []. The result of a SOQL query is typically a list of sObject records.

List<Account> accounts = [SELECT Id, Name, Industry FROM Account WHERE BillingCountry = 'USA' ORDER BY Name LIMIT 50];

if (!accounts.isEmpty()) {
    for (Account acc : accounts) {
        System.debug('Account Name: ' + acc.Name + ', Industry: ' + acc.Industry);
    }
} else {
    System.debug('No accounts found in the USA.');
}

// Querying related Contacts
Account myAccount = [SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account WHERE Name = 'Acme Corp' LIMIT 1];
if (myAccount != null && !myAccount.Contacts.isEmpty()) {
    for (Contact con : myAccount.Contacts) {
        System.debug('Contact Name: ' + con.FirstName + ' ' + con.LastName);
    }
}

Governor Limits Related to SOQL

It’s crucial to be aware of SOQL governor limits to avoid runtime exceptions:

• Total number of SOQL queries issued in a transaction: Typically 100 for synchronous Apex and 200 for asynchronous Apex.
• Total number of records retrieved by SOQL queries: Typically 50,000.
• Maximum number of rows for a single SOQL query: 50,000.
• And others related to query complexity, subqueries, etc.

Always design your SOQL queries to be efficient and avoid exceeding these limits, especially in bulk operations and triggers.

Conclusion

SOQL is a fundamental language for interacting with Salesforce data. Its structured syntax, powerful filtering and sorting capabilities, and ability to traverse relationships make it an essential tool for developers building applications on the Salesforce platform. By mastering the various clauses and considerations, you can effectively retrieve and manipulate the data your applications need while adhering to Salesforce’s governor limits and security best practices.