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

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. Received on Wed Jul 11 2007 - 16:35:40 CEST