Re: difference between UNION operator and OR in where clause

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 10 Jul 2007 12:18:10 -0300
Message-ID: <4693a317$0$8832$9a566e8b_at_news.aliant.net>


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. Received on Tue Jul 10 2007 - 17:18:10 CEST

Original text of this message