pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Question on UNION
2+ messages / 2 participants
[nested] [flat]

* Question on UNION
@ 2006-05-22 18:58  mark.dingee@cox.net
  0 siblings, 1 reply; 2+ messages in thread

From: mark.dingee@cox.net @ 2006-05-22 18:58 UTC (permalink / raw)
  To: pgsql-sql

Everyone,

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:

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  |  1
 B  | b  |  2
 C  | c  |  3
 D  | 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  |  3
  3 | 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Question on UNION
@ 2006-05-22 19:07  Markus Schaber <schabi@logix-tt.com>
  parent: mark.dingee@cox.net
  0 siblings, 0 replies; 2+ messages in thread

From: Markus Schaber @ 2006-05-22 19:07 UTC (permalink / raw)
  To: mark.dingee@cox.net; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-05-22 19:07 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-22 18:58 Question on UNION mark.dingee@cox.net
2006-05-22 19:07 ` Markus Schaber <schabi@logix-tt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox