Joining Related Salesforce Objects in SOQL

Objects in Salesforce can be related to one another via a parent-child relationship. For example, the Account object is the parent to the child Contact object; an Account record can have one or many Contact records. Much like in SQL, the child has a field that acts as a foreign key to the parent record. One way of thinking of this is with the analogy that a child has the DNA of the parent that we can use to relate the child back to them.

It is relatively simple to query Salesforce using SOQL to include relations. However, whereas in SQL we would use a JOIN statement, in SOQL we just need to reference the parent or child object in our query.

Child with parent

When querying from the child object we can include the parent object using dot notation ([object].[field]):

SELECT FirstName, LastName, Account.Name FROM Contact

This query will return all the contact records’ first and last names along with the parent records’ names.

Salesforce knows we want to join accounts onto the contacts as we have referenced the parent object in our select columns.

Parent with children

When querying from the parent object we do not use the dot notation, instead we use a nested SOQL query:

SELECT Name, (SELECT FirstName, LastName FROM Contacts) FROM Account

Here we are getting out all the account records and returning the account name and the first and last names of all the related contacts.

Again, Salesforce will automatically handle the join between the two objects.

Custom objects

One thing to watch out for is when dealing with custom objects. These tend to have a name ending with the suffix __c. When referencing these custom objects in the dot notation the suffix wants replacing with __r when on the left side of the dot.

For example, if our accounts belonged to a custom employer object, identified by Employer__c:

SELECT Name, Employer__r.Name FROM Account

This would return all the account names with their associated employer names.