[SQL] "union" vs. left join

2009-03-03 Thread Emi Lu 

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





--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] "union" vs. left join

2009-03-03 Thread ries van Twisk 


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




--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql