Salesforce SOQL Full Developer Guide

Salesforce is a robust platform offering many tools and features for businesses to manage customer relationships. One of the most critical tools in Salesforce is SOQL or Salesforce Object Query Language.

What is Salesforce SOQL?

SOQL is a query language developers use to retrieve data from Salesforce databases. It is similar to SQL but with some key differences that make it unique to the Salesforce platform.

With SOQL, you can retrieve data from a single object or multiple objects that are related to one another. It is case-insensitive and can refine records based on a condition. Overall, SOQL is a powerful tool that allows developers to retrieve data from Salesforce databases efficiently.

Salesforce SOQL Syntax

SOQL syntax consists of a SELECT statement followed by optional object fields and clauses such as TYPEOF, WHERE, WITH, GROUP BY, and ORDER BY.

The basic structure of a SOQL query is as follows:

  • SELECT: Specifies the fields to retrieve from the object.

  • FROM: Specifies the object to retrieve data from.

  • WHERE: Specifies the condition that the retrieved records must meet.

  • LIMIT (Optional): Specifies the limit of records retrieved.

  • ORDER BY {Field} (Optional): Specifies the order in which records will be retrieved.

Illustrative image representing Salesforce SOQL

SOQL Syntax Examples

Retrieve the id, name, and industry from all Accounts on the system.

SELECT Id, Name, Industry FROM Account

Retrieve the id, first name and last name, from the Contacts created yesterday ordered by the last name.

SELECT Id, Firstname, Lastname FROM Contact WHERE CreatedDate = YESTERDAY ORDER BY Lastname

Retrieve all the fields from the Opportunities in which the amount is greater than $1000 with a limit of 10 records.

SELECT FIELDS(All) FROM Opportunity WHERE Amount > 1000 LIMIT 10

Salesforce SOQL Governor Limits

SOQL queries are subject to governor limits, which are limits on the amount of resources that a single transaction can consume. Here are some SOQL limits and performance considerations to keep in mind:

SELECT Account.CreatedBy.CreatedBy.CreatedBy.CreatedBy.CreatedBy.CreatedBy.CreatedById FROM Contract
SELECT Account.CreatedBy.CreatedBy.CreatedBy.CreatedBy.CreatedBy.CreatedById FROM Contract

Salesforce SOQL Best Practices

With Best Practices you can significative improve the performance and the optimization for your SOQL on the Apex code.

Remember that there are some Governor Limits that you can not break so follow a best practices is the best way to not hit them.

Here are a few Best Practices that you can use to write an SOQL:

Best Practices in Salesforce SOQL

Selective Queries

Use indexed fields in the WHERE clause to create selective queries that return fewer records. This helps avoid hitting governor limits and improves query performance.

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

Avoid SOQL Queries inside loops

Avoid using SOQL queries inside loops, which can cause governor limit issues.

for (Contact cont : [SELECT Id, Firstname FROM Contact WHERE Firstname LIKE 'A%']) {
    Account acc = [SELECT Id, Name FROM Account WHERE Id =: cont.AccountId];
    if(acc.Name == 'John'){

    }
}
for (Contact cont : [SELECT Id, Firstname, Account.Name FROM Contact WHERE Firstname LIKE 'A%']) {
    if(cont.Account.Name == 'John'){

    }
}

Field lists

Use field lists to retrieve only the necessary fields instead of retrieving all fields.

SELECT Fields(ALL) FROM Account
SELECT Id, Name, Industry, CreatedDate FROM Account

Relationship queries

Use Relationship queries to retrieve data from related objects instead of multiple queries.

SELECT Name, Amount, Account.Name, (SELECT Description FROM OpportunityLineItems) FROM Opportunity

Aggregate functions

Use aggregate functions such as COUNT(), SUM(), and AVG() to perform calculations on data.

SELECT COUNT() FROM Opportunity

Sanitize user input

If using dynamic SOQL, use the escapeSingleQuotes method to sanitize user-supplied input. This will prevent an SOQL injection attack

String userInput = 'O\'Reilly'; // Assume this is input from a user, which includes a single quote.
String sanitizedInput = String.escapeSingleQuotes(userInput);

String soqlQuery = 'SELECT Id, Name FROM Account WHERE Name = \'' + sanitizedInput + '\'';

For Update

SOQL supports the For Update clause. You can use the FOR UPDATE keyword in a SOQL query. This is typically used in Apex code to lock records and prevent other processes from modifying them until the lock is released.

List<Account> accToUpdate = [SELECT Id, Name FROM Account WHERE Industry = 'Technology' FOR UPDATE];

Latest Articles: