Re: performance difference between UNION operator and OR in where clause Options

From: Roy Hann <specially_at_processed.almost.meat>
Date: Wed, 11 Jul 2007 15:20:17 +0100
Message-ID: <>

"Mike" <> wrote in message
> 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 ?

In general there is no way to know. Any reasonable SQL DBMS should be free to do as it pleases as long as the answer comes out right. Intuition is a really bad guide. When it comes to SQL, intuitions are very frequently wrong. (For instance, are you confident about when you will break-even doing a table scan with readahead, or how parallel execution will play out?)

The obvious corollories are: (1) if you are using an SQL DBMS which requires you to care/know about which of several equivalent formulations is "optimal" you should start looking for a better one, and (2) if your DBMS is too stupid to recognize equivalent formulations today so that *you* have to choose the best one, there is no reason to suppose a future release won't change so that another formulation is more optimal.

Roy Received on Wed Jul 11 2007 - 16:20:17 CEST

Original text of this message