Re: difference between UNION operator and OR in where clause
From: Brian Selzer <brian_at_selzer-software.com>
Date: Tue, 10 Jul 2007 17:39:28 GMT
Message-ID: <kvPki.45835$5j1.21632_at_newssvr21.news.prodigy.net>
>> "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 -
Date: Tue, 10 Jul 2007 17:39:28 GMT
Message-ID: <kvPki.45835$5j1.21632_at_newssvr21.news.prodigy.net>
"Mike" <gongweigang_at_gmail.com> wrote in message news:1184086648.109524.92680_at_q75g2000hsh.googlegroups.com...
> 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 > ------------------------------------ >
A table this small masks the problem.
If A and B are sets, then |A x B| = |A| * |B|. If |A| = 1, then |A x B| = |B|
> 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 >
As Lennart pointed out, this one should be TRUE.
> > 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 - 19:39:28 CEST