Join vs Merge vs Lookup - Datastage - Data Warehousing Data Warehousing

Reading Time: < 1 minute

Join	Merge	Lookup
We can perform four types of Joins in Join Stage. Inner Join Left Outer Join Right Outer Join Full Outer Join	We can perform only two types of Joins in Merge Stage. Inner Join Left Outer Join	We can perform only two types of Joins in Look Stage. Inner Join And Left Outer Join
There will be a N-Inputs ( In the case of Left, Inner, Right Outer Joins) There will be a 2 Inputs ( In the case of Full Outer Join)	There will be a N-Inputs	There will be a N-Inputs ( In the Case of Normal Stage) 2 Inputs (In the Case of Sparse Look Up )
One Output Link	One Output Link	One Output Link
No Reject Links	N-1 Reject Links	One Reject Link
Light Memory Usage	Light Memory Usage	Heavy Memory usage
Key Column Names should be Same. That is Primary record should be same with Secondary Records.	Key Column Names should be Same. That is Primary record should be same with Secondary Records.	Key column names Optional. It should be same in the case of Sparse Look Up
Primary and secondary records should be in sorted order before this stage	Primary and secondary records should be in sorted order before this stage	In Look Up stage it is Optional. That is all the primary and secondary records no need to be sorted.