SOQL Join in Salesforce

When working with SQL, you can use the JOIN clause to combine data from one or more tables or objects. However, Salesforce doesn't have a SOQL JOIN clause. Instead, you can use various methods to achieve the same result.

SOQL Join: A few Workarounds

Here are a few methods to combine data from one or more objects on Salesforce using SOQL to get the records. In these examples we will connect two object Opportunity which will be the child and Account which is the parent.

Account and Opportunity relationship in Salesforce for SOQL Join

Child to Parent: Object Relationship

This method allows you to use a field from the parent object regardless of their relationship: lookup or master-detail.

When it's a standard relationship, you must use the API Name of the field without the Id at the end and use a . to access the field of the parent record.

Account Name field on Opportunity Object in Salesforce
SELECT Id, Name, Account.Name, Account.Industry FROM Opportunity

For a custom relationship, it is the same, but make sure to include a __r at the end of the field relationship to access their fields.

Custom field on Opportunity object to explain a custom relationship on a SOQL
SELECT Id, Name, SecondaryAccount__r.Name, SecondaryAccount__r.Industry FROM Opportunity

Parent to Child: Sub-query

This approach is the opposite of the previous method. Here, you can select all the child records from a parent. You must use the Child Relationship Name declared on the field of the child to create the subquery on the parent SOQL.

Parent to child SOQL query from Account to get Opportunity records related

Let's use the same scenario created above as an example; this is how you get data from the child using a standard relationship.

SELECT Id, Name, (SELECT Name FROM Opportunities) FROM Account

On the other hand, if you're using a custom relationship, include the __r at the end of the Child Relationship Name.

SELECT Id, Name, (SELECT Name FROM Opportunities__r) FROM Account