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

On Jul 11, 10:35 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Mike wrote:
> > 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 ?
>
> As Roy pointed out, intuition is a really bad guide. While I expect
> things have since changed drastically, I can say with great confidence
> that circa 1994, your intuition would have been wrong.- Hide quoted text -
>
> - Show quoted text -

Fari enough. Do you mind sharing any reasons why it would have been wrong? Received on Wed Jul 11 2007 - 16:52:17 CEST