Joiner transformation in Informatica and types of joins supported

Joiner transformation in Informatica and types of joins supported

Informatica joiner transformation are good if you want to join data coming from different databases. If you are reading from the same database it is better to join all the tables using the SQ qualifier instead of joiner transformation for better performance. In the ports tab of the joiner transformation you can set which is the master table and detail table. On the condition tab you can specify the join condition. In the properties tab of the joiner transformation you can specify the type of join.

The Informatica joiner transformation supports the following four types of joins:

• Normal (Inner Join)
• Master Outer (Detail left outer join Master)
• Detail Outer (Master left outer join detail)
• Full Outer (

Normal join: This is equivalent of an inner join and hence selects only the rows from the master and detail tables that match.

SELECT * FROM master_table, detail_table WHERE
master.id = details.id

Master outer join: Does a left outer join of the detail table with the master table hence discards all the unmatched rows from the master table and keeps all the rows from the detail table.


SELECT * FROM detail_table LEFT OUTER JOIN master_table ON
(master_table.id = detail_table.id)


Detail outer join: Does a left outer join of the master table with the detail table hence discards all the unmatched rows from the
details table and keeps all the rows from the master table.

SELECT * FROM master_table LEFT OUTER JOIN details ON
(master_table.id = detail_table.id)


Full outer join: is a full outer join that keeps all rows of data from both the master
and detail sources.

SELECT * FROM maser_table FULL OUTER JOIN details_table ON
(master_table.id = detail_table.id)