Joiner transformation in Informatica

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

1. 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.
2. 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.
3. 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.
4. 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.

DROP TABLE EMP_DEPTNAME;

CREATE TABLE EMP_DEPTNAME(

 EMPNO NUMBER(4) NOT NULL,

 ENAME VARCHAR2(10),

 JOB VARCHAR2(9),

 MGR NUMBER(4),

 HIREDATE DATE,

 SAL NUMBER(7, 2),

 COMM NUMBER(7, 2),

 DEPTNO NUMBER(2),

 DEPTNAME VARCHAR2(30)

) ;

PURGE RECYCLEBIN

/

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.

1. Select joiner transformation
2. Enter transformation name "jnr_emp_dept"
3. 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

1. Select condition tab
2. Click on add new condition icon
3. Select deptno in master and detail columns list

Step 6 - Then in the same window

1. Select properties tab
2. Select normal Join as join type
3. 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

1. Select ports tab
2. Select any column of a particular source which you want to make a master
3. 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.