Home -> Usenet -> comp.databases.theory -> performance difference between UNION operator and OR in where clause Options

performance difference between UNION operator and OR in where clause Options

From: Mike <gongweigang_at_gmail.com>
Date: Wed, 11 Jul 2007 06:21:45 -0700
Message-ID: <1184160105.785774.35920_at_w3g2000hsg.googlegroups.com> 





tables:

1. publication(pubid, title)
2. book(pubid, date)
3. journal(pubid, date)





purpose: find all publication titles for books or journals.



precondition: both book and journal tables are not empty. (if either
is empty, the following solution 2 will be wrong. See my old post
"difference between UNION operator and OR in where clause Options" for
details).



Solution 1: use set "union"



select title


from publication, ( (select pubid from book) UNION (select pubid from
journal)) bj


where publication.pubid = bj.pubid



solution 2: use "or" operator in the where clause



select DISTINCE title


from publication, book, journal


where publication.pubid = book.pubid or publication.pubid =
journal.pubid




Both solutions are correct under the precondition.



My question is which one should use for better performance ?



My intuitive guess will be solution 2.



Any comments ?



Thanks ahead.




Thanks ahead.
Received on Wed Jul 11 2007 - 15:21:45 CEST












Original text of this message