Re: difference between UNION operator and OR in where clause

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 10 Jul 2007 14:36:30 -0300
Message-ID: <4693c383$0$8853$9a566e8b_at_news.aliant.net>


Mike wrote:

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

I disagree. No journals will match "publication.pubid = journal.pubid", but that is only part of the WHERE clause. More than zero journals will match the where clause. How many?

TRUE OR X = ____ X OR TRUE = ____

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

Yes, it will be true. It will be true for how many journals?

> Similiar answer to the other part of your question.
> 
> Please correct me if I am wrong.

You are focusing on single records rather than the CROSS PRODUCT, but the FROM clause yields a CROSS PRODUCT.

If you have 5 publications P, 3 books B and 2 journals J:

P has 5 rows
B has 3 rows
J has 2 rows
P X B has 15 rows
P X J has 10 rows
B X J has 6 rows
P X B X J has 30 rows

Let's take P X B and restrict it to rows with equal pubid (ie. replace CROSS PRODUCT with NATURAL JOIN) and call the result PB.

PB has 3 rows
PB X J has ____ rows ? Received on Tue Jul 10 2007 - 19:36:30 CEST

Original text of this message