Re: difference between UNION operator and OR in where clause

On Jul 10, 12:36 pm, "Brian Selzer" <b..._at_selzer-software.com> wrote:
> "Mike" <gongweig..._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- Hide quoted text -
>
> - Show quoted text -

Use smaller tables:
publication:

pubid              title
1                    t-1
2                    t-2
3                    t-3
--------------------------------------

book:

pubid             date
1                   d-1
2                   d-2
------------------------------------

journal:

pubid             date
3                   d-3
------------------------------------

The cross product will be:

publication.pubid  publication.title      book.pubid
book.date       journal.pubic             journal.date
WHERE
        1                      t-1
1                 d-1
3                             d-3                        TRUE
        1                      t-1
2                 d-2
3                             d-3                        FALSE
        2                      t-2
1                 d-1
3                             d-3                        FALSE
        2                      t-2
2                 d-2
3                             d-3                        TRUE
        3                      t-3
1                 d-1
3                             d-3                        TRUE
        3                      t-3
2                 d-2
3                             d-3                        FALSE

Therefore, only 3 records are TRUE in the WHERE clause, ie.

publication.pubid  publication.title      book.pubid
book.date       journal.pubic             journal.date
WHERE
        1                      t-1
1                 d-1
3                             d-3                        TRUE
        2                      t-2
2                 d-2
3                             d-3                        TRUE
        3                      t-3
1                 d-1
3                             d-3                        TRUE

then solution 1 and solution 2 give the same result. Received on Tue Jul 10 2007 - 18:57:28 CEST