The joiner transformation provides you the option to create joins in Informatica. The joins created using joiner transformation are similar to the joins in databases. The advantage of joiner transformation is that joins can be created for heterogeneous systems (different databases).
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 joinIn 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 joinIn detail outer join only matching rows are returned from the detail source, and all rows from the master source are returned.
- Full outer joinIn 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 joinIn 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.
DROP TABLE EMP_DEPTNAME;
CREATE TABLE EMP_DEPTNAME(
EMPNO NUMBER(4) NOT NULL,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
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
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.