Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3D2BA9FA373 for ; Fri, 15 Dec 2006 18:29:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 27818-02 for ; Fri, 15 Dec 2006 18:29:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx2.ipf.is (scania.ipf.is [85.197.192.147]) by postgresql.org (Postfix) with ESMTP id 8522B9FA31F for ; Fri, 15 Dec 2006 18:29:11 -0400 (AST) Received: (qmail 5947 invoked by uid 1010); 15 Dec 2006 22:26:06 +0000 Received: from 88.149.106.19 by scania (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1235. spamassassin: 3.1.1. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.0/4.5):. Processed in 4.392925 secs); 15 Dec 2006 22:26:06 -0000 X-Antivirus-IPF-Mail-From: gnari@hive.is via scania X-Antivirus-IPF: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.0/4.5):. Processed in 4.392925 secs Process 5937) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx2.ipf.is with SMTP; 15 Dec 2006 22:26:01 +0000 Subject: Re: Unions and Grouping From: Ragnar To: Aaron Bono Cc: Postgres SQL language list In-Reply-To: References: <21818.1166218924@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1 Date: Fri, 15 Dec 2006 22:30:09 +0000 Message-Id: <1166221809.6369.202.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/124 X-Sequence-Number: 27080 On fös, 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