I think you can also move the condition T2.field6='yyyyyyy' into the ON clause.
From: Igor Tatarinov [mailto:[email protected]] Sent: Friday, June 10, 2011 9:31 PM To: [email protected] 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 <[email protected]<mailto:[email protected]>> 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
