Re: difference between UNION operator and OR in where clause
Date: Tue, 10 Jul 2007 16:36:10 GMT
Message-ID: <_zOki.45831$5j1.41847_at_newssvr21.news.prodigy.net>
"Mike" <gongweigang_at_gmail.com> wrote in message
news:1184083980.683470.307900_at_g4g2000hsf.googlegroups.com...
> 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. > PUB BOOK JOURNAL WHERE? 1 1 6 TRUE 1 1 7 TRUE 1 1 8 TRUE 1 1 9 TRUE 1 1 10 TRUE 2 2 6 TRUE 2 2 7 TRUE 2 2 8 TRUE 2 2 9 TRUE 2 2 10 TRUE 6 1 6 TRUE 6 2 6 TRUE... many many more combinations Received on Tue Jul 10 2007 - 18:36:10 CEST