Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 258119FA304
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 18:17:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 05789-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 18:17:36 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.224])
	by postgresql.org (Postfix) with ESMTP id 740929F9F08
	for <pgsql-sql@postgresql.org>; Fri, 15 Dec 2006 18:17:39 -0400 (AST)
Received: by wr-out-0506.google.com with SMTP id i23so445165wra
	for <pgsql-sql@postgresql.org>; Fri, 15 Dec 2006 14:17:38 -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=SufbIP0AvX4EEuIIMKSDPQ9yg79o8FVqJwET72aW24xyUUOAysVX44He6jc0/T9himkNlt1OKQqz2PwExiKM6jNxUK4kgDmdWy5OPwsiEjwWEHrTnmUfQMVtxjApTcv7B7hokNyFV2wYXPma5WLRgeM1jRSJQAm/kpJQFSYMNps=
Received: by 10.78.176.20 with SMTP id y20mr170445hue.1166221040597;
	Fri, 15 Dec 2006 14:17:20 -0800 (PST)
Received: by 10.78.131.12 with HTTP; Fri, 15 Dec 2006 14:17:20 -0800 (PST)
Message-ID: <bf05e51c0612151417o1bdfa65bh5e869185469f46e1@mail.gmail.com>
Date: Fri, 15 Dec 2006 16:17:20 -0600
From: "Aaron Bono" <postgresql@aranya.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Unions and Grouping
Cc: "Postgres SQL language list" <pgsql-sql@postgresql.org>
In-Reply-To: <21818.1166218924@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_164723_8120628.1166221040528"
References: <bf05e51c0612151315s59e6647etc2dd45e940d41a2a@mail.gmail.com>
	<21818.1166218924@sss.pgh.pa.us>
X-Google-Sender-Auth: 8698c12c24998873
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/123
X-Sequence-Number: 27079

------=_Part_164723_8120628.1166221040528
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 12/15/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Aaron Bono" <postgresql@aranya.com> 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:
>
>             b) If a set operator is specified, then the result of applying
>               the set operator is a table containing the following rows:
>
>               i) Let R be a row that is a duplicate of some row in T1 or
> of
>                  some row in T2 or both. Let m be the number of duplicates
>                  of R in T1 and let n be the number of duplicates of R in
>                  T2, where m >= 0 and n >= 0.
>
>              ii) If ALL is not specified, then
>
>                  Case:
>
>                  1) If UNION is specified, then
>
>                    Case:
>
>                    A) If m > 0 or n > 0, then T contains exactly one
> dupli-
>                       cate of R.
>
>                    B) Otherwise, T contains no duplicate of R.
>
>
>                         regards, tom lane
>

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

Thanks!


-- 
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

------=_Part_164723_8120628.1166221040528
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 12/15/06, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
&quot;Aaron Bono&quot; &lt;<a href="mailto:postgresql@aranya.com">postgresql@aranya.com</a>&gt; writes:<br>&gt; My question, what do the SQL Specifications say should happen on a Union?<br>&gt; Is it supposed to eliminate duplicates even WITHIN the individual queries
<br>&gt; that are being unioned?<br><br>Yes.&nbsp;&nbsp;SQL92 7.10 saith:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;b) If a set operator is specified, then the result of applying<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the set operator is a table containing the following rows:
<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i) Let R be a row that is a duplicate of some row in T1 or of<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; some row in T2 or both. Let m be the number of duplicates<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; of R in T1 and let n be the number of duplicates of R in
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T2, where m &gt;= 0 and n &gt;= 0.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ii) If ALL is not specified, then<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1) If UNION is specified, then<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case:
<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A) If m &gt; 0 or n &gt; 0, then T contains exactly one dupli-<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cate of R.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B) Otherwise, T contains no duplicate of R.<br><br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;regards, tom lane
<br></blockquote></div><br>So if I don't want the duplicated WITHIN the two queries to be eliminated, I use UNION ALL?<br>
<br>
Thanks!<br><br clear="all"><br>-- <br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com
</a><br>&nbsp;&nbsp; <a href="http://codeelixir.com">http://codeelixir.com</a><br>==================================================================

------=_Part_164723_8120628.1166221040528--