Is this proper UNION behavior??

From: "Edward Q(dot) Bridges" <ed(dot)bridges(at)buzznik(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Is this proper UNION behavior??
Date: 2000-11-14 01:27:48
Message-ID: 200011140132.eAE1Wes05681@mail.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

According to my copy of SQL For Smarties by Joe Celko (2nd ed, p. 411):

> The UNION removes all duplicate rows from the results and does
> not care from which table the duplicate rows came. We could
> use this feature to write a query to remove duplicates from a
> table:
> (TABLE tableA)
> UNION
> (TABLE tableA);
>
> But this is the same as
> SELECT DISTINCT * FROM tableA;

however, per the below example, the union and the select distinct
are not the same in postgres 7.0.2. is joe missing somehting here?
or am i?

thanks
--e--

CREATE TABLE "has_some_dupes" (
"a_col" character(3),
"b_col" character(3),
"c_col" character(3)
);
COPY "has_some_dupes" FROM stdin;
abc def ghi
abc def ghi
abc def ghi
jkl mno pqr
jkl mno pqr
jkl mno pqr
stu vwx yz
stu vwx yz
stu vwx yz
\.

ebridges=> (select * from has_some_dupes)
ebridges-> UNION
ebridges-> (select * from has_some_dupes);
a_col | b_col | c_col
-------+-------+-------
abc | def | ghi
abc | def | ghi
abc | def | ghi
jkl | mno | pqr
jkl | mno | pqr
jkl | mno | pqr
stu | vwx | yz
stu | vwx | yz
stu | vwx | yz
(9 rows)

ebridges=> select distinct * from has_some_dupes;
a_col | b_col | c_col
-------+-------+-------
abc | def | ghi
jkl | mno | pqr
stu | vwx | yz
(3 rows)

Browse pgsql-general by date

  From Date Subject
Next Message Hiroshi Inoue 2000-11-14 01:33:56 Re: Problems during doing reindex on 7.0.3
Previous Message Loren Koss 2000-11-14 00:18:42 Question regarding temporary tables & functions..