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.
Next we need to set our Join Type within the properties tab of the Joiner Transformation to “Master Outer Join”.
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.
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.
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.
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!
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 -
Latest posts by Aaron Gendle (see all)
- 5 Reasons to Quit Your Job Today - June 20, 2017
- The 2017, Top 50
Data Driven CEO’s - March 1, 2017
- 25+ Advanced Informatica Interview Questions and Answers - February 7, 2015