Re: Unions and Grouping

On 12/15/06, Ragnar <gnari(at)hive(dot)is> wrote:
>
> On fös, 2006-12-15 at 16:17 -0600, Aaron Bono wrote:
> > On 12/15/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Aaron Bono" <postgresql(at)aranya(dot)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.com
http://codeelixir.com
==================================================================