有点长哈,不过测试是通过的,你直接把代码贴到PL/SQL中运行就可以了 如下 with test1 as ( select '1' as id from dual union all select '3' as id from dual union all select '4' as id from dual union all select '5' as id from dual ), test2 as ( select '1' as id from dual union all select '2' as id from dual union all select '3' as id from dual union all select '5' as id from dual ), test3 as ( select aid from ( select a.id as aid ,b.id as bid from test2 a ,test1 b where a.id = b.id(+) union select a.id as aid ,b.id as bid from test1 a ,test2 b where a.id = b.id(+) ) order by aid ) select a.id ,b.id from test1 a , test2 b, test3 c where c.aid =a.id(+) and c.aid = b.id(+)
full join select a.id,b.id from a full outer join b on a.id=b.id
把上面的代码简化一下。。 with test1 as ( select '1' as id from dual union all select '3' as id from dual union all select '4' as id from dual union all select '5' as id from dual ), test2 as ( select '1' as id from dual union all select '2' as id from dual union all select '3' as id from dual union all select '5' as id from dual ), test3 as ( select aid from ( select a.id as aid test2 a union select a.id as aid test1 a ) order by aid ) select a.id ,b.id from test1 a , test2 b, test3 c where c.aid =a.id(+) and c.aid = b.id(+)
full outer join ? 这是什么语法?
full join 全联接 left join 外联接 inner join 内联接
这是左外连接,右外连接以及全外连接的问题。 楼主用的左外连接,现在你要达到全外连接的效果,就把左外连接换成全外连接就行了。 sql代码如下: select A.id,B.id from A FULL join B on A.id=B.id
如下 with
test1 as
(
select '1' as id from dual
union all
select '3' as id from dual
union all
select '4' as id from dual
union all
select '5' as id from dual
),
test2 as
(
select '1' as id from dual
union all
select '2' as id from dual
union all
select '3' as id from dual
union all
select '5' as id from dual
),
test3 as
(
select aid from
(
select a.id as aid ,b.id as bid from test2 a ,test1 b where a.id = b.id(+)
union
select a.id as aid ,b.id as bid from test1 a ,test2 b where a.id = b.id(+)
)
order by aid
)
select a.id ,b.id from test1 a , test2 b, test3 c where c.aid =a.id(+) and c.aid = b.id(+)
select a.id,b.id
from a full outer join b
on a.id=b.id
with
test1 as
(
select '1' as id from dual
union all
select '3' as id from dual
union all
select '4' as id from dual
union all
select '5' as id from dual
),
test2 as
(
select '1' as id from dual
union all
select '2' as id from dual
union all
select '3' as id from dual
union all
select '5' as id from dual
),
test3 as
(
select aid from
(
select a.id as aid test2 a
union
select a.id as aid test1 a
)
order by aid
)
select a.id ,b.id from test1 a , test2 b, test3 c where c.aid =a.id(+) and c.aid = b.id(+)
这是什么语法?
left join 外联接
inner join 内联接
楼主用的左外连接,现在你要达到全外连接的效果,就把左外连接换成全外连接就行了。
sql代码如下:
select A.id,B.id from A FULL join B on A.id=B.id