用full join就可以了。select greatest(nvl(tableA.customid,0),nvl(tableB.customid,0)) customid, greatest(nvl(tableA.managerid,0),nvl(tableB.managerid,0)) managerid, deposit,loan from tableA full join tableB on tableA.customid=tableB.customid and tableA.managerid=tableB.managerid;
来个通用版本吧,case其实效率也挺高的!--db2上测试, with taba as( select '1001' cid,'3000' mid,4000 dps from sysibm.sysdummy1 union all select '1002' cid,'8000' mid,47000 dps from sysibm.sysdummy1), tabb as( select '1001' cid,'2000' mid,3000 ln from sysibm.sysdummy1 union all select '1001' cid,'3000' mid,5000 ln from sysibm.sysdummy1) select case when a.cid is null then b.cid else a.cid end, case when a.mid is null then b.mid else a.mid end, dps,ln from taba a full join tabb b on a.cid=b.cid and a.mid=b.mid -------------------------------- 1001 3000 4000 5000 1001 2000 3000 1002 8000 47000
正解,不过 greatest 是oracle自带的函数 最好用case when 这样的sql99通用语法,但是只是在oracle上适用 greatest 绝对好使。
支持用full join... select decode(a.id,null,b.id,a.id) id ,decode(a.mid,null,b.mid,a.mid) mid , a.did,b.pid from a full join b on a.id = b.id and a.mid = b.mid
greatest(nvl(tableA.managerid,0),nvl(tableB.managerid,0)) managerid,
deposit,loan from
tableA full join tableB on
tableA.customid=tableB.customid and tableA.managerid=tableB.managerid;
with taba as(
select '1001' cid,'3000' mid,4000 dps from sysibm.sysdummy1
union all
select '1002' cid,'8000' mid,47000 dps from sysibm.sysdummy1),
tabb as(
select '1001' cid,'2000' mid,3000 ln from sysibm.sysdummy1
union all
select '1001' cid,'3000' mid,5000 ln from sysibm.sysdummy1)
select case when a.cid is null then b.cid else a.cid end,
case when a.mid is null then b.mid else a.mid end,
dps,ln
from taba a full join tabb b
on a.cid=b.cid
and a.mid=b.mid
--------------------------------
1001 3000 4000 5000
1001 2000 3000
1002 8000 47000
最好用case when 这样的sql99通用语法,但是只是在oracle上适用 greatest 绝对好使。
你执行上的Sql,看看执行计划,消耗的内存与时间。。就知道哪个效率高了。