Re: difference between UNION operator and OR in where clause

On Jul 10, 11:18 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Mike wrote:
> > On Jul 10, 10:40 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> >>Mike wrote:
>
> >>>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 ?
>
> >>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
>
> >>Consider as well the nullary cases.
>
> > This is the case which fails the solution 2, I think.
>
> It is one case and one mechanism by which solution 2 fails. Go back
> where I left the blanks and fill them in. Then re-read the paragraph
> they appear in.- Hide quoted text -
>
> - Show quoted text -

My answer for your previous question.

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

If a book id matches a publication id, 0 journal will match the WHERE clause.

but the where clause : "where publication.pubid = book.pubid or publication.pubid = journal.pubid"
will be TRUE.

Similiar answer to the other part of your question.

Please correct me if I am wrong. Received on Tue Jul 10 2007 - 18:13:00 CEST