Salesforce Object Query Language (SOQL) is one of the most important concepts in Salesforce Apex development. It is used to fetch data from Salesforce objects, such as Account, Contact, Opportunity, and custom objects.
If you are learning Salesforce or working as a developer, understanding SOQL is essential because almost every Apex class and trigger uses queries to retrieve data.
In this tutorial, we will learn about Salesforce Object Query Language (SOQL) in Apex, from beginner to advanced, with real-world examples, syntax, best practices, and common mistakes.
I will explain when to use SOQL and how to write SOQL in Apex to search and filter Salesforce data effectively.
What is Salesforce Object Query Language (SOQL)?
SOQL (Salesforce Object Query Language) is similar to SQL (Structured Query Language), which is used in databases but designed for Salesforce’s multi-tenant architecture. SOQL is Salesforce’s query language.
It is designed to retrieve data from the Salesforce database according to the specified conditions and objects. These objects can be both standard and custom. Like all Apex code, SOQL is not case-sensitive.
For the standard object, we can write an SOQL query to retrieve data directly. However, for custom objects and fields, we need to add __c as a suffix to fetch the custom object records.
SOQL vs SQL
| Feature | SOQL | SQL |
|---|---|---|
| Used in | Salesforce | Databases |
| Joins | Limited | Full joins |
| Objects | Standard & Custom Objects | Tables |
| Syntax | Simple | Complex |
When to Use SOQL in Salesforce?
It is important to understand when and where to use SOQL to achieve efficient processes and results. Below, I explained when we can use SOQL:
- Retrieve data from a single object or from multiple objects that are related to one another.
- Count the number of records that meet the specified criteria.
- Fetching records based on specific criteria
- Sort results as part of the query.
- Retrieve data from number, date, or checkbox fields.
Basic SOQL Syntax:
SELECT Field1, Field2 FROM Object_Name WHERE ConditionTypes of Queries in SOQL
Below, I have explained the different types of SOQL queries used to fetch the record from the sObject.
1. Basic SOQL Query in Salesforce Apex
In the query below, I fetched all account records from the Salesforce database. We don’t need to add any clauses or conditions to basic SOQL queries.
When creating the SOQL query, we need to specify the field names we want displayed in the list.
SELECT Name, Rating, Active__c FROM AccountAs you click the Execute button, you will see all the account records that you created in the account object. The fields you provided in the SOQL query are the fields you can see as columns in the list.
2. Filtered SOQL Query in Salesforce Apex
A filtered SOQL query in Apex is one with specific conditions or filters applied, allowing you to retrieve only records that match specific criteria.
In the SOQL query below, we fetched account records with high customer priority. The custom priority is a custom field with __c as the suffix, and the account is a standard object we can use directly.
SELECT Name, CustomerPriority__c FROM Account WHERE CustomerPriority__c = 'High'As you execute the query, you can see that we have some account records with high customer priority.
In this way, we can fetch records of any standard or custom object based on a condition of your choice in Salesforce Apex.
3. Fetch Limited Records SOQL Query in Salesforce Apex
In Salesforce Apex, a limit clause in SOQL query is used to retrieve a particular number of records from the database. To retrieve the limited records, we need to add a LIMIT clause to ensure only one record is returned.
In the SOQL query below, I fetched lead records. If there are many leads, but you want only 10 leads, then we can fetch that number of leads using the limit clause.
SELECT Name, LeadSource, Status FROM Lead LIMIT 10As I execute the query, you can see that even if you have multiple records in the object, you will only see the records you provided in the SOQL query.
4. Aggregated SOQL Query in Salesforce Apex
SOQL supports aggregate functions like COUNT(), SUM(), AVG(), and MAX(). These can be used to get summary information from the Salesforce database.
In the SOQL query below, I want to know how many records are in the closed won opportunities and the total amount from those records.
For that, I used the count() and sum() functions, and we need to provide the appropriate fields as parameters.
SELECT COUNT(Id), SUM(Amount) FROM Opportunity WHERE StageName = 'Closed Won'After executing the above query, you can see the results: 13 opportunity records were closed as won, and the total amount for those opportunities is also displayed.
5. SOQL for Custom Object
To fetch the custom object records from the Salesforce database, we need to add __c as a suffix to object and field names.
In the SOQL query below, I retrieved custom employee records with some fields and used the WHERE clause to add a condition.
SELECT Name, domain__c, Joining_Date__c FROM Employees__c WHERE domain__c = 'Salesforce'After executing the query, only those records will display that condition you added in the WHERE clause.
6. Parent-Child SOQL Query Relationship in Apex
In a parent-to-child query, you query records from a parent object and include related child records. This is done using a subquery with the child relationship name.
For example, we have an Account (parent) with related Contact (child) records, and we want to retrieve all accounts with their associated contacts.
In the query below, I created an sObject because I’m fetching only one record using a limit clause. If we want to fetch multiple records, we need to declare a list to store them. In the query, you can see that I first added account fields.
Then, in the account query, I added a contact query as a subquery to fetch contact records, and provided an account ID with a limit.
Then, using the debug statement, I displayed the account record details, and in each loop, I displayed the contact details for the fetched account records.
Account acc = [
SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts)
FROM Account
WHERE Id = '0015i00000KafCAAAZ' LIMIT 1];
System.debug( 'Account Name: ' + acc.Name);
for (Contact con : acc.Contacts) {
System.debug(' Contact Name: ' + con.FirstName + ' ' + con.LastName);
}As you execute the query, you can see the account name, the ID we provided, and the contact names associated with that account record.
7. Child-Parent SOQL Query Relationship in Apex
In a child-to-parent SOQL query, we query records from the child object and include fields from the parent object using dot notation.
For example, we want to fetch contact records and some fields from their parent records, so we need to use child-parent SOQL in Apex.
Here, when we need to add a parent object’s fields, we just need to add the parent object name in dot notation and the field name.
As we are fetching account fields, there should be an account with an account ID; it should not be null.
SELECT Id, FirstName, LastName, Account.Name, Account.Industry FROM Contact WHERE AccountId != nullAs you can see, we displayed the contact records and also the account fields, which are the parent object fields of the contact record.
Variable Binding in Apex SOQL
SOQL variable binding is used when we need to use a variable value in the query as part of any SOQL condition. So, let’s understand where we can use a variable in a SOQL query so that we can use the created variable as a condition in SOQL.
1. Use ID Variable in SOQL Apex
ID is the data type in Salesforce Apex. Here, I have explained how to use a variable in a SOQL query.
Here, I created a class with a method, then used the UserInfo class’s getUserId() method. This method stores the currently logged-in user ID in a variable. I passed that variable into the SOQL query to retrieve user details.
public class GetUserDetails {
public void userdetails() {
Id userId = UserInfo.getUserId();
User u= [ SELECT Name, Email, Profile.Name FROM User WHERE Id = :userId ];
system.debug ( 'Current User Details: ' +u );
}
}In this way, we can retrieve whatever details we want about currently logged-in users using the UserInfo class method and SOQL query in Salesforce Apex.
2. Use String Variable in SOQL Apex
We can also create a string variable and pass it to the SOQL query to retrieve records that meet the condition.
In the code below, I created a string variable and passed the ‘Salesforce’ value to it. Now, in the SOQL query, I’m fetching employee records whose domain is Salesforce, so we can pass the string variable here in the WHERE clause.
public class Demo {
public void Empdetails() {
String strName = 'Salesforce';
List <Employees__c> empList = [ SELECT Name, domain__c FROM Employees__c WHERE domain__c = :strName ];
for (Employees__c e : empList) {
system.debug (' Employee Name : ' + e.Name + ' Domain : '+ e.domain__c );
}
}
}As you execute the query, you can see the employee list whose domain is Salesforce.
In this way, we can use Salesforce Object Query Language (SOQL) to fetch records from the Salesforce database and send them to the user interface.
Different Keywords or Clauses in SOQL
We have seen the basics of Salesforce Object Query Language (SOQL) in Apex and learned why and how to use it. Now, moving forward to keywords or clauses in SOQL, which are used to structure, filter, and manipulate query results effectively.
Each clause performs a specific function, allowing developers to access only the data they require from the Salesforce database.
In the steps below, I explain the different clauses and their functions in the SOQL query used to fetch records from the Salesforce database.
1. SELECT and FROM Clause in SOQL
SELECT:
The SELECT clause is mandatory in SOQL queries and specifies which fields to retrieve from the Salesforce database. It can retrieve multiple fields or even aggregate fields using functions like COUNT(), SUM(), etc. Fields in the SELECT clause are separated by commas.
FROM:
The FROM clause defines the Sobject from which to retrieve data. Only one object can be specified in the FROM clause, though you can query related objects using relationships.
Example:
SELECT Name, Rating, Active__c, Industry FROM Account
2. WHERE Clause in SOQL
The WHERE clause filters query results based on specific conditions, as per your requirements.
Conditions can use operators such as =, !=, >, <, >=, <=, LIKE, IN, NOT IN, and INCLUDES to compare or fetch the records that meet your requirements. The logical operators like AND, OR, and NOT allow us to combine multiple conditions.
Example:
Display only closed won opportunity records with an amount greater than 500000.
SELECT Name, StageName, Amount FROM Opportunity WHERE StageName = 'Closed Won' AND Amount > 500000
3. ORDER BY Clause in SOQL
The ORDER BY clause sorts the query results. It can be sorted in ascending (ASC) or descending (DESC) order and can use multiple fields to sort the result.
This clause is useful for ranking or organizing data based on specific fields.
To display the results in ascending order, we don’t need to add the ASC keyword because the results are sorted in ascending order by default.
Example: Display Result in Ascending Order
Display account records in ascending order. Even though I didn’t add the ASC keyword, the result is displayed in ascending order.
Example: Display Result in Descending Order
SELECT Name, Industry FROM Account ORDER BY Name DESC
Example: Display Null Values First in SOQL Result
When you want to display contact records that are not associated with any account, we can use this SOQL query to retrieve them.
SELECT FirstName, LastName, Account.Name FROM Contact ORDER BY Account.Name NULLS FIRST
4. LIMIT Clause in SOQL
The LIMIT clause in SOQL limits the number of records returned by a query. It allows us to specify a maximum number of records to retrieve, which is particularly useful for performance optimization.
In the SOQL query below, I fetched lead records. If there are many leads, but you want only 10 leads, then we can fetch that number of leads using the limit clause.
SELECT Name, LeadSource, Status FROM Lead LIMIT 10As I execute the query, you can see that even if you have multiple records in the object, you will only see the records you provided in the SOQL query.
5. OFFSET Clause in SOQL
The OFFSET clause in SOQL skips a specified number of rows from the result set. This is particularly useful for implementing pagination when retrieving data from Salesforce, allowing us to control which subset of records to fetch in each query.
OFFSET has a maximum limit of 2,000 rows. This means that we cannot skip more than 2,000 records. OFFSET must be used with an ORDER BY clause to ensure the rows are returned in a predictable order.
Syntax:
SELECT fields FROM object WHERE conditions ORDER BY field OFFSET number_of_rows_to_skip
Example:
First, I want to display only 10 lead records on the 1st page, then another 10 on the 2nd page, which means I need to skip the first 10 records, so I need to add an OFFSET clause.
Page 1: First 10 records:
SELECT Name, LeadSource FROM Lead ORDER BY Name LIMIT 10 OFFSET 0When we set OFFSET to 0, it does not skip any records, so this query returns the first 10 lead results.
Page 2: Skip the first 10 records and display another 10 records.
SELECT Name, LeadSource FROM Lead ORDER BY Name LIMIT 10 OFFSET 10In this query, we added OFFSET 10, which means it will skip the first 10 results and display the result we provided.
6. GROUP BY Clause in SOQL
The GROUP BY clause in SOQL groups rows that share common values in specified fields. It is similar to the SQL GROUP BY clause and is often used with aggregate functions like COUNT(), SUM(), MAX(), or MIN() to calculate summary values for each group.
We can group by multiple fields by separating them with commas.
Syntax:
SELECT field, aggregate_function (field) FROM object WHERE condition GROUP BY fieldExample: Group Records with Count in SOQL
When we want to count the account records for a particular Industry, we can create a SOQL query with a GROUP BY clause and an industry field. Using the count() function, we can get the number of records generated from different industries.
SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
Example: Group Records by Multiple Fields in SOQL
In the SOQL query below, I retrieved account records with industry and billing country grouping fields. In a group of multiple records, the result will display the first industry, the billing city, and the record count.
After that, the same industry and another billing city. Like this, it will display the Groupwise record count.
SELECT Industry, BillingCountry, COUNT(Id) FROM Account GROUP BY Industry, BillingCountry
Frequently Asked Questions
Q1: What is SOQL used for?
To retrieve data from Salesforce
Q2: Can we use joins in SOQL?
Limited via relationships
What is the limit of SOQL?
100 queries per transaction
Conclusion
SOQL is a powerful tool in Salesforce Apex that helps developers retrieve and manage data efficiently. By understanding basic to advanced concepts such as relationship queries, aggregate functions, and governor limits, you can write optimized, scalable Apex code.
I hope you have an idea about Salesforce Object Query Language (SOQL) in Apex. In that, I have explained when to use SOQL, its syntax, and basic SOQL examples.
Then, we have seen some SOQL query types to fetch records from Salesforce objects, as well as SOQL variable binding in Apex.
You may like to read:
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
