From: | "Edward Q(dot) Bridges" <ed(dot)bridges(at)buzznik(dot)com> |
---|---|
To: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Is this proper UNION behavior?? |
Date: | 2000-11-14 01:40:53 |
Message-ID: | 200011140146.eAE1k7s06474@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)
From | Date | Subject | |
---|---|---|---|
Next Message | Alvar Freude | 2000-11-14 01:59:09 | Array-Handling: use in subselect, push ... |
Previous Message | Hiroshi Inoue | 2000-11-14 01:33:56 | Re: Problems during doing reindex on 7.0.3 |