oracle的全连接的替换

[ERP资深顾问]

处理后 tab1
企业（ep） 期间（date） 值（value）
1 2006 1
2 2006 2
3 2006 3

处理后 tab2
企业 期间 值
1 2007 1
4 2007 4
5 2007 5
6 2010 6

希望结果
企业 期间1 值1 期间2 值1
1 2006 1 2007 1
2 2006 2 2007 0
3 2006 3 2007 0
4 2006 0 2007 4
5 2006 0 2007 5


全连接的结果是所有信息都显示，左右连接又只满足一边，想知道怎么弄呢？

[HelloWorld_001]

select * from tab1,tab2
where tab1.ep = tab2.ep(+)
union
select * from tab1,tab2
where tab1.ep(+) = tab2.ep

只想到这样做

[star_guan2008]

select * from tab1 full outer jion tab2
on tab1.ep = tab2.ep

[gracepop]

select j1.a a,'2006' b1,j1.c c1,'2007' b2,j2.c c2
from jyq_test001 j1,jyq_test002 j2
where j1.a=j2.a(+)
union
select j2.a a,'2006' b1,j1.c c1,'2007' b2,j2.c c2
from jyq_test001 j1,jyq_test002 j2
where j1.a(+)=j2.a

[HelloWorld_001]

full join就可以了，2楼的很好

[zhu_gx]

帮顶

[abchappybear]

select erp,decode(dat,'','2006',dat),decode(val,'','0',val),decode(dat1,'','2007',dat1),decode(val1,'','0',val1) from
(select a.erp,a.dat,a.val,b.dat1,b.val1 from abc a left join bac b on a.erp=b.erp1
union
select b.erp1,a.dat,a.val,b.dat1,b.val1 from bac b left join abc a on b.erp1=a.erp where dat1 <> '2010') tabnum

[pccastle]

ding

[pccastle]

jf

[meiZiNick]

什么呀

[xjianshi]

今天试了一下，发现少了一次全表扫描，性能有所提高；
select decode(t1.useragent,null,t2.useragent,t1.useragent),t1.succ,t2.fail from test1 t1 full join test2 t2 on t1.useragent = t2.useragent