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
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:
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
from table T1
left outer join table T2 on (T1.key=T2.key and
T1.partition='p1' and T2.partition='p2')
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