Difference between Joiner and Union Transformation

Question

I'm new to Informatica....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

1. Using joiner we can remove duplicate rows

2. Joiner can be Normal,Right Outer,Left Outer,Full Outer Join

3. In Joiner we have one input group and one output group

4. Joiner implemented by using Joiner Transformation in Informatica.

5. Joiner Transformation combines data record horizontally based on a join condition

6. Joiner Transformation combines data record horizontally based on a join condition

Union

1. Union will not remove duplicate rows

2. Union is equlivalent to UNION ALL in SQL

3. In Union we have multiple input groups and one output group.

4. Union implemented by using Union Transformation in Informatica

5. Union Transformation combines data record vertically from multiple sources

6. Union also supports hetregenous(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.