表A
id value
1 4
2 5
表B
id value
1 6
3 7
需要的结果表
id a.value b.value
1 4 6
2 5
3 7
------解决方案--------------------
SQL> create table a(id number,value number);
Table created
SQL> create table b(id number,value number);
Table created
SQL> insert into a values(1,4);
1 row inserted
SQL> insert into a values(2,5);
1 row inserted
SQL> insert into b values(1,6);
1 row inserted
SQL> insert into b values(3,7);
1 row inserted
SQL> select a.id,a.value,b.value from a left join b on a.id = b.id
2 union
3 select b.id,a.value,b.value from b left join a on b.id = a.id
4 ;
ID VALUE VALUE
---------- ---------- ----------
1 4 6
2 5
3 7
SQL>
------解决方案--------------------
明显的全外连接的用法：
给你个例子：
LOVE2008> select * from t;
ID NAME
---------- --------------------
1 WZK
2 ZX
3 wz
LOVE2008> select * from test;
ID VALUE
---------- ----------
1 1
2 2
LOVE2008> select nvl(a.id,b.id),a.value,b.name from test a full outer join t b on a.id=b.id;
NVL(A.ID,B.ID) VALUE NAME
-------------- ---------- --------------------
1 1 WZK
2 2 ZX
3 wz
------解决方案--------------------
love_2008(love2008) 的说法正确，外连接的写法 full join on