Master Outer Join in Informatica

Four types of joins in Informatica

1) Normal Join – Normal join discards all the rows of data from the master and detail source that do not match, based on the condition.

2) Master Join – Master outer join discards all the unmatched rows from the master source and keeps all the rows from the detail source and the matching rows from the master source.

3) Detail Join – Detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.

4) Full Outer Join – Full outer join keeps all rows of data from both the master and detail sources.

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

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.

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.