SOQL Skills

Queries

SOQL Subqueries

Using Subqueries

SOQL subqueries filter records with nested SELECT statements.

Introduction to SOQL Subqueries

SOQL (Salesforce Object Query Language) subqueries are used to filter records by incorporating nested SELECT statements. This allows you to retrieve related data in a single query, making your data retrieval operations more efficient and powerful.

Basic Structure of a SOQL Subquery

A typical SOQL subquery is embedded within a main query using parentheses. The subquery usually retrieves related records from a child relationship in a parent-to-child query or from a parent relationship in a child-to-parent query. Below is a basic example of a parent-to-child subquery:

In this example, the main query retrieves the Name of each Account, and the subquery retrieves the LastName of each related Contact.

Parent-to-Child Subqueries

Parent-to-child subqueries are used when you need to retrieve a list of child records associated with a parent. You specify the relationship name in the subquery. Here's an example:

This query retrieves the Name of each Account in the 'Technology' industry and lists FirstName and LastName of its Contacts with an email address containing '@example.com'.

Child-to-Parent Subqueries

Child-to-parent subqueries are useful when you want to retrieve fields from a parent record that is associated with a child record. To achieve this, you use dot notation to reference the parent fields. Here is an example:

In this child-to-parent query, for each Contact in the 'Finance' industry, the query retrieves the FirstName, LastName, and the name of the associated Account.

Using Subqueries in WHERE Clauses

Subqueries can also be used in the WHERE clause to filter records based on criteria that involve related objects. This can be particularly useful for complex data retrieval needs. Consider the following scenario:

This query retrieves the names of Accounts that have at least one Opportunity with the stage 'Closed Won'. The subquery inside the WHERE clause filters Opportunity records to feed into the main query.

Limitations and Considerations

While SOQL subqueries are powerful, they come with limitations. For instance, subqueries cannot be nested more than one level deep, and they cannot be used with certain keywords, such as GROUP BY. It's also important to consider query performance, as overly complex subqueries can increase execution time.

Queries

Next
IN Subqueries