Full join in Talend
Introduction:
There is no shared idea or pre-defined solution to achieve Full join in Talend. Hence I am sharing my idea.
What need to be achieved using Full Join?
Consider the below RecordSet,
Table1
EmpId
|
EmpName
|
Salary
|
ID1
|
Emp1
|
15000
|
ID2
|
Emp2
|
16000
|
ID3
|
Emp3
|
17000
|
Table2
EmpId
|
Emp_AGE
|
EmP_Address
|
ID1
|
25
|
Chennai
|
ID2
|
35
|
Bangalore
|
ID4
|
30
|
Chennai
|
If you notice, Table1 does not contain data for ID4 and Table2 does not contain data for ID3.
The result-set that we need to get using Full Join is
EmpId
|
EmpName
|
EmpSalary
|
EmpAge
|
EmpAddress
|
ID1
|
Emp1
|
15000
|
25
|
Chennai
|
ID2
|
Emp2
|
16000
|
35
|
Bangalore
|
ID3
|
Emp3
|
17000
|
||
ID4
|
30
|
Chennai
|
How to achieve Full Join?
Follow below steps to achieve Full Join,
Step 0: Consider the below two tables (Temporary data creation)
Table1
Table2
Step 1: Store the record-set in Hash components
Fetch data from two tables and store each record-set into separate tHashOutput component as shown below,
Table1
Table1
Table2
Step 2: Map dataset in Table1 and Table2
Map data that is stored in tHashOutput_1 and tHashOutput_2 with Table1 data as Main table and Table2 data as LookUp table in tMap component.
Mapping in tMap,
Step 3: Store the output in tHashOutput
Store the output which contains data from Table1 as Main table and Table2 as LookUp table.
Step 4: Follow Step 2 and 3 with Table2 as Main table and Table1 as LookUp table
See below image for mapping,
Step 5: Merge two result-sets and get unique records
Now merge two result-set using tUnite component and get the unique records using tUniqueRow component.
Final output:
