Re: difference between UNION operator and OR in where clause

On Jul 10, 10:24 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> David Portas 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.
>
> Not really. He really needs to look into the concept of CROSS PRODUCT as
> it applies to the FROM clause.- Hide quoted text -
>
> - Show quoted text -

This sounds more related with the answer.

Let me take a guess:

if the publication table has 4 records, the book table has 3 records, and the journal table has 2 records, the "cross product" of the FROM clause will give 4 * 3 * 2 = 24 records. Then from them, the WHERE clause will pick the required records.
So if there are records in both book table or journal table, the solution 1 and solution 2 are both correct. But if there is no record in book table, the cross product in the solution 2 will be 0, becuase 4 * 0 * 2 = 0. Then the WHERE clause will have nothing to pick. Of course, this is not what we want. Therefore, the solution 2 is not correct.

Is my understanding right ? Received on Tue Jul 10 2007 - 16:35:37 CEST