—Updated September 2021—

Developers often need to read data from Salesforce org. Salesforce provides the Salesforce Object Query Language (SOQL) that one can use to read data from Salesforce. SOQL is similar to the standard SQL language but customized for the Salesforce Platform. Developers use SOQL queries in Apex.

SOQL helps answer simple questions like “Who are all the contacts for each account?”, as well as complex questions like “When was the last time we visited key contacts from our top revenue-generating accounts, and who did we visit?” Understanding SOQL relationships is key to building SOQL queries that involve reading data from multiple related objects in Apex code.

Today I’m going to show you three primary SOQL examples that use SOQL relationships. For the queries used in this post, I am going to be using Account and Contact standard objects. Keep in mind the same principles apply to both Standard and Custom objects. For custom objects, the queries require you to use Relationship names. You can read more about Relationship names here.

All of the examples in this blog post can be run using the SOQL Builder VSCode extension. To learn how to install, run and explore results from SOQL using SOQL builder, check out this short video.

Relationship queries

If you’re familiar with SQL, the first thing you might want to do is join the two tables and run a query against their fields. SOQL supports this through relationship queries. Relationship queries, established by lookup and master-detail fields, allow developers to retrieve data through parent-child and child-parent relationships.

Relationship queries can be bidirectional: from a child object looking at a single parent or from a parent object looking at zero or more children. The cardinality of a relationship query has a large impact on SOQL construction – as we’ll soon see.

Child to parent query

Let’s take a look at a simple relationship query. Here we answer the question: “What is the name of our Accounts and which user owns them?”

Query results

Earlier, we stated that cardinality was essential to the structure of the SOQL. Here we are at the child querying the parent, so we use the relationship field ‘owner’ to get the field ‘Name’. We can say, Owner.Name because for each Account record, there will be at most one owner with one name.

You can think of the relationship field as a pointer to the related object. We can then use that pointer to access the fields of the related object. It’s the many looking at the one that allows us to use the previous syntax.

You’ll also want to remember that you can only go one level down in relationships when querying from parent to child and five levels up the relationship chain when querying from a child to a parent.

Parent to child query

What if we wanted to change direction and thus change the cardinality? In this query, we answer the question: “Who are the contacts for each Account?”

To do so, execute the below query in SOQL Builder.

Query results

Notice how the SOQL syntax has changed. Since there are now zero or more possible Contacts for each Account, we can no longer use the relationship field to point to a single Account as we could for the Owner in our first query.

We need to use a nested select to build a result set that contains all the related Contacts. For each Account, we match all the related Contacts with this syntax. There are a couple of other things worth noting. First, we are using the relationship name ‘contacts’ to refer to related child records. Second, I prefaced each “name” field in the query with Account and Contact. The preface isn’t necessary, but it is a best practice to remove ambiguity when reading the statement.

Join types

If you ran the query covered in the previous section in the SOQL Builder, you probably noticed something interesting. The query returned all Account records whether they had a related Contact or not. This is called Outer Join. By default, relationship queries behave like an outer join.

Creating an Inner Join

What can a developer do if they want to create an inner join on these two objects? That is if you want to fetch the accounts that have at least one contact

The following SOQL will do the trick.

Query Results

Here, we use the where clause to match only records where the AccountId exists in the list of matching AccountIds in the related Contacts. Since we are filtering on an indexed field we also make the query a bit faster. This is also an example of a selective query.

Building a More Complex Query using Polymorphic Relationships in SOQL Queries

To show you how to build a more complex query let’s try to create SOQL to answer the question “When was the last time we visited key contacts from our top revenue-generating accounts and who did we visit?”

Let’s execute the below query in the SOQL builder

Query Results

Notice the above SOQL uses polymorphic fields who.name and who.type. Polymorphic fields, are special field types that can point to different objects depending on the type of relationship. To explore more about Polymorphic fields visit the documentation here. Also, note we have used dynamic date functions as a part of your SOQL. We have used TODAY as a part of the filter criteria.

Hopefully, now you have a better understanding of the SOQL Relationship Queries. To learn more about the SOQL and SOSL visit the below documentation and trailhead links.

• SOQL and SOSL References Developer Documentation
• Move From SQL to SOQL Trailhead
• Write SOQL Queries Trailhead

About the Author

Mohith Shrivastava works as a Lead Developer Advocate at Salesforce. He is currently focusing on Salesforce CLI, Heroku, and Lightning Web Components. You can follow him on Twitter @msrivastav13