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, Right Join, and Outer 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

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?