From: Aaron Bono <postgresql@aranya.com>
To: Ragnar <gnari@hive.is>
Cc: Postgres SQL language list <pgsql-sql@postgresql.org>
Subject: Re: Unions and Grouping
Date: Fri, 15 Dec 2006 16:41:52 -0600
Message-ID: <bf05e51c0612151441r4eac90e4ke29615d5e2163ddd@mail.gmail.com> (raw)
In-Reply-To: <1166221809.6369.202.camel@localhost.localdomain>
References: <bf05e51c0612151315s59e6647etc2dd45e940d41a2a@mail.gmail.com>
<21818.1166218924@sss.pgh.pa.us>
<bf05e51c0612151417o1bdfa65bh5e869185469f46e1@mail.gmail.com>
<1166221809.6369.202.camel@localhost.localdomain>
On 12/15/06, Ragnar <gnari@hive.is> wrote:
>
> 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
>
>
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.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, gnari@hive.is
Subject: Re: Unions and Grouping
In-Reply-To: <bf05e51c0612151441r4eac90e4ke29615d5e2163ddd@mail.gmail.com>
* 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