Re: OT: seeking query help, where?

On Fri, 17 Jan 2003, Bruno Wolff III wrote:
> On Fri, Jan 17, 2003 at 08:57:14 -0500,
> Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu> wrote:
> >
> > that way you avoid duplicates across tables.
>
> The union operator already removes duplicates.

Right, which means everyone's UNION queries have too many DISTINCTs
in them:

SELECT email from table1
UNION
SELECT email from table2 ;

should do it.

Here's proof: note the duplicate 'foo' and 'bar':

test=# select * from table1;
email
-----------------
foo(at)example(dot)com
bar(at)example(dot)com
bar(at)example(dot)com
(3 rows)

test=# select * from table2;
email
------------------
quux(at)example(dot)com
foo(at)example(dot)com
(2 rows)

test=# select email

test=# select email from table1 union select email from table2;
email
------------------
bar(at)example(dot)com
foo(at)example(dot)com
quux(at)example(dot)com
(3 rows)

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Research Scientist phone: 713-348-6166
The Connexions Project http://cnx./rice.edu fax: 713-348-6182
Rice University MS-39
Houston, TX 77005