问大家一下,假如有两个表.A,B
结构如下 :
A B
ID NAME ID NAME
001 A1 005 B5
002 A2 009 B9
003 A3 002 B2
004 A4 003 B3
005 A5 008 B8
006 A6
007 A7
008 A8
009 A9
010 A10
用什么语句能得到下面的结果
A.ID A.NAME B.ID B.NAME 001 A1 0 0
002 A2 002 B2
003 A3 003 B3
004 A4 0 0
005 A5 005 B5
006 A6 0 0
007 A7 0 0
008 A8 008 B8
009 A9 009 B90表示为空
结构如下 :
A B
ID NAME ID NAME
001 A1 005 B5
002 A2 009 B9
003 A3 002 B2
004 A4 003 B3
005 A5 008 B8
006 A6
007 A7
008 A8
009 A9
010 A10
用什么语句能得到下面的结果
A.ID A.NAME B.ID B.NAME 001 A1 0 0
002 A2 002 B2
003 A3 003 B3
004 A4 0 0
005 A5 005 B5
006 A6 0 0
007 A7 0 0
008 A8 008 B8
009 A9 009 B90表示为空
FROM A LEFT JOIN B ON A.ID = B.ID
ORDER BY A.ID;
nvl(B.id,0) as Bid,
nvl(B.name,0) as Bname
from A,
B
where A.id = B.id (+);ID NAME BID BNAME
--- ---- --- -----
001 A1 0 0
002 A2 002 B2
003 A3 003 B3
004 A4 0 0
005 A5 005 B5
006 A6 0 0
007 A7 0 0
008 A8 008 B8
009 A9 009 B9
010 A10 0 010 rows selected
FROM
(select '001' AID, 'A1' ANAME from dual
union
select '002' AID,'A2' ANAME from dual
union
select '003' AID, 'A3' ANAME from dual
union
select '004' AID ,'A4' ANAME from dual
union
select '005' AID , 'A5' ANAME from dual
union
select '006' AID, 'A6' ANAME from dual
union
select '007' AID , 'A7' ANAME from dual
union
select '008' AID , 'A8' ANAME from dual
union
select '009' AID, 'A9' ANAME from dual
union
select '010' AID, 'A10' ANAME from dual) A
left outer join
(select '005' BID, 'B5' bname from dual
union
select '009' bid, 'b9' bname from dual
union
select '002' bid, 'b2' bname from dual
union
select '003' bid, 'b3' bname from dual
union
select '008' bid, 'b8' bname from dual) b on a.aid=b.bid