Salesforce Docs Portal

Using the Join Clause for Insights

A calculated insight can simultaneously access multiple data model objects (DMOs). A calculated insight query that accesses multiple rows of the different DMOs at one time is called a join query. Data 360 supports four types of joins in a calculated insight: inner, left, right, and full.

Required Editions

Available in: All Editions supported by Data 360. See Data 360 edition availability.
Inner, left, right, and Join image example for Calculated Insights

Inner Join

An inner join returns all records that have matching values in two DMOs.

Example
Example 
SELECT 
SUM(SALESORDER__dlm.grand_total_amount__c) as TotalOrderAmount__c, 
Individual__dlm.Id__c as CustomerId__c
FROM  SALESORDER__dlm
INNER JOIN Individual__dlm ON SALESORDER__dlm.partyid__c = Individual__dlm.Id__c 
GROUP BY CustomerId__c

Left Join

A left join returns all records from the left table and the matched records from the right DMO.

Example
Example 
SELECT 
SUM(SALESORDER__dlm.grand_total_amount__c) as TotalOrderAmount__c, 
Individual__dlm.Id__c as CustomerId__c
FROM  SALESORDER__dlm
LEFT JOIN Individual__dlm ON SALESORDER__dlm.partyid__c = Individual__dlm.Id__c 
GROUP BY CustomerId__c

Right Join

A right join returns all records from the right table and the matched records from the left DMO.

Example
Example 
SELECT 
SUM(SALESORDER__dlm.grand_total_amount__c) as TotalOrderAmount__c, 
Individual__dlm.Id__c as CustomerId__c
FROM  SALESORDER__dlm
RIGHT JOIN Individual__dlm ON SALESORDER__dlm.partyid__c = Individual__dlm.Id__c 
GROUP BY CustomerId__c

Full Join

A full join returns all records when there’s a match in either the left or right DMO.

Example
Example 
SELECT 
SUM(SALESORDER__dlm.grand_total_amount__c) as TotalOrderAmount__c, 
Individual__dlm.Id__c as CustomerId__c
FROM  SALESORDER__dlm
FULL JOIN Individual__dlm ON SALESORDER__dlm.partyid__c = Individual__dlm.Id__c 
GROUP BY CustomerId__c
  • Autopopulated Join Keys in Data 360
    When you join two or more objects in Insight Builder, the join nodes and join keys are autopopulated based on their relationships in Data 360. A key join is a simple way to join link tables. Autopopulated join nodes make it easy to connect engagement data with a unified profile and override autosuggested joins.