In CRM Analytics, a full outer join includes all rows from the left (recipe data) and right, regardless of whether they have matches. The join includes all matched rows in the target when multiple rows match.
Required Editions
| Available in Salesforce Classic and Lightning Experience. |
| Available with CRM Analytics, which is available for an extra cost in Enterprise, Performance, and Unlimited Editions. Also available in Developer Edition. |
Our company's marketing team captures demographic data in Redshift for opportunities stored in Salesforce. To help create more targeted campaigns, the team is segmenting our current opportunities by the Education Level demographic. Let’s use a full outer join to combine the opportunity and demographic data.
Consider the following two data streams that feed the recipe’s target dataset. To illustrate how this recipe function handles unmatched rows and duplicate keys, we included them in both data streams.
After performing the full outer join based on the matching the keys, the recipe produces the following target dataset.
All rows—matched or not—are included in the target. Because of multiple matches, you see two records with Opp_ID 2 (Amy and Bob) and two more records with Opp_id 1 (High School and Secondary School). Opp_ID 4 and Opp_id 5 are also included despite not having a match.
By including unmatched left records, we now know that we have to find out David’s education level to complete this dataset. By including unmatched right records, we see that no customers have a PhD.