Join output problem

[ady]

I am joining 2 tables with a "left inner join" , The key combination I am using is promo_id and offer_Id.

Table A(Left) has 117500 rows, Table B(Right) has 1490 rows. But I get 274470 rows as output ?. I really dont understand?..... can anyone suggest what seems to be the problem here ?

Thnx

[samsuf2002]

Try to sort the data before you do join

[ray.wurlod]

There's no such thing as a left inner join. Are you using a left outer join, or an inner join?

An inner join will return a number of rows no greater than the smaller of the row counts of the two inputs. A left outer join will return a number of rows no greater than the number of rows in the left input.

Only a full outer join can return more rows than either of the input Data Sets (but see next paragraph).

The Join stage requires its inputs to be identically partitioned and sorted (based on the join key(s)). Without this condition being satisfied, you are likely to get spurious additional matches.

[us1aslam1us]

As noted there is nothing like 'left inner join'. you might be trying 'left outer join'. you need to sort the data on the 'keys' and also check how the data is partitioned.

[ady]

I was using a "Left Outer Join"

I am using the inbuilt sort (Ascending, Case Sensitive) and the Partion type is "Hash"

[kumar_s]

What is your job design? Wrong partition will screw up the number of records that been passed. Are the both stream well partitioned on the key (as you mentioned 'Hash')?

[ray.wurlod]

Are both inputs identically hash partitioned and sorted (on the join keys)?

[ady]

The data I have dosent make sense .... I need to recheck it !

Thanx for the help guys !