Table of Contents
Introduction
Relationship queries are very important when we are working as a developer or even if we are an admin.
Relationship queries are similar to SQL joins. However, you cannot specify the joins like INNER JOIN, and OUTER JOIN.
Understanding the relationship name
Understanding the relationship is very important before writing any SOQL Query. There are two types of relationships in Salesforce
- Parent to Child
- Child to Parent
For child-to-parent relationships, the name of the relationship name is the name of the foreign key.
To get the name of the relationship, follow the below steps
- Login to Salesforce
- Navigate to Object Manager
- Find your object
- Select “Fields & Relationships” from the left panel.
- Click on the Parent Field Label to open the field.
- Field Name is the relationship name
In Case of a Custom relationship, you need to append __r in the relationship name.
SELECT Id, Name, Account.Name, Account.Phone FROM Contact LIMIT 100
SELECT Id, Name, Speaker__r.Name, Session__r.Name FROM SessionSpeaker__c LIMIT 100
For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object’s name.
To get the name of the relationship, follow the below steps
- Login to Salesforce
- Navigate to Object Manager
- Find your object
- Select “Fields & Relationships” from the left panel.
- Click on the Parent Field Label to open the field.
- Child Relationship Name is the relationship name
In Case of a Custom relationship, you need to append __r in the relationship name.
SELECT Id, Name, (SELECT Id, Name, Email FROM Contacts) FROM ACCOUNT
SELECT Id, Name, (SELECT Id, Name FROM Session_Speakers__r) FROM Session__c
Example 1
Write a SOQL Query to get all the Account that does not have any contacts
SELECT Id, Name FROM Account WHERE ID NOT IN (Select AccountId from Contact)
In the above query, the first part is to Query all the account records and then add a filter based on the Id Not IN ( Not Exists ) ( Get all the accounts associated with Contacts )
Example 2
Query all the accounts which have at least one contact associated.
SELECT Id, Name FROM Account WHERE ID IN (Select AccountId from Contact)
There are two ways to get the result of the above query.
- Using Child-to-parent query on the Contact object
- Using Query on account object and filter based on the Account Record Id
If we use the first approach then we will get a duplicate account name because one account is associated with multiple contacts. So we have used the second approach.
Example 3
Write a query to select all the Account records belonging to Education Industry.
Approach: – Always try to break the query or problem into multiple steps. Here are the 2 steps we will use
- Query All the accounts
- Once you got all the account filters WHERE Industry equals to Education
SELECT Id, Name FROM Account WHERE Industry = 'Education'
Example 4
Write a query to list all the contact records whose associated account industry is either education or technology.
Approach:- Now, here in this query we will use the child-to-parent query approach.
Let’s break the query into multiple steps
- Query all the contact records first
- Now, Filter if Account.Industry is equal to Education
- Now, add one more filter with OR condition if Account.Industry is equal to Technology
SELECT Id, Name, Email FROM Contact WHERE Account.Industry = 'Education' OR Account.Industry = 'Technology'
You can also write the above query using the IN parameters where you can modify the WHERE Part like below
WHERE Account.Industry IN (‘Education’, ‘Technology’)
SELECT Id, Name, Email FROM Contact WHERE Account.Industry IN ('Education','Technology')
Joins in S0QL
Now, let’s talk about how we can convert SQL Join queries in Salesforce SOQL or what SQL Join query looks like in Salesforce.
- Right Outer Join
- Left Outer Join
- Left Inner Join
- Right Inner Join
Left Outer Join
Problem – Write a SOQL Query to list all the accounts, along with their contact as well.
Again, here we wanted to list all the Account no matter if there is associated contacts or not.
SELECT Id, Name, (SELECT Id, Name, Email FROM Contacts) FROM ACCOUNT
Right Outer Join
Problem:- Return all the Contacts records along with their Account Name.
So, here in the result, we want all the contacts even if that is not associated with any account. If the contact is not associated with any account then it will return null for that row.
From the above diagram, you can consider Table 2 as Contact and Table 1 as Account.
SELECT Id, Name, Email, Account.Name FROM Contact
Left Inner Join
Problem – Example #2 is the perfect example of a Left inner join. Where we wanted to list all the account records which have at least one associated contact.
SELECT Id, Name FROM Account WHERE ID IN (Select AccountId from Contact)
Right Inner Join
Problem- Example #4 is a good example of Right Inner join where we list the child record if the child record is associated with a parent record. ( List all contact records where account industry is education )
SELECT Id, Name, Email FROM Contact WHERE Account.Industry IN ('Education')
Left Anti Join
Problem- Finding all the parent records with no child records are left anti-join. Example #1 is the perfect example of this.
SELECT Id, Name FROM Account WHERE ID NOT IN (Select AccountId from Contact)
Right Anti Join
Problem – Write a SOQL query to find all the contact record that is not associated with any contacts.
Finding all the orphan child records is Right Anti Join.
SELECT Id, Name, Email, Account.Name FROM Contact Where AccountId = null
