Re: Unions and Grouping

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:
>
> 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
==================================================================