Thread: Question on UNION

Everyone,

I ran into something I wasn't expecting while developing a new application.  I have two similar tables that are
occasionallyunioned pulling only about 3 fields from each.  During my testing phase I noticed that the union statement
wasreturning what appeared to be a distinct list rather than a pure union such as is illustrated below:
 

create table t1 (     f1 serial primary key,     f2 text,     f3 text,     f4 integer
);

create table t2 (     f1 serial primary key,     f2 text,     f3 text,     f4 integer
);

insert into t1 (f2, f3, f4) values ('A', 'a', 1);
insert into t1 (f2, f3, f4) values ('A', 'a', 1);
insert into t1 (f2, f3, f4) values ('B', 'b', 2);
insert into t1 (f2, f3, f4) values ('B', 'b', 2);

insert into t2 (f2, f3, f4) values ('C', 'c', 3);
insert into t2 (f2, f3, f4) values ('C', 'c', 3);
insert into t2 (f2, f3, f4) values ('D', 'd', 4);
insert into t2 (f2, f3, f4) values ('D', 'd', 4);

temp=# select * from t1;f1 | f2 | f3 | f4
----+----+----+---- 1 | A  | a  |  1 2 | A  | a  |  1 3 | B  | b  |  2 4 | B  | b  |  2
(4 rows)

temp=# select * from t2;f1 | f2 | f3 | f4
----+----+----+---- 1 | C  | c  |  3 2 | C  | c  |  3 3 | D  | d  |  4 4 | D  | d  |  4
(4 rows)

When I leave out f1 I receive a distinct list:

temp=# (select f2, f3, f4 from t1) UNION (select f2, f3, f4 from t2) order by f4;f2 | f3 | f4
----+----+----A  | a  |  1B  | b  |  2C  | c  |  3D  | d  |  4
(4 rows)

When the key field is included I get a full list:

temp=# (select * from t1) UNION (select * from t2) order by f4;f1 | f2 | f3 | f4
----+----+----+---- 1 | A  | a  |  1 2 | A  | a  |  1 3 | B  | b  |  2 4 | B  | b  |  2 1 | C  | c  |  3 2 | C  | c  |
33 | D  | d  |  4 4 | D  | d  |  4
 
(8 rows)

Can anyone explain the underlying principle(s) in the UNION that would cause this?

Thanks in advance
Mark

Hi, Mark,

mark.dingee@cox.net schrieb:

> I ran into something I wasn't expecting while developing a new
> application.  I have two similar tables that are occasionally unioned
> pulling only about 3 fields from each.  During my testing phase I
> noticed that the union statement was returning what appeared to be a
> distinct list rather than a pure union such as is illustrated below:

This is the documented behaviour of UNION, if you don't wand duplicate
elimination, use UNION ALL.

http://www.postgresql.org/docs/8.1/static/sql-select.html documents this
nicely.

HTH,
Schabi