—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.
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?”
Account LIMIT 100
[ALT: SOQL on child object that shows how to traverse parent object fields]
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.
Account LIMIT 100
[ALT: SOQL on parent object that shows how to fetch child records]
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.
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.
Contact) LIMIT 100
[ALT: SOQL on parent object that shows how to fetch parent records that have atleast one child record]
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
SELECT task.account.name, task.who.name, task.activitydate
FROM task WHERE task.activitydate <= TODAY AND task.who.type = 'Contact' AND task.account.annualrevenue > 150000000 ORDER BY task.account.annualrevenue DESC