SOQL Query in Salesforce Apex & Examples

Table of Contents

• What is SOQL?
• SOQL Cheat Sheet
• SOQL Variable Binding
• Keywords Used In SOQL Queries
• Traversing Relationship Fields
• Fetching Child Records
• Fetching Parent Record
• Aggregate Functions
• Binding Apex Variables
• How to Start with SOQL?
• What are the differences between SOQL, SOSL, and SQL?
• How to write a basic SOQL Query?
• How to find Object and Field API Names for your Queries?
• How to use SOQL in Apex?
• Logistics SOQL Examples
• Education SOQL Examples
• FAQs

What is SOQL in Salesforce?

SOQL stands for Salesforce Object Query Language. SOQL can be used to access information stored in your organization’s database. The syntax of SOQL is similar to that of SQL (Structured Query Language).SOQL queries can be written in Apex code or in the Query Editor of the Developer Console. In Soql records can only be searched on a single sObject if it meets the given criteria. Unlike SOSL, it cannot search across multiple objects but nested queries are supported in SOQL.

SQL (Structured Query Language) is the standard language for managing and manipulating databases. Here’s an overview of the basic syntax used in SQL to perform common operations such as selecting data, inserting records, updating records, and deleting records.

Checkout: SOSL Query in Salesforce Apex

1. SELECT Statement

The SELECT statement is used to select data from a database. Data is retrieved from one or more tables.

Syntax:

SELECT column1, column2, ...
FROM tableName
WHERE condition;

2. INSERT INTO Statement

The INSERT INTO statement is used to insert new records into a table.

Syntax:

INSERT INTO tableName (column1, column2, ...)
VALUES (value1, value2, ...);

3. UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE tableName
SET column1 = value1, column2 = value2, ...
WHERE condition;

Read more: Database methods – Salesforce Apex

4. DELETE Statement

The DELETE statement is used to delete existing records from a table.

Syntax:

DELETE FROM tableName
WHERE condition;

5. CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in the database.

Syntax:

CREATE TABLE tableName (
    column1 dataType constraints,
    column2 dataType constraints,
    ...
);

Additional Clauses

• WHERE: Specifies conditions for the SELECT, UPDATE, and DELETE statements.
• ORDER BY: Orders the results according to one or more columns.
• GROUP BY: Groups rows that have the same values in specified columns into summary rows.
• HAVING: Specifies a condition on the groups being created.

Read more: DML in Salesforce Apex

CRS Info Solutions offers a comprehensive and dynamic Salesforce online course career building program for beginners, covering admin, developer, and LWC concepts. This course features immersive real-time projects, interactive hands-on learning, detailed daily notes, essential interview questions, thorough certification preparation, and strategic job prep guidance. Join their inspiring free demo to embark on an exciting Salesforce journey with expert mentorship and unlock your full potential in the Salesforce ecosystem. Enroll for a free demo today!

SOQL Cheat Sheet

This cheat sheet covers the basic syntax and common uses of SOQL commands to help with quick reference during development.

Read more: Strings in Salesforce Apex

SOQL Variable Binding

SOQL (Salesforce Object Query Language) variable binding allows you to use a single set of query string and dynamically pass values to the query at runtime. This helps prevent SOQL injection and makes your queries more efficient.

Example:

Consider a simple SOQL query without variable binding:

String name = ‘John’;
List accounts = [SELECT Id, Name FROM Account WHERE Name = :name];

In this example, :name is a bind variable that gets replaced with the value of the name variable when the query is executed. This approach helps protect against SOQL injection and allows for more flexible query construction.

Collection is one of the important concept, checkout: Collections in Salesforce Apex

Keywords Used In SOQL Queries

In Salesforce Object Query Language (SOQL), keywords play a crucial role in crafting precise and effective queries to retrieve data from Salesforce objects. These keywords, such as SELECT, FROM, WHERE, and ORDER BY, among others, provide the structure and logic necessary to filter, sort, and manipulate data according to specific criteria. Understanding and utilizing these keywords effectively is essential for Salesforce developers and administrators to extract meaningful insights and drive informed decision-making within their organizations.

SELECT: Retrieves one or more fields from an object.

SELECT Id, Name FROM Account

FROM: Specifies the object from which to retrieve records.

SELECT Id, Name FROM Account

WHERE: Filters records based on specified criteria.

GROUP BY: Groups query results by specified fields.

HAVING: Adds additional filtering after GROUP BY.

LIMIT: Specifies the maximum number of records to return.

OFFSET: Skips a specified number of records before returning results.

SELECT Id, Name FROM Account LIMIT 10 OFFSET 5

Checkout: DML statements in Salesforce

IN: Specifies a list of values for a field.

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

NOT IN: Specifies a list of values not to include.

SELECT Id, Name FROM Account WHERE Industry NOT IN (‘Retail’, ‘Healthcare’)

LIKE: Performs a partial match search.

SELECT Id, Name FROM Account WHERE Name LIKE ‘%Corp%’

AND: Combines multiple conditions in a WHERE clause.

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

OR: Specifies multiple conditions, of which at least one must be true.

SELECT Id, Name FROM Account WHERE Industry = ‘Technology’ OR Industry = ‘Finance’

FOR UPDATE: Locks the selected rows for update.

SELECT Id, Name FROM Account WHERE Name = ‘Acme’ FOR UPDATE

ALL ROWS: Allows querying of all records, including archived and deleted records:

SELECT Id, Name FROM Account WHERE Name = ‘Acme’ ALL ROWS

Checkout: Data types in Salesforce Apex

Traversing Relationship Fields in SOQL

Let’s traverse relationship fields in Salesforce using an example. Suppose we have two custom objects: Account and Contact, where Contact has a lookup relationship to Account called Account__c.

Read more: Salesforce apex programming examples

Fetching Child Records in SOQL

Let’s fetch child records related to a parent record in Salesforce using an example. We’ll consider a scenario where we have two custom objects: Account (parent) and Contact (child), with a lookup relationship from Contact to Account. Here’s how we can fetch child records (Contact) related to a parent record (Account):

// Define the ID of the parent Account record
Id parentAccountId = 'Insert_Account_ID_Here'; // Replace with actual Account ID

// Querying child Contact records related to the parent Account
List<Contact> childContacts = [SELECT Id, Name, Email 
                               FROM Contact 
                               WHERE AccountId = :parentAccountId];

// Iterating through the queried child Contact records
for (Contact childContact : childContacts) {
    // Accessing child Contact fields
    String contactName = childContact.Name;
    String contactEmail = childContact.Email;

    // Printing child Contact information
    System.debug('Contact Name: ' + contactName);
    System.debug('Contact Email: ' + contactEmail);
}

Explanation:

1. Parent Account ID: We define the ID of the parent Account record from which we want to fetch child Contact records. Replace 'Insert_Account_ID_Here' with the actual ID of the parent Account.
2. SOQL Query: We use a SOQL query to retrieve child Contact records (SELECT Id, Name, Email FROM Contact WHERE AccountId = :parentAccountId). We filter the contacts based on the AccountId field, which represents the lookup relationship to the parent Account.
3. Iteration: We loop through the queried child Contact records using a for loop.
4. Accessing Fields: Within the loop, we access fields of each child Contact using dot notation (.). For example, childContact.Name accesses the Name field of the child Contact.
5. Debugging: We print out information about each child Contact using System.debug() statements for debugging purposes.

Read more: Loops in Salesforce Apex

Fetching Parent Record in SOQL

Let’s fetch the parent record related to a child record in Salesforce using an example. We’ll consider a scenario where we have two custom objects: Account (parent) and Contact (child), with a lookup relationship from Contact to Account. Here’s how we can fetch the parent record (Account) related to a child record (Contact):

// Define the ID of the child Contact record
Id childContactId = ‘Insert_Contact_ID_Here’; // Replace with actual Contact ID

// Querying the parent Account record related to the child Contact
Contact childContact = [SELECT Id, Name, Email, AccountId, Account.Name, Account.Industry
FROM Contact
WHERE Id = :childContactId];

// Accessing parent Account fields from the related Contact record
String accountId = childContact.AccountId;
String accountName = childContact.Account.Name;
String accountIndustry = childContact.Account.Industry;

// Printing parent Account information
System.debug(‘Account ID: ‘ + accountId);
System.debug(‘Account Name: ‘ + accountName);
System.debug(‘Account Industry: ‘ + accountIndustry);

Explanation:

1. Child Contact ID: We define the ID of the child Contact record for which we want to fetch the related parent Account record. Replace 'Insert_Contact_ID_Here' with the actual ID of the child Contact.
2. SOQL Query: We use a SOQL query to retrieve the child Contact record along with the related parent Account information (SELECT Id, Name, Email, AccountId, Account.Name, Account.Industry FROM Contact WHERE Id = :childContactId). We use dot notation (.) to traverse the relationship and access fields from the parent Account object.
3. Accessing Parent Fields: After executing the query, we access fields of the parent Account from the related Contact record using dot notation (.). For example, childContact.Account.Name accesses the Name field of the parent Account.
4. Debugging: We print out information about the parent Account using System.debug() statements for debugging purposes.

You can explore all the String methods in Apex, and learn those examples for each method.

Aggregate Functions in SOQL

Aggregate functions in Salesforce are used to perform calculations on a set of records and return a single result. Here’s an example of using aggregate functions in SOQL:

Suppose we have a custom object called Opportunity with fields Amount (representing the amount of the opportunity) and StageName (representing the stage of the opportunity). We want to calculate the total amount of all opportunities and the average amount of opportunities in each stage.

Watch our FREE Salesforce online course video, it’s a full length free tutorial for beginners.

Binding Apex Variables in SOQL

Binding Apex variables, also known as binding variables or bind expressions, are used to dynamically pass values into SOQL queries, SOSL searches, and dynamic SOQL queries in Salesforce. They help prevent SOQL injection and improve performance. Here are examples of how to use binding variables in various contexts:

Static SOQL Query with Binding Variable:

String searchKey = 'Test'; // Value to search for
List<Contact> contacts = [SELECT Id, Name, Email FROM Contact WHERE LastName = :searchKey];

In this example, searchKey is a binding variable. It is used in the SOQL query to dynamically filter contacts by the last name specified in the searchKey variable.

String searchKey = 'Test'; // Value to search for
List<Contact> contacts = [SELECT Id, Name, Email FROM Contact WHERE LastName = :searchKey];

Dynamic SOQL Query with Binding Variable:

String objectType = 'Contact'; // Object type to query
String fieldName = 'LastName'; // Field name to filter
String searchKey = 'Test'; // Value to search for

String soqlQuery = 'SELECT Id, Name, Email FROM ' + objectType + ' WHERE ' + fieldName + ' = :searchKey';
List<SObject> records = Database.query(soqlQuery);

In this example, we construct a dynamic SOQL query using variables objectType, fieldName, and searchKey. The searchKey variable is bound to the query using the : syntax.

Read more: Workflow rules in Salesforce

SOSL Search with Binding Variable:

String searchKey = ‘Test’; // Value to search for
List> searchResults = [FIND :searchKey IN ALL FIELDS RETURNING Contact(Id, Name, Email)];

Here, searchKey is bound to the SOSL search query using the :searchKey syntax. This searches for the specified keyword in all fields of the Contact object.

DML Statements with Binding Variables:

Account accToUpdate = new Account(Name = ‘Example Account’);
update accToUpdate;

In DML statements like update, insert, delete, and upsert, binding variables are used implicitly. In this example, accToUpdate is bound to the update operation, and its changes are persisted to the database.

Binding variables help make queries and DML operations dynamic, secure, and efficient by allowing values to be passed dynamically into queries and DML statements, thus preventing SOQL injection attacks and improving performance.

How to start with SOQL?

To work on SOQL you must have records in the database. So, before starting working on Soql follow the below steps to insert some records via apex.

Step 1: Open the developer console and click on the Debug tab to open the anonymous window as shown below the image.

Read more: Latest Salesforce interview questions and answers.

Using the Query Editor, we can now write and execute a SOQL query to fetch the above-inserted records. A SOQL query searches for data in a specific object and optionally, you may add a condition to the WHERE clause to filter your search.

Read more: Validation Rules in Salesforce

In the image below we have queried the Account and its associated contact and opportunity records and filtered the result by Account Name.

Key Features: 

1. SOQL allows you to query records from Standard and Custom objects in one go.
2. SOQL allows querying on a single object or multiple objects that are related to one another. 
3. SOQL supports aggregate functions just like SQL. We can roll up and summarize data with aggregate functions. For example, Avg, Min, Max, etc.
4. SOQl allows binding apex variables in the query to filter the records.
5. SOQl supports Parent to Child and Child to Parent queries.

Watch our FREE Salesforce online course video, it’s a full length free tutorial for beginners.

Frequently Asked Questions

What are the differences between SOQL, SOSL, and SQL?

SOSL, or Salesforce Object Search Language, is like a powerful tool I use to find stuff in Salesforce. Imagine I’m searching for a specific word in a big library full of books. SOSL helps me do just that. It’s like a magic spell that lets me search across multiple books, not just one, to find what I need. So, instead of looking through each book one by one, SOSL quickly sifts through all the books and shows me the ones that mention the word I’m looking for. It’s super handy when I need to find information fast and don’t want to spend all day searching.

SOQL and SOSL are like cousins to SQL, but not identical twins. They speak a similar language, but they have their own quirks tailored specifically for Salesforce. Imagine SQL as the language you use to talk to a big library full of books, while SOQL and SOSL are the languages you use to talk to a specialized library, like a library of comic books or recipe books. They understand each other to some extent, but they also have their own unique words and phrases that make them different.

So, while you might recognize some similarities between them, they’re not exactly the same, just like cousins who share a family resemblance but have their own distinct personalities. SOQL and SOSL are designed for querying data within the Salesforce platform, each serving a specific purpose: SOQL for querying records from Salesforce objects and SOSL for performing full-text searches across multiple objects.

Checkout: Variables in Salesforce Apex

Examples:

SOQL Query:

SELECT Id, Name, Account.Name FROM Contact WHERE Account.Name = ‘ACME’

This SOQL query retrieves the Id, Name, and the related Account’s Name of all contacts where the related Account’s Name is ‘ACME’.

SOSL Query:

FIND {ACME} IN ALL FIELDS RETURNING Contact(Id, Name), Account(Name)

This SOSL query searches for the keyword ‘ACME’ across all fields of the Contact and Account objects and returns matching Contact records with their Id and Name, as well as matching Account records with their Name.

SQL Query:

SELECT id, first_name, last_name FROM employees WHERE department = ‘Sales’

This SOSL query searches for the keyword ‘ACME’ across all fields of the Contact and Account objects and returns matching Contact records with their Id and Name, as well as matching Account records with their Name.

How to write a basic SOQL Query?

To write a basic SOQL (Salesforce Object Query Language) query, you can use the SELECT statement followed by the fields you want to retrieve from a Salesforce object. Here’s a basic example of a SOQL query:

const query = 'SELECT Id, Name, AccountNumber FROM Account';

In this example, the query retrieves the Id, Name, and AccountNumber fields from the Account object in Salesforce.

Checkout: Data types in Salesforce Apex

You can execute this query using Salesforce’s JavaScript Remoting, Apex, or other Salesforce API methods depending on your use case. For example, using JavaScript Remoting, you would define a remote action in your Apex controller and call it from your JavaScript code:

Apex Controller:

public with sharing class AccountController {
    @RemoteAction
    public static List<Account> getAccounts() {
        return [SELECT Id, Name, AccountNumber FROM Account];
    }
}

JavaScript

Visualforce.remoting.Manager.invokeAction(
    '{!$RemoteAction.AccountController.getAccounts}',
    function(result, event) {
        if (event.status) {
            // Process the result
            console.log(result);
        } else {
            console.error(event.message);
        }
    }
);

This is a basic example, and SOQL queries can be more complex, including conditions (WHERE clause), ordering (ORDER BY), and grouping (GROUP BY).

To write a basic SOQL (Salesforce Object Query Language) query, you first specify the fields you want to retrieve using the SELECT clause. For instance, let’s say we want to retrieve the “Name” and “Industry” fields from the “Account” object. We would write the SELECT clause like this: “SELECT Name, Industry”. This tells Salesforce to return the “Name” and “Industry” fields in the query results.

Next, we specify the object from which we want to retrieve the data using the FROM clause. In this example, we want to retrieve data from the “Account” object, so we write “FROM Account”. This indicates that we want to query data from the “Account” object specifically.

Putting it all together, our basic SOQL query looks like this: “SELECT Name, Industry FROM Account”. This query tells Salesforce to retrieve the “Name” and “Industry” fields from the “Account” object. It’s a simple yet powerful way to extract specific information from Salesforce objects, and it forms the foundation for more complex queries involving additional clauses like WHERE, ORDER BY, GROUP BY, and others.

How to find Object and Field API Names for your Queries?

To find Object and Field API Names for your queries in Salesforce, you can utilize several methods. One common approach is to navigate to the Object Manager within the Salesforce Setup menu. From there, you can search for the specific object you’re interested in, such as “Account” or “Contact.” Once you locate the object, click on it to access its details. Here, you’ll find a list of all the fields associated with that object, along with their respective API Names. These API Names serve as unique identifiers for each field and are crucial for referencing them accurately in your SOQL queries.

Read more: Salesforce apex programming examples

By leveraging these methods, you can efficiently discover Object and Field API Names required for your SOQL queries in Salesforce. Whether you’re a Salesforce administrator, developer, or consultant, understanding how to find and utilize Object and Field API Names is essential for effectively querying and manipulating data within the Salesforce platform.

Click on “Execute” to run the code. The Developer Console will display the Field Label and API Name for each field of the Account object.

To find the Object and Field API Names for your queries in Salesforce, you can use the Schema class in Apex. Here’s a code snippet that demonstrates how to retrieve the API Names of an object and its fields:

This code snippet demonstrates how to retrieve the API Name of an object (Account in this case) and its fields. You can modify the objectApiName variable to specify the object for which you want to retrieve the schema.

Read more: Methods – Salesforce Apex

How to use SOQL in Apex?

In Apex, SOQL (Salesforce Object Query Language) is employed to retrieve records from Salesforce objects. SOQL queries in Apex are expressed as strings and can be executed using various methods provided by the Salesforce platform. One common approach is to write a static SOQL query directly within your Apex code. For example, you can use a static query to fetch a list of Account records where the Industry field is set to ‘Technology’. This involves writing the query directly within square brackets and assigning the result to a list variable, as shown:

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

In addition to static queries, you can construct SOQL queries dynamically at runtime using string concatenation or by leveraging the Database.query() method. This approach allows you to build queries based on dynamic criteria. For instance, you might define query conditions based on user input or runtime variables. Dynamic queries enable greater flexibility in querying data from Salesforce objects within your Apex code. Here’s an example of constructing a dynamic SOQL query:

Read more: Array methods in Salesforce Apex

String industry = 'Technology';
String soqlQuery = 'SELECT Id, Name FROM Account WHERE Industry = \'' + industry + '\'';
List<Account> accounts = Database.query(soqlQuery);

Furthermore, SOQL supports the use of binding variables, which allow you to dynamically pass values into the query at runtime. Binding variables are denoted by a colon followed by the variable name. This helps prevent SOQL injection and enhances query security. Here’s how you can use binding variables in a SOQL query:

String industry = 'Technology';
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = :industry];

Moreover, SOQL allows you to query fields on related objects using relationship fields. For example, you can query fields on child objects using parent-child relationship fields. Once you have executed a SOQL query, you can iterate through the query results using standard Apex iteration methods like for loops. This enables you to process and manipulate the retrieved data as needed within your Apex code. By leveraging SOQL in Apex, you can effectively interact with Salesforce data and build robust applications on the Salesforce platform.

Difference between SOQL and SOSL

Checkout: Interfaces – Salesforce Apex

Frequently Asked Questions (FAQs)

What tool to run SOQL query in Salesforce?

To run SOQL queries in Salesforce, you can use several tools, including the Salesforce Developer Console, Workbench, and the Query Editor in Salesforce Setup. The Developer Console is a robust tool that provides a user-friendly interface for writing and executing SOQL queries. Workbench is another powerful web-based tool that allows you to run SOQL queries and interact with Salesforce data. Additionally, the Query Editor, accessible through the Salesforce Setup, offers a simple interface for executing SOQL queries directly within your Salesforce org.

What is the full form of SOQL?

The full form of SOQL is Salesforce Object Query Language. It is a query language provided by Salesforce to retrieve data from the Salesforce database. SOQL is similar to SQL (Structured Query Language) but is specifically designed to work with Salesforce objects and fields.

Which three data types can a SOQL query return?

A SOQL query can return three primary data types:

1. List<sObject>: A list of standard or custom objects, where each item in the list is a record that matches the query criteria.
2. List<AggregateResult>: A list of aggregate result objects, typically used when performing aggregate queries such as COUNT, SUM, AVG, MIN, and MAX.
3. Integer: An integer value, usually returned when using aggregate functions like COUNT to determine the number of records that match the query criteria.

What is the maximum length of a SOQL query?

The maximum length of a SOQL query is 20,000 characters. This limit includes the query string and any variable bindings used in the query. It is important to be mindful of this limit when constructing complex queries to ensure they do not exceed the maximum allowed length.

How many records we can query in SOQL?

In a single SOQL query, you can retrieve up to 50,000 records. This limit applies to the total number of records returned by the query. If you need to process more records, you may need to use pagination techniques or batch Apex to handle larger datasets efficiently.

How do I get a single record in SOQL?

To get a single record in SOQL, you can use the LIMIT clause to restrict the query result to one record. For example:

Account singleAccount = [SELECT Id, Name FROM Account LIMIT 1];

This query retrieves the first account record from the Account object. It’s important to note that if you expect only one record, you can directly assign the result to a single sObject variable.

How do I fetch all records in SOQL?

To fetch all records in SOQL, you write a query without any filtering criteria. However, it’s important to be mindful of Salesforce’s governor limits. Here’s an example:

List<Account> allAccounts = [SELECT Id, Name FROM Account];

This query retrieves all account records. If the number of records exceeds the governor limits, you might need to use batch processing or pagination to handle the data efficiently.

How many levels we can query in SOQL?

In SOQL, you can query up to five levels of parent-child relationships in a single query. However, there are some limitations on the number of parent-to-child subqueries you can include. The query depth limit in Salesforce ensures that complex queries remain performant and within governor limits.

How do I resolve too many SOQL queries?

To resolve the “too many SOQL queries” error, you can:

1. Optimize Queries: Ensure your queries are efficient and retrieve only necessary fields and records.
2. Reduce Loop Queries: Avoid placing SOQL queries inside loops. Instead, use bulk queries outside of loops.
3. Use Collections: Retrieve records in bulk and use collections (lists, sets, maps) to process the data.
4. Use Batch Apex: For large data operations, consider using Batch Apex to process records in manageable chunks.
5. Leverage Caching: Cache results when appropriate to avoid repetitive queries.

How to retrieve more than 50,000 records in Salesforce SOQL?

To retrieve more than 50,000 records in Salesforce SOQL, you can use one of the following approaches:

Batch Apex: Use Batch Apex to process records in batches of up to 200 records per batch, allowing you to handle large data sets efficiently.