lkp and join

[vsi]

Whats the differnce between a lkp and join

what does a lkp do...is it a inner join or right outer join or left outer join


any help will be appreciated

what does it mean COndition Not met and Lookup Failure


Thanks

[kumar_s]

Lookup does the outer join based on your input stream. The other conditions are to state how to the lookup stage can react with the records when it doesn't find a match in the lookup stream.
Do a search, you will find some related post..

[ray.wurlod]

Lookup stage supports conditional lookup. That is, some condition that has to be met before the lookup is even attempted. Therefore there are separate rules available for handling the "condition not met" situation, where the lookup was not even attempted, and the "lookup failed" situation, where the lookup was attempted but the key value(s) not found.

Whether this is an inner or left outer join depends on your lookup failed rule. If it's "Continue" then the Lookup stage is performing a left outer join (and any output column derived from the reference link needs to be nullable). If it's "Drop" or "Reject", the you've got the result of an inner join going onto the Lookup stage output link. And if it's "Fail", and a lookup fails, then the job aborts and you don't have any kind of join.

[kartheek.M]

[quote="vsi"]Whats the differnce between a lkp and join

The main difference between lkp and join is the performance issue.when the size of reference file is more than the primary file u can go for join as it allocates more memory than lkp.

[sudeepmantri]

Hi, a lookup is basicaaly an Inner join. However the major difference which I can think of is the memory issue. What a lookup(Normal, not sparse) does is, it executes the query only once in the reference table and brings the whole bulk of data to the main memory and then performs the inner join on them. On the other hand Join queries the database each time and does not bring anything to the main memory. Though you might feel that Joini is a bit slow but thing is when there is huge number of data available as a result of querying the reference link, then using a lookup the job may crash due to memory constraint, which is not a case in Join. We prefer lookup when the ratio between source link and reference link is 100:1 or a very complex query is getting executed at the reference side.

Thanks n Regards

Sudeep

[kumar_s]

Hi Sudeep,

If you check the previous post, you can find that the Lookup does both inner and outer join by it self.

[vsi]

Thanks Guys