In joiner transformation, there are two sources which we are going to use it for joins. These two sources are called
Master Source
Detail Source
In the properties of joiner transformation, you can select which data source can be Master and which source can be detail source. During execution, the master source is cached into the memory for joining purpose. So it is recommended to select the source with less number of records as the master source. The following joins can be created using joiner transformation
Master outer join In Master outer join, all records from the Detail source are returned by the join and only matching rows from the master source are returned.
Detail outer join In detail outer join only matching rows are returned from the detail source, and all rows from the master source are returned.
Full outer join In full outer join, all records from both the sources are returned. Master outer and Detail outer joins are equivalent to left outer joins in SQL.
Normal join In normal join only matching rows are returned from both the sources.
In this example, we will join emp and dept tables using joiner transformation Step 1 – Create a new target table EMP_DEPTNAME in the database using the below script and import the table in Informatica targets. Download the above emp_deptname.sql File Step 2 – Create a new mapping and import source tables “EMP” and “DEPT” and target table which we created in the previous step
Step 3 – From the transformation menu, select create option.
Select joiner transformation
Enter transformation name “jnr_emp_dept”
Select create option
Step 4 – Drag and drop all the columns from both the source qualifiers to the joiner transformation
Step 5 – Double click on the joiner transformation, then in the edit transformation window
Select condition tab
Click on add new condition icon
Select deptno in master and detail columns list
Step 6 – Then in the same window
Select properties tab
Select normal Join as join type
Select OK Button
For performance optimization, we assign the master source to the source table pipeline which is having less no of records. To perform this task – Step 7 –Double click on the joiner transformation to open edit properties window, and then
Select ports tab
Select any column of a particular source which you want to make a master
Select OK
Step 8 – Link the relevant columns from joiner transformation to target table
Now save the mapping and execute it after creating session and workflow for it. The join will be created using Informatica joiner, and relevant details will be fetched from both the tables.
