If you are familiar with T-SQL you may wonder why this transform exists when we can query source data joined in the T-SQL? Remember that in an ETL we may have source data from different types of sources such as flat files or XML. This transformation provides all types of joins such as Inner, Outer, and Left join.
There are differences between these three types of joins, which are explored in this recipe. In this recipe we have two data sources, from an Excel file and a Flat File, and we want to combine them in rows (join them) with the Merge Join Transform. We need to fetch all records that exist in both files or exist in one of the files but not in the other one.