Обсуждение: "union" vs. left join

Good morning,

Would it be possible to know that whether "union" is quicker than "left 
join" in a general sense?

Queries:
========================
(1) union
select a.*, b.value
from      a
left join b   union
select a.*, c.value
from      a
left join c


(2) left join
select distinct a.*,  case when ... then     b.value  else     c.value
from      a
left join b
left join c


Will (1) be more efficient than (2) or vice versa? Or it really depends 
on data


Thanks a lot!
Lu Ying

On Mar 3, 2009, at 3:26 PM, Emi Lu wrote:

> Good morning,
>
> Would it be possible to know that whether "union" is quicker than  
> "left join" in a general sense?
>
> Queries:
> ========================
> (1) union
> select a.*, b.value
> from      a
> left join b
>   union
> select a.*, c.value
> from      a
> left join c
>
>
> (2) left join
> select distinct a.*,
>  case when ... then
>     b.value
>  else
>     c.value
> from      a
> left join b
> left join c
>
>
> Will (1) be more efficient than (2) or vice versa? Or it really  
> depends on data
>
>
> Thanks a lot!
> Lu Ying
>



I would say try it in your situation...
I never did any real worl testing, but usually I grab a left join  
before anything else.
I would suspect that the planner knows a bit better how to optimize  
the left join version.

Ries