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

    Lookup Vs Join

    8b68d45e-7931-4c06-9ed6-46fbefb8defd

      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.........?

       

       

      Regards,

      Subbu

        • 1. Re: Lookup Vs Join
          DecaXD

          Lookup is a definition of a table

           

          http://en.wikipedia.org/wiki/Lookup

           

          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
            _Phil

            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
              _Phil

              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.