Skip to main content

Joins in SOQL

If you are familiar with SQL, the biggest gotcha when writing SOQL is how joins are done. You'll see the term Sub-Query used instead of JOIN because of how queries are done on the Salesforce platform. You can still do joins; it's just done a little differently than you're used to.

Sub-Query (Outer Join)

In SQL, a join is when rows are selected from multiple tables and joined together on common columns. Thinking about your tables like a Venn Diagram is a great analogy for this.

Let's say you want to query all Accounts and also include their related Opportunities.

This is an example of an Outer Join because it selects all Accounts and will include their related Opportunities via the sub-query. If an Account does not have an Opportunity, it will still be returned here.

An Outer Join is similar to selecting one whole circle in a Venn Diagram, including the area that overlaps with the other circle.

Sub-Query (Inner Join)

An inner join can seem a little more complex. This is when you need to select the area that intersects between the circles in a Venn Diagram. A more concrete example would be to select all Accounts and their related Opportunities, only when an Account has a related Opportunity.

The addition of line 3 is the only difference between this query and the previous example. The WHERE clause has a sub-query in it which will filter for Accounts whose Id is IN a list of AccountIds selected from Opportunity. This creates out inner join because we now only return Accounts with Opportunities.

Why we Don't Use the JOIN Keyword in SOQL

Each SOQL query must select from a single primary table. This is different from SQL where you can select from Table A and Table B, then join them together. I suspect this is not possible in SOQL because it keeps your queries fast and less prone to hitting limits

It's not uncommon to have a wild SQL query select from many tables, perform complex transformations and output a nicely joined result - all taking 10 minutes to run. Unfortunately SOQL adheres to certain limits that would prevent you from doing something really complex like this. 

While Sub-Queries will solve most of your JOIN use cases in SOQL, you may still find yourself exporting the data and performing transformations outside of Salesforce. It's also not uncommon to toss the data into another database and run SQL on it there instead.

Continued Learning

If you're not familiar with joins, it can be a difficult concept to grasp. Don't get discouraged if you're still struggling with it. There are a lot of great SQL blogs out there that explain it in much more detail than I did.

If you know joins and have been trying to do them in SOQL, I hope this article cleared up how you can (or can't) accomplish that in SOQL.

Here are my favorite links related to doing joins in SOQL: 


Popular posts from this blog

Salesforce Lighting Migration: Plan for Success

In April 2018, I led the Salesforce Lightning Migration at Postmates . This is how I would do it over again if I could. Define Goals The goals of a Lightning Migration are: Enable Lightning Experience Configure Lightning Experience Document all Salesforce processes Create training and support materials Lightning Experience is adopted Easy enough right? Let's go though the plan to see how we accomplish each one. Enable Lightning Experience Just turn it on right? Well, kinda. Although it may be controversial, I recommend turning it on and hiding the option from all users. This can be done by removing the Lightning Experience User permission from your custom profiles.  And yes, turn it on in production .  Why? You're going to turn it on anyway. Salesforce has declared this as the path forward, so you either turn it on now or wait until they flip the switch for you. Plus, turning this on ahead of time will make deploying from a sandbox much  e

Modify Salesforce Data from the Command Line

When doing Salesforce data loads, how often are you putting the same value in an entire column? I find this happening when I need to: Move all accounts from one user to another Assign a set of leads to a specific user Set a field to a specific value Let's use the example from my other post: Salesforce Data Loads from the Command Line - Move all accounts owned by one user to another user. Magic Let's break down each piece of this: soql This is an alias I set for sfdx force:data:soql:query --query . It saves me from having to type this out each time and let's me query from the command line by just typing soql  and my query. This is a popular StackExchange post  that talks about how to set an alias. cat This command outputs the contents of a file. It is common to use this command with other commands by piping the output into another command. In our example, we are piping the output into the cut command. This post gives more examples of how to use t