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: 

Comments

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 ExperienceConfigure Lightning ExperienceDocument all Salesforce processesCreate training and support materialsLightning 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 easier. Trust me.
Goal #1 Enable Lightning Exp…

SOQL from the Command Line

SOQL and the Command Line go together better than peanut butter and jelly. Here's why.
Once upon a time, I got tired of running Salesforce Reports to get miscellaneous data. I found Workbench and started using their interface to run SOQL queries. This was great for a while, but I knew there was a better way.

Pro-tip with Workbench: If you set a browser bookmark after your query runs, you can return to that bookmark and it will re-run your query!

Enter the SFDX Command Line Interface (CLI). Get ready to take your productivity into hyperdrive! 🚀🚀🚀
sfdx force:data:soql:query If there's one command you learn, this is the one. All you have to do is pass a query string into the -q parameter and you're in business. Check it out:
-r | --resultformat This is my 2nd favorite thing. How often do you need to export a csv from Salesforce? And how annoying is it to make throwaway Salesforce reports each time? Yeah, it's the worst.
-r to the rescue! Just add "-r csv" to …

Why should I use SOQL?

You need data, fast!
Whether it's exporting a csv or answering a question, SOQL can get you the result much faster than creating a Salesforce report.

My top 3 reasons to use SOQL are:
Get data fastGet answers fastFlexible access Get Data Fast How often do you find yourself making a throwaway report to check what is happening in the system? For example, you updated your Lead routing and want to see if it's been assigning Leads as you expect. Well you could make a report, run it and review the results. Or you could run a SOQL query.
The benefits of running a SOQL query in this case is that you can retrieve different data sets much quicker. In a Salesforce report, you are stuck with the Report Type you selected when creating the report. With SOQL, you can traverse object relationships in a much more natural way. Get Answers Fast Have you ever been in a meeting and someone asks a data question that no one has the answer to? Well SOQL comes in handy here because you can run a quick …