[Hive-user] left outer join on same table

The condition T2.field6='yyyyyyy;' is tested after the outer join. As a
result you won't see any non-matching results.
You'll need a subquery to enforce that condition. Alternatively, adding ' OR
T2.field6 IS NULL' might work for you too. Just make sure to use parens
around the OR clause. But that's probably not what you want - then, use a
subquery.

I think you can also move the condition T2.field6='yyyyyyy' into the ON clause.


From: Igor Tatarinov
Sent: Friday, June 10, 2011 9:31 PM
To: user@hive.apache.org
Subject: Re: left outer join on same table

The condition T2.field6='yyyyyyy;' is tested after the outer join. As a result you won't see any non-matching results.
You'll need a subquery to enforce that condition. Alternatively, adding ' OR T2.field6 IS NULL' might work for you too. Just make sure to use parens around the OR clause. But that's probably not what you want - then, use a subquery.

On Fri, Jun 10, 2011 at 6:55 PM, sagar naik wrote:
Hi ,

I am trying to do join on same table.
I wanted to do a left outer join
However, the results are not like left outer join( with no null values)

select T1.field1, T1.field2, T1.field3
T2.field10
from table T1
left outer join table T2 on (T1.key=T2.key and
T1.partition='p1' and T2.partition='p2')
where
T1.field5='xxxxx' and T2.field6='yyyyyyy;'


I tried left/full all join result are like inner join.
What am I missing here ?
is it some mistake on my part or some optimization /flag to be turned off

Thanks for the help



-Sagar

Thxs Igor

Trick is to get where conditions in join condition

Thxs again

-Sagar