Join Types
- Last updated
- Save as PDF
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
|
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
|
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
|
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
|