Re: difference between UNION operator and OR in where clause
Date: Tue, 10 Jul 2007 17:18:04 -0300
Message-ID: <4693e962$0$8835$9a566e8b_at_news.aliant.net>
Mike wrote:
> On Jul 10, 1:36 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote: >
>>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?
All of them.
>>TRUE OR X = ____
>>
>>X OR TRUE = ____
>
> both are TRUE.
Regardless of X. In your where clause, X is the restrict condition for books in one case and for journals in the other case.
>>>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?
> > > It will be true for |book.pubid|.
You have that backward. It be true for |journal.pubid|. All journals will match for every book and vice versa.
>>>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 ?- Hide quoted text -
>
> Since |PB| = 3 and |J|=2, |PB * J| = 6.
Exactly, which means every book appears twice. Let's take P X J and restrict it to rows with equal pubid (ie. replace CROSS PRODUCT with NATURAL JOIN) and call the result PJ.
PJ has 2 rows
PJ X B has 6 rows
The result you want has |PB| + |PJ| - |INTERSECT(B,J)| rows.
Your second solution will have
|PB X J| + |PJ X B| - |INTERSECT(PB X J,PJ X B)| rows.
Each book will be duplicated |J| times and each journal |B| times.
As you mentioned elsewhere, you can get rid of the duplicates using DISTINCT, but this only works when |B| > 0, |J| > 0 and the select list contains only columns from P. Those requirements severely limit the opportunity for generalizing the method. Received on Tue Jul 10 2007 - 22:18:04 CEST