Master Outer Join in Informatica with Examples

Master Outer Join In Informatica Mapping Example

Master Outer Join in Informatica Summary

So, what is a master outer join in Informatica?

The master outer join in Informatica is used within a Joiner Transformation to help join two pipelines of data together. This functionality is very similar to an OUTER JOIN within SQL when needing to join two tables together.

Within a Joiner Transformation’s ports tabs, M column, we must designate the two different data pipelines as either the master or detail. Consider below example of two data sources having CustomerID as the common criterion to bring data together. This field, CustomerID, can be used to merge the data from two sources into a single record.

Table A

OrderID CustomerID Order Date
1000 C1 01/01/2010
1001 C2 01/01/2010
1002 C3 01/01/2010

Table B

CustomerID Customer Name
C1 David
C3 Tom
C4 Michael
Master Outer Join In Informatica

Master Outer Join In Informatica

Next we need to set our Join Type within the properties tab of the Joiner Transformation to “Master Outer Join”.

Master Outer Join In Informatica Join Type

Master Outer Join In Informatica Join Type

Finally, we need to set the ID columns that will relate our records to one another much like the ON statement in SQL. This is done within the condition tab of the Joiner Transformation in Informatica.

Master Outer Join In Informatica Condition Tab

Master Outer Join In Informatica Condition Tab

A Master Outer join in the Joiner transformation is the same as a LEFT OUTER JOIN in SQL (considering Detail pipeline as LEFT side), and so returns all rows from the Detail pipeline, and the matched rows from the Master pipeline. This is what the SQL might look like. Remember TABLE_B is our detail pipeline and TABLE_A is our master pipeline.

SELECT *
FROM TABLE_B
LEFT OUTER JOIN TABLE_A
ON TABLE_A.CustomerID = TABLE_B.CustomerID

Here’s a venn diagram and data results to help you better visualize the master outer join in Informatica.

Right-OuterJoin

OrderID CustomerID OrderDate CustomerName
1000 C1 01/01/2010 David
1002 C3 01/01/2010 Tom
NULL C4 NULL Michael

 

Master Outer Join in Informatica Conclusion

Remember the key to a master outer join in Informatica is the ability to relate one data pipeline to another. While sometimes SQL may be simpler to perform the same functionality, many times our data sources are not tables, but flat files. Or our tables might be in different databases. When thinking of the master outer join in Informatica, think of the LEFT OUTER JOIN in SQL where our Detail pipeline is the LEFT side. We retain all the records in the left side and attempt to relate records from our master pipeline. If our ID columns do not align, Informatica places NULL values in the master pipeline ports.

Well, I hope this post gives you a clear picture of the Master Outer Join in Informatica. Enjoy!

Aaron Gendle

Aaron Gendle

Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.

I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.

Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.

Find Aaron Gendle on LinkedIn -
Aaron Gendle

Leave a Reply

Your email address will not be published. Required fields are marked *