Difference between mergejoin and lookup? which is better? why not merge join?

Hi
When you want to join 2 source tables on the bases of relation irrespective of their location you can use merge join, you can perform inner, outer join on this source tables as we do in T-SQL.

Look Up is used to find value(look for a value) in destination table with respect to source column values, i.e. check existence of source column value against destination column values.

Above solution also gives you more detail on the differences.

Let us know if you need more detail.

Regards
Mubin

Merge Join needs both inputs to be sorted, Sorting is highly expensive operation because it is Fully Blocking transformation which is not suggestible to use in package if you are loading millions of records.

Lookup transformation is non blocking transformation and sorting is not required. So, Lookup transformation is better than Merge Join by performance perspective.