Difference between Joiner and Union Transformation

Question

What is the difference between Joiner and Union Transformation? Also, Should we use Router instead of Joiner, to increase performance when there are multiple sources?

Answer 1

Joiner

• Using joiner we can remove duplicate rows
• Joiner can be Normal,Right Outer,Left Outer,Full Outer Join
• In Joiner we have one input group and one output group
• Joiner implemented by using Joiner Transformation in Informatica.
• Joiner Transformation combines data record horizontally based on a join condition

Union

• Union will not remove duplicate rows
• Union is equivalent to UNION ALL in SQL
• In Union we have multiple input groups and one output group.
• Union implemented by using Union Transformation in Informatica
• Union Transformation combines data record vertically from multiple sources
• Union also supports heterogeneous(different sources)

Now, Router transformation is an active and connected transformation. It is similar to the filter transformation used to test a condition and filter the data. In a filter transformation, you can specify only one condition and drops the rows that do not satisfy the condition. Where as in a router transformation, you can specify more than one condition and provides the ability for route the data that meet the test condition. Use router transformation if you need to test the same input data on multiple conditions.

So, when the data is coming from muliple sources you can use Router to route values accordingly. It will increase your performance and save time too.

Comments

remove duplicate rows using joiner how

Answer 2

Most of the points covered by Shweta, few more are -
Union
- Can join two structures without common port.
- Structure should be same.

Joiner
- Can join any two heterogeneous sources, but common port is necessary.
- Structure doesn't matter

Comments

Joiner Transformation in Informatica , is a connected and active transformation  which let you join data from two heterogeneous source (same source system or different source system).   
Example : Joining from EMP and DEPT table (Based on DEPT_ID)
In Joiner Transformation tor two sources to be joined there must be at least a common column between those two with same data types based on which it can be joined


Union Transformation in Informatica , is a connected and active transformation  which let you to merge data from multiple pipelines or pipeline branches into one pipeline branch
Example : Combining Data from Multiple Dept table into one table
In Union all the columns of the multiple sources must have similar data types and Number of columns of being merged should also be same

check http://tutorialcorner.com/2015/12/difference-between-joiner-and-union-transformation/ for more details