Join Types

Last updated
Save as PDF

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