Join vs Merge vs Lookup – Datastage

Reading Time: < 1 minute
JoinMergeLookup
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-InputsThere will be a N-Inputs ( In the Case of Normal Stage)
2 Inputs (In the Case of Sparse Look Up ) 
One Output LinkOne Output LinkOne Output Link
No Reject LinksN-1 Reject LinksOne Reject Link
Light Memory UsageLight Memory UsageHeavy 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 stagePrimary and secondary records should be in sorted order before this stageIn Look Up stage it is Optional. That is all the primary and secondary records no need to be sorted.