Difference between a lookup and join

babbu9 · Post by **babbu9** » Wed Jan 25, 2006 9:37 am

Hi
I would like to know the difference between a lookup and join. This not concerned with the stages in datastage but the definition itself. When will a process be called a lookup and when a join.

Thanks
Bob

kcbland · Post by **kcbland** » Wed Jan 25, 2006 12:14 pm

Joins have specific meaning to relational database processing. There are types: inner, outer, left, right, full, etc. Within some of those types there's a concept of a driving (driver) table, whereby that driver table ultimately decides how many target rows will end up in the result set (minus any filtering criteria).

A lookup is terminology for any table referenced by a driver table. So, a driver table again sets the number of rows in the result set (minus any filtering criteria).

Since an inner join is the intersection of multiple tables, none of the tables qualify as a "lookup". Any table that is referenced via an outer join are considered "lookups", since they are referenced by the driver table (which could be a virtual result of several inner joined tables, but a driver nonetheless).

babbu9 · Post by **babbu9** » Wed Jan 25, 2006 2:45 pm

That was something I never knew.Thank you for the information.