Skip to main content
Gainsight Inc.

Join Types

 

Basic JOIN clause is used to combine rows from two or more tables, based on a common field between them.

Types of Join: Inner Join, Left Join, and Right Join.

Sample tables

account__c

account_id account_name
1 acc_1
2 acc_2
3 acc_3
4 acc_4

 

usageDetails__c

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
  • Was this article helpful?