This article is a SOQL 101 Primer to get you up and running with Salesforce SOQL and learn some SOQL join examples and use cases.

What is SOQL?

We use SOQL (Salesforce Object Query Language) to search your Salesforce org’s data for specific information.

We can use SOQL to retrieve, count or sort the record data from your Salesforce org – this is equivalent to SQL but it performs joins very differently.

Where can I use SOQL?

• Using queryString parameter in a SOAP API query()
• Using q parameter in a REST APU query request
• Used in Apex (Controllers, Classes etc)
• Used in the Salesforce CLI eg. force:data:soql:query
• VS code extensions

Can I use SOQL to query more than one object?

Yes, there is specific SOQL syntax that allows the ability to query more than one object type at a time using what we call relationship queries. In this post, we will give examples of the inner and outer joins using relationship queries.

What are Relationship Queries?

Relationship queries are similar to SQL joins. However, you cannot perform arbitrary SQL joins. Basically, there should be a relationship between the records for them to be returned by any SOQL query.

In SQL you can join the records if there is some data common among the tables. In SOQL it only works when there is a relationship. You can use relationship queries to return objects of one type based on criteria that apply to objects of another type.

To be able to traverse these relationships for standard and custom objects, a relationship name is given to each relationship

These are the two main relationships:

• Parent-to-child
• Child-to-parent

Parent-to-child

The parent object has a name for the child relationship that is unique to the parent. Eg. Cases, Assets, Contacts. For custom objects __c changes to __r in plural form. This can be traversed using a NESTED SOQL Query

Standard Object:

SELECT Name, (SELECT FirstName FROM Contacts) FROM Account

Custom Object

SELECT Name, (SELECT Name FROM ChildObjects__r) FROM ParentObject__c

Child-to-parent

The relationship with the parent is the name of the foreign key. Eg. the Contact child object has a child-to-parent relationship with Account. For custom objects __c changes to __r. To access the parent information we use a dot notation.

Standard Object:

SELECT FirstName, Account.Name FROM Contact

Custom Object

SELECT Name, ParentObject__r.Name FROM ChildObject__c

Join and SOQL Relationship Query Examples

Here are the different Join examples and what it looks like in SOQL with the relationship Queries.

Inner Join – used to join the objects (child-to-parent)

SELECT Id, Name, Account.Name FROM Contact

Outer Join – relationship SOQL queries return records, even if the relevant foreign key field has a null value, (only for lookup relationship) as with an outer join same inner join query works the same.

SELECT Id, Name, Account.Name FROM Contact

The same query but there is no Account record, Account.Name will return null.

Left Inner Join – using Semi-Join with IN allows querying values in a field where another field on the same object is specified. A semi-join is a subquery on another object in an IN clause to restrict the records returned.

SELECT Name FROM Position_c WHERE Id IN
(SELECT Position__c FROM Job_Application__c)

An anti-join is a subquery on another object in a NOT IN clause to restrict the records returned.

Right Inner Join – using the relationship to return only the match from what you can say is the right table (Position) or Foreign key that matches the query.

SELECT Name,Position__r.Name,
FROM Job_Application__c
WHERE Position__r.Department__c = ‘Sales’

Limitation and Summary

• Objects must have a relationship to create a join in SOQL.
• No more than 55 child-to-parent relationships can be specified in a query
• No more than 20 parent-to-child can be specified in a query
• No more than 5 levels can be specified in a child-to-Parent (eg. Contact.Account.Owner.FirstName)
• Semi-join allows the matching of different objects using a subquery. You can use at most two subqueries in a single semi-join or anti-join query.