Re: difference between UNION operator and OR in where clause

On Jul 10, 9:59 am, David Portas
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> On 10 Jul, 14:39, Mike <gongweig..._at_gmail.com> wrote:
>
>
>
>
>
> > tables:
> > 1. publication(pubid, title)
> > 2. book(pubid, date)
> > 3. journal(pubid, date)
>
> > purpose: find all publication titles for books or journals.
>
> > 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 title
> > from publication, book, journal
> > where publication.pubid = book.pubid or publication.pubid =
> > journal.pubid
>
> > This example is taken from some lecture notes about sql. The notes
> > said the solution 2 was wrong.
>
> > Anyone knows why ?
>
> Let me pose two more questions for you. What are the keys for each
> table? What is the difference between ALL and DISTINCT?
>
> If you can answer those questions then you may be on the way to the
> answer you are looking for.
>
> --
> David Portas- Hide quoted text -
>
> - Show quoted text -

The keys for all the 3 tables are "pubid". "ALL" means to allow duplicated records, and "DISTINCT" will remove the duplicated ones.

But I still don't get it.

Could you give more hints ? Received on Tue Jul 10 2007 - 16:26:27 CEST