Diff Between Lookup, Join And Merge

[reddysrc]

Hi Experts,

I am still confusing the difference between Lookup, Join and Merge Stage .

in which scenario which Stage do we need to use.


thx
Reddysrc

[ray.wurlod]

Lookup stage is for small reference data sets where exact or range lookup capability is required. Duplicates of the lookup key are permitted in the stream input - each will be looked up separately. Lookup stage is also indicated if you need to access huge data sets directly from the database tables in which they are stored (sparse lookup). Failed lookups can cause the stream input row to be directed to a reject link.

Join stage is when you want to perform a regular SQL-style join. There is no concept of a reject link; output is the result of the join. For outer joins, some of the columns need to be declared to be nullable.

Merge stage implements a master/update model. Rows from the update links are consumed, so duplicates from the master input will only be matched once. Separate reject links can be created for each separate update link. Properties of the stage include how you want to handle unmatched masters.