Is this proper UNION behavior??

Started by Edward Q. Bridgesover 25 years ago1 messagesgeneral
Jump to latest
#1Edward Q. Bridges
ed.bridges@buzznik.com

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)