Re: difference between UNION operator and OR in where clause

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 10 Jul 2007 12:16:19 -0300
Message-ID: <4693a2a9$0$8832$9a566e8b@news.aliant.net>

Mike wrote:

> 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 ?

You have identified half of the problem. In the 24 records above, for each book id, how many records will have an equal publication id? For each journal id, how many records will have an equal book id? Received on Tue Jul 10 2007 - 10:16:19 CDT