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 (
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.
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
SELECT Name, Employer__r.Name FROM Account
This would return all the account names with their associated employer names.