3 Replies Latest reply on Jul 17, 2013 3:39 PM by _Phil

    Lookup Vs Join


      Hi all,


      We have Lookup and Join Transformations. When we are using lookup, It takes left outer join.

      when we are using join transformations we can use join types like equi,non-equi,self and outer joins.


      Here I want to know the diffrence between lookup and join transformations.........?





        • 1. Re: Lookup Vs Join

          Lookup is a definition of a table




          To join with a lookup you have to make an outer join. A join transformation is used when you have to match some data (not only a lookup one)


          I hope it'll help

          • 2. Re: Lookup Vs Join

            By definition, a lookup must return a value for each record in the data set.  An outer join is used so that if the lookup fails, at the very least a NULL is returned.  (If an inner join was to be used, the record from the main data set for which the lookup failed would be excluded from the query result which isn't what we want.)


            On the other hand, a join transformation can be used to do the same thing as a lookup, by selecting the left-outer join.  However, it can also be used to filter (with an inner join) or expand (with a right or cross join) the data set, as well to joining records together from multiple tables.

            • 3. Re: Lookup Vs Join

              I should add that the lookup also gives you the option to execute as a sub-query within the SELECT statement which can be very useful.