From: | "hx(dot)li" <fly2nn(at)126(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FULL JOIN is only supported with merge-joinable join conditions |
Date: | 2010-01-06 07:48:58 |
Message-ID: | hi1f9a$2djd$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi guys,
I have a question about outer join. For example as follow (pg 8.4.1):
------------------------------
create table t_1(a int);
create table t_3(a int);
insert into t_1 values(1);
insert into t_1 values(2);
insert into t_3 values(1);
insert into t_3 values(3);
postgres=# select version();
version
-------------------------------------------------------------
PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit
(1 row)
postgres=# select * from t_1 full outer join t_3 on t_1.a=1;
ERROR: FULL JOIN is only supported with merge-joinable join conditions
--------------------------
My question is: why on clause restrict "t_1.a=1"?
I test it in Oracle, it support to such as "t_1.a=1":
------------------------
SQL> SELECT * FROM TAB1;
A B C
---------- ---------- ----------
2
3
SQL> SELECT * FROM TAB2;
A
----------
1
2
SQL> select * from tab1 full outer join tab2 on tab1.a=2;
A B C A
---------- ---------- ---------- ----------
2 1
2 2
3
SQL> select * from tab1 left outer join tab2 on tab1.a=2;
A B C A
---------- ---------- ---------- ----------
2 1
2 2
3
SQL> select * from tab1 right outer join tab2 on tab1.a=2;
A B C A
---------- ---------- ---------- ----------
2 1
2 2
SQL> select * from tab1 right outer join tab2 on 1=1;
A B C A
---------- ---------- ---------- ----------
2 1
3 1
2 2
3 2
SQL> select * from tab1 right outer join tab2 on tab2.a=2;
A B C A
---------- ---------- ---------- ----------
1
2 2
3 2
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2010-01-06 08:45:10 | Re: using a function |
Previous Message | Dann Corbit | 2010-01-06 07:05:36 | Re: PostgreSQL Write Performance |