From: Ragnar <gnari@hive.is>
To: Aaron Bono <postgresql@aranya.com>
Cc: Postgres SQL language list <pgsql-sql@postgresql.org>
Subject: Re: Unions and Grouping
Date: Fri, 15 Dec 2006 22:30:09 +0000
Message-ID: <1166221809.6369.202.camel@localhost.localdomain> (raw)
In-Reply-To: <bf05e51c0612151417o1bdfa65bh5e869185469f46e1@mail.gmail.com>
References: <bf05e51c0612151315s59e6647etc2dd45e940d41a2a@mail.gmail.com>
<21818.1166218924@sss.pgh.pa.us>
<bf05e51c0612151417o1bdfa65bh5e869185469f46e1@mail.gmail.com>
On fös, 2006-12-15 at 16:17 -0600, Aaron Bono wrote:
> 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:
> [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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@hive.is, postgresql@aranya.com
Subject: Re: Unions and Grouping
In-Reply-To: <1166221809.6369.202.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox