Request explanation on differences in transformations in DI Studio

[Sharad_Pujari]

what is difference between look up transformation and join transofrmation.???

[RW9]

The manual is generally the first place for information:
http://support.sas.com/documentation/cdl/en/etlug/65016/HTML/default/viewer.htm#n0geesvp950nken1tetx...

[Kurt_Bremser]

In a lookup transformation, the lookup tables are loaded into memory at the start, and the main table is processed sequentially.

In a join transformation, all tables need to be sorted (often done implicitly by a SQL step) and then merged along the by variables.

Joins should be done when the contributing tables are quite large, or there is a m:n relationship.

Lookups are good for smaller lookup tables (small just meaning they should fit into memory) and 1:n relationships (lookup to main); one can do several lookups in one sequential step.

[LinusH]

There's a scenario where join should be preferred, when the master and lookup tabke(s) reside on an external data source. SQL can relatively easy be pushed down to the RDBMS.