Re: difference between UNION operator and OR in where clause

From: Mike <gongweigang_at_gmail.com>
Date: Tue, 10 Jul 2007 07:48:29 -0700

> Mike wrote:
> > On Jul 10, 9:59 am, David Portas
>
> >>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 ?
>
> In the second case, if a book id matches a publication id, how many
> journals will match the WHERE clause? Remember TRUE OR FALSE = ____
> Similarly if a journal id matches the a publication id, how many books
> will match the WHERE clause? Remember FALSE OR TRUE = ____
>
> I will let you fill in the blanks.
>
> Consider as well the nullary cases. What happens to the result of the
> FROM clause if there are no books at all or if there are no journals at all?- Hide quoted text -
>
> - Show quoted text -

In the second case, if a book id matches a publication id, how many journals will match the WHERE clause?

0

Similarly if a journal id matches the a publication id, how many books will match the WHERE clause?

0

This is the case which fails the solution 2, I think. Received on Tue Jul 10 2007 - 16:48:29 CEST

Original text of this message