Re: union all vs. left outer join

henrik.sorensen_at_balcab.ch writes:

> Warning long mail ...
>
> For several days I have tried to figure out why two sql with an union all
> is much faster than using a single sql with left outer join.
> But I cannot get my head around this one. It is quite possible I am
> overlooking something simple...
>
> I have tried the following sql on both DB2 and Oracle, and didn't get the
> expected result in any of the cases. The explain of the query looked
> remarkable alike, so maybe there is something wrong with my understanding
> of how the joins should work.
>
> When I look at the explain for this query:
>
> SELECT *
> from A
> left outer join B on A.id = B.aid
> left outer join C on A.id = C.aid
> inner join D      on
>    d.bid = B.id
> or d.bid = C.bid
> inner join E      on E.id = D.eid
> where E.m1 = 'A'

(Sorry, this is untested, but...) is the query plan and/or execution speed of the following alternative different?

SELECT *
from A
left outer join B on A.id = B.aid
left outer join C on A.id = C.aid
inner join D      on
   d.bid = isnull(B.id,c.bid)
inner join E      on E.id = D.eid
 where E.m1 = 'A'

-tapio Received on Tue Nov 08 2005 - 14:38:02 CET