Grokbase Groups Hive user June 2011
FAQ
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

Search Discussions

  • Igor Tatarinov at Jun 11, 2011 at 4:31 am
    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
  • Steven Wong at Jun 12, 2011 at 12:01 am
    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
  • Sagar naik at Jun 12, 2011 at 10:49 pm
    Thxs Igor

    Trick is to get where conditions in join condition

    Thxs again

    -Sagar
    On Sat, Jun 11, 2011 at 5:01 PM, Steven Wong wrote:
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedJun 11, '11 at 1:56a
activeJun 12, '11 at 10:49p
posts4
users3
websitehive.apache.org

People

Translate

site design / logo © 2021 Grokbase