Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id DE7529FA334 for ; Fri, 15 Dec 2006 18:41:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 43155-08 for ; Fri, 15 Dec 2006 18:41:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 6CCE89FA31F for ; Fri, 15 Dec 2006 18:41:54 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so941557ugf for ; Fri, 15 Dec 2006 14:41:53 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=tQ3AQo3h6twfjkoPAko4+OwTZXrAYxiAbXtkUiiOwG0UiWss0JGCfFD/gj1qP8Bw7HCGeFd8N9vYJwCBKnE2tIea37a5bRp0uj1lS8oGtiA/5zQfn1vfmOfM//1FrcPIfgdJZKlDaw2UW0s5NsDEXU5rMrabVcTyiUZYa51Hhbw= Received: by 10.78.171.13 with SMTP id t13mr547238hue.1166222512101; Fri, 15 Dec 2006 14:41:52 -0800 (PST) Received: by 10.78.131.12 with HTTP; Fri, 15 Dec 2006 14:41:52 -0800 (PST) Message-ID: Date: Fri, 15 Dec 2006 16:41:52 -0600 From: "Aaron Bono" To: Ragnar Subject: Re: Unions and Grouping Cc: "Postgres SQL language list" In-Reply-To: <1166221809.6369.202.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_164937_9149488.1166222512037" References: <21818.1166218924@sss.pgh.pa.us> <1166221809.6369.202.camel@localhost.localdomain> X-Google-Sender-Auth: c565062233aa44ed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/125 X-Sequence-Number: 27081 ------=_Part_164937_9149488.1166222512037 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 12/15/06, Ragnar wrote: > > On f=F6s, 2006-12-15 at 16:17 -0600, Aaron Bono wrote: > > On 12/15/06, Tom Lane wrote: > > "Aaron Bono" writes: > > > My question, what do the SQL Specifications say should > > happen on a Union? > > > Is it supposed to eliminate duplicates even WITHIN the > > individual queries > > > that are being unioned? > > > > Yes. SQL92 7.10 saith: > > [snip SQL92 standardese] > > > > > So if I don't want the duplicated WITHIN the two queries to be > > eliminated, I use UNION ALL? > > with UNION ALL, no duplicates will be discarded, either > "within" or "between" the queries. > > if I understand you, you are looking for having only queries > that are both in Q1 and Q2 removed, but not for example, > any duplicates in Q1 that are not in Q2. > > if this is what you want, then I think you may not have > thought it through. what about a row that is twice in > Q1 and three times in Q2? how many copies of this row > should be in the result? > > gnari > > What I want is two-fold: 1. I want to eliminate all duplicates which is exactly what UNION does - so I am using a straight UNION 2. I want to understand how UNION and UNION ALL work (which I do now - thanks) I didn't really want the duplicates between the two queries eliminated but not within. That is just what I had expected to see the first time I ran the query. As you pointed out, this really isn't desirable so I am glad it doesn't work this way. Thanks for all the input. --=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ------=_Part_164937_9149488.1166222512037 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 12/15/06, Ragnar <gnari@hive.is> wrote:
On f=F6s, 2006-12-15 at 16:17 -0600, Aaron Bono wrote:
> On 12/15/06,= Tom Lane <tgl@sss.pgh.pa.us>= ; wrote:
>         "Aaro= n Bono" < postgresql@aranya.com> writes:
>     =     > My question, what do the SQL Specifications say sho= uld
>         happen on a Uni= on?
>         > Is it supp= osed to eliminate duplicates even WITHIN the
>         individual queries=
>         > that are bein= g unioned?
>
>         = Yes.  SQL92 7.10 saith:
> [snip SQL92 standardese]

&= gt;
> So if I don't want the duplicated WITHIN the two queries to be
> eliminated, I use UNION ALL?

with UNION ALL, no duplicates = will be discarded, either
"within" or "between" the = queries.

if I understand you, you are looking for having only querie= s
that are both in Q1 and Q2 removed, but not for example,
any duplica= tes in Q1 that are not in Q2.

if this is what you want, then I think= you may not have
thought it through. what about a row that is twice in
Q1 and three times in Q2? how many copies of this row
should be in t= he result?

gnari


What I want is two-fo= ld:

1. I want to eliminate all duplicates which is exactly what UNIO= N does - so I am using a straight UNION
2. I want to understand how UNION and UNION ALL work (which I do now - = thanks)

I didn't really want the duplicates between the two queries = eliminated but not within.  That is just what I had expected to see th= e first time I ran the query.  As you pointed out, this really isn't d= esirable so I am glad it doesn't work this way.

Thanks for all the input.

--
=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
   Aaron Bono
 &n= bsp; Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D ------=_Part_164937_9149488.1166222512037--