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)
