Salesforce SOQL Inner Join and Outer Join Relationships

In Salesforce, using SOQL, we manage data structures and relationships between objects. Executing the inner and outer join concepts is helpful in fetching data more efficiently and enhances our Salesforce applications’ functionality.

In this Salesforce tutorial, I will explain inner joins and outer joins in Salesforce SOQL along with their syntax. Using real-time examples, we will also discuss their use cases.

Table of Contents

What are Inner Joins and Outer Joins in SOQL?

In Salesforce SOQL, we don’t have a prebuilt or predefined JOIN function to combine data from two or more tables based on a related object between them. To fetch the data between related objects and fields, we use relationship query methods such as inner join and outer join to achieve similar results. The outer join method is also known as the left join method.

Inner Join in Salesforce SOQL

Inner Join: SOQL Inner Join statements are used to eliminate the records that do not match related objects. This query method is used for Child-to-parent relationships, where it retrieves parent records related to the child’s records. It uses the dot notation (.) to access the parent field from the child object.

Example of Inner join query method:

The SOQL query below will retrieve contacts along with their related account names.

SELECT Id, Name, Account.Name FROM Contact

Output:

Outer Join in Salesforce SOQL

Outer join: This query method, also known as Outer Left Join, is used for Parent-to-Child relationships, where it retrieves child records related to parent records. As a syntax, it uses sub-queries to retrieve the child records.

Example of outer join query method:

In the below SOQL query, we will retrieve all accounts, including those without associated contacts.

SELECT Name, (SELECT FirstName, LastName FROM Contacts)

Output:

In the above output, Accounts without contacts will still appear in the result, but their subquery will return null for the contacts. This return type is similar to the left outer join behavior.

CROSS Join in Salesforce SOQL

The cross join is a query combining records from two objects without any direct relationship or condition specified. It returns all possible combinations of records between the queried objects, similar to a Cartesian product in SQL.

In SOQL, we don’t have cross join method like SQL. Instead, a cross-join happens when we query two unrelated objects in the FROM clause without using a WHERE clause or specific relationship.

Cross Join for Parent to Child SOQL query:

The below SOQL query will fetch contacts for each account:

SELECT Name, (SELECT LastName FROM Contacts) FROM Account

Cross Join for Child to Parent SOQL query:

The below SOQL query will fetch accounts for each contact:

SELECT LastName, Account.Name FROM Contact

Cross join query for child to parent in Salesforce SOQL

This way, we can use the FROM clause in Salesforce SOQL to get a similar output to the cross-join method.

Limitations for Join Methods in Salesforce SOQL

Since most of the JOIN methods are not directly supported in the Salesforce SOQL so we will see the limitations that are not supported through the alternate method also.

In Salesforce, SOQL does not support Full Outer Joins and is not able to perform full outer joins.
In SOQL, we have limited join conditions, and the Joining objects are limited to those with pre-defined relationships in Salesforce.
No direct Outer Join is supported in SOQL, so we need to adjust the query logic to simulate right outer joins.

Conclusion

In Salesforce SOQL, we need to know the alternative approaches for the join methods to perform join operations, such as inner joins and outer joins, which is crucial for effective data retrieval in Salesforce. By following the above query methods, you will be able to replicate the behavior of join methods and execute the SOQL queries that meet your business needs.