(select a.code, nvl(a.money1,0), nvl(a.money2,0), nvl(b.money3,0), nvl(b.money4,0) from a left join b on a.code = b.code ) union (select b.code, nvl(a.money1,0), nvl(a.money2,0), nvl(b.money3,0), nvl(b.money4,0) from b left join a on a.code = b.code )
SQL> SELECT * FROM T; CODE MONEY1 MONEY2 ---------- ---------- ---------- 1 123 234 2 343 435 3 435 346SQL> SELECT * FROM T1; CODE MONEY3 MONEY4 ---------- ---------- ---------- 2 3453 4645645 3 4545 4576547 4 3453 34534SQL> SELECT T.CODE,T.MONEY1,T.MONEY2,NVL(T1.MONEY3,0),NVL(T1.MONEY4,0) FROM T,T1 WHERE T.CODE=T1.COD E(+) 2 UNION 3 SELECT T1.CODE,0,0,T1.MONEY3,T1.MONEY4 FROM T,T1 WHERE T.CODE(+)=T1.CODE AND T.CODE IS NULL; CODE MONEY1 MONEY2 NVL(T1.MONEY3,0) NVL(T1.MONEY4,0) ---------- ---------- ---------- ---------------- ---------------- 1 123 234 0 0 2 343 435 3453 4645645 3 435 346 4545 4576547 4 0 0 3453 34534
select a.code,a.money1,a.money2,nvl(b.money3,0),nvl(b.money4,0) from a,b where a.code=b.code(+) union select b.code,nvl(a.money1,0),nvl(a.money2,0),b.money3,b.money4 from a,b where a.code(+)=b.code;
select nvl(a.code,b.code),nvl(a.Money1,0),nvl(a.Money2,0), nvl(b.Money3,0),nvl(b.Money4,0) from a full join b on b.code=a.code;
union
(select b.code, nvl(a.money1,0), nvl(a.money2,0), nvl(b.money3,0), nvl(b.money4,0) from b left join a on a.code = b.code )
---------- ---------- ----------
1 123 234
2 343 435
3 435 346SQL> SELECT * FROM T1; CODE MONEY3 MONEY4
---------- ---------- ----------
2 3453 4645645
3 4545 4576547
4 3453 34534SQL> SELECT T.CODE,T.MONEY1,T.MONEY2,NVL(T1.MONEY3,0),NVL(T1.MONEY4,0) FROM T,T1 WHERE T.CODE=T1.COD
E(+)
2 UNION
3 SELECT T1.CODE,0,0,T1.MONEY3,T1.MONEY4 FROM T,T1 WHERE T.CODE(+)=T1.CODE AND T.CODE IS NULL; CODE MONEY1 MONEY2 NVL(T1.MONEY3,0) NVL(T1.MONEY4,0)
---------- ---------- ---------- ---------------- ----------------
1 123 234 0 0
2 343 435 3453 4645645
3 435 346 4545 4576547
4 0 0 3453 34534
union
select b.code,nvl(a.money1,0),nvl(a.money2,0),b.money3,b.money4 from a,b where
a.code(+)=b.code;
nvl(b.Money3,0),nvl(b.Money4,0)
from a
full join b on b.code=a.code;