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

From: Nis Jørgensen <>
Date: Wed, 11 Jul 2007 18:42:08 +0200
Message-ID: <46950861$0$90267$>

Mike skrev:
> 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 ?

The fact that solution 2 needs a precondition AND a DISTINCT to give the correct result should tell you that solution 1 is the "better" way to do it.

My suggestions:

  • Formulate your query as closely to your thoughts as possible
  • Don't try to outsmart the optimizer
  • Don't use unconstrained cross joins unless you really have to. Try to always use ANSI joins - that way, the cross joins will stand out.
  • If you have to use DISTINCT to get the correct answer, you are probably doing something


PS: The two formulations are not equivalent even given the precondition, since solution 1 may include duplicate titles if "publication" does. Received on Wed Jul 11 2007 - 18:42:08 CEST

Original text of this message