Skip to main content
Gainsight Inc.

Join Types

IMPORTANT - Articles Impacted due to 6.42 July CS Release

Due to the v6.42 July, 2024 release, this article has been impacted. Steps, images, and playable GIFs in this article will soon be updated to reflect the latest changes.

This article explains the Join types in Rules Engine.

Overview

Basic JOIN clauses are used to combine rows from two or more tables, based on a common field between them. There are four types of Joins supported in Gainsight: Inner Join, Left Join, Right Join, and Outer Join. Each join type, when used with a merge task in Rules Engine and Data Designer, produces a slightly different dataset. 

For a quick video intro to join types, click here. 

IMPORTANT: Before performing MDA Joins in Rules Engine, you must create a lookup relation on an MDA Object. 

Sample Tables

Account Table

account_id account_name
1 acc_1
2 acc_2
3 acc_3
4 acc_4

 

Usage details Table

usage_details_id account_id page_views
u_1 1 100
u_2 2 200
u_2 3 150
u_3 12 300

Retain Common Records from both Dataset: Inner Join

Type value INNER or inner  (need to provide as is in Join chain of JSON config)
Action Performs SQL inner join between selected tasks (tables) on selected fields (columns)
Example

Use-case: Get page views of accounts

SQL:

select acc.account_id, acc.account_name, ud.page_views

From account__c acc Inner Join usageDetails__c ud on acc.account_id=ud.account_id

SQL output

account_id account_name page_views
1 acc_1 100
2 acc_2 200
3 acc_3 150

Retain all Records from Right Dataset: Right Join

Type value RIGHT or right  (need to provide as is in Join chain of JSON config)
Action Performs SQL right join between selected tasks (tables) on selected fields (columns)
Example

SQL:

select acc.account_id, acc.account_name, ud.page_views

From account__c acc right Join usageDetails__c ud on acc.account_id=ud.account_id

SQL output

account_id account_name page_views
1 acc_1 100
2 acc_2 200
3 acc_3 150
null null 300

Retain all records from left dataset: Left Join

Type value

LEFT or left (need to provide as is in Join chain of JSON config)
Action Performs SQL left join between selected tasks (tables) on selected fields (columns)
Example

Use-case: Get page views of all accounts if exists

SQL:

select acc.account_id, acc.account_name, ud.page_views

From account__c acc left Join usageDetails__c ud on acc.account_id=ud.account_id

SQL output

account_id account_name page_views
1 acc_1 100
2 acc_2 200
3 acc_3 150
4 acc_4 null

Retain all Records from both Datasets: Full Outer Join 

Type value

 FULL or full (need to provide as is in Join chain of JSON config)
Action Performs SQL outer join between selected tasks (tables) on selected fields (columns)
Example

Use Case: Get page views of all distinct accounts

SQL:

select acc.account_id, acc.account_name, ud.page_views

From account__c acc left Join usageDetails__c ud on acc.account_id=ud.account_id

UNION

select acc.account_id, acc.account_name, ud.page_views

account__c acc right Join usageDetails__c ud on acc.account_id=ud.account_id

SQL output

account_id account_name page_views
1 acc_1 100
2 acc_2 200
3 acc_3 150
4 acc_4 null
12 null 300
  • Was this article helpful?