Join and Lookup Stage

If you have a huge volume of data to be referenced, which stage will you use? Join or Lookup stage? Why

Questions by datastage8

Showing Answers 1 - 7 of 7 Answers


  • Feb 27th, 2008

if the reference datasets are big enough , use a join. A join does a high-speed sort on the driving and reference datasets.Once the sort is over the join processing is very fast


  • Apr 18th, 2008

join has no reject links. wheras lookup has one reject link
join has 4 types inner outer,left outer,rght outer. l0ok up gives inner and left outer
lokk up takes any kind of colmn names and perform join operations.join needs same coloumn names among all sources.
if refernce table has little data prefer to lookup more means prefer join..

  Was this answer useful?  Yes


for huge size of data we must use join.

Lookup stage requires all the data to be loaded in memory 1st &  then only it can start  matching process with inputs.

Hence It is advisable  to use join stage then Lookup stage.

If you want to capture Reject records  then you can use transformer (or filter) after Join Stage and capture the null values.
Or Instead of join  stage use  Merge Stage to capture the reject records.

Hope this helps


  • Jul 24th, 2008

use the lookup stage when the reference table size is less relative to physical ram memory
if the reference table size is too big then data stage recommends to use the join stage to speed up the join process

  Was this answer useful?  Yes


  • Aug 23rd, 2011

If you have huge data go for join stage instead of lookup stage.Because look up stage takes buffer space while looking the data.

  Was this answer useful?  Yes


  • Aug 24th, 2011

Look up stage

  Was this answer useful?  Yes

Gopi N

  • Jul 11th, 2012

If we have a Huge data at reference defiantly we should go to Join stage because Look up takes much time to process hence we will use entire partition but in Join stage we will give the sorted data and it will simplify better than Look-up

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions