FULL JOIN is only supported with merge-joinable join conditions
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
"hx.li" <fly2nn@126.com> writes:
ERROR: FULL JOIN is only supported with merge-joinable join conditions
My question is: why on clause restrict "t_1.a=1"?
It's an implementation restriction. If the clauses aren't mergejoinable
there's no very practical way to keep track of which inner-side rows
have had a match.
I test it in Oracle, it support to such as "t_1.a=1":
I'd be interested to know how whatever they're doing scales to very
large joins.
regards, tom lane
It's an implementation restriction. If the clauses aren't mergejoinable
there's no very practical way to keep track of which inner-side rows
have had a match.
If we could consider it is equivalent transformation as follow?
select * from t_1 full outer join t_3 on t_1.a=1;
and
select * from t_1 full outer join t_3 on true where t_1.a=1;
If we could transform RestrictInfo into the where-clause, maybe it right.
TEST=# select * from t_1 full outer join t_3 on true where t_1.a=1;
A | A
---+---
1 | 1
1 | 3
(2 rows)
"hx.li" <fly2nn@126.com> writes:
If we could consider it is equivalent transformation as follow?
select * from t_1 full outer join t_3 on t_1.a=1;
and
select * from t_1 full outer join t_3 on true where t_1.a=1;
Those are not equivalent.
regards, tom lane