2个简单的表的联合

Question

有表A1：Code        Name       Yuwen
001         张三        65
002         李四        78
003         王五        90有表A2：Code        Name       English    Shuxue
001         张三        88          77
002         李四        50          44
004         陈六        60          0  
006         梁九        55          66我想要的效果是：Code        Name       Yuwen       English       Shuxue
001         张三        65           88            77
002         李四        78           50            44
003         王五        90           0             0
004         陈六        0            60            0
006         梁九        0            55            66数据库环境：sql 2000 不是2005

Answer 1

select isnull(a.code,b.code) code,isnull(a.name,b.name) name,isnull(a.yumen,0) yumen,
       isnull(a.english,0) english,isnull(a.shuxue,0) shuxue
from A1 a full join A2 b on a.code = b.code

Answer 2

select a1.code,a1.name,isnull(yuwen,0),isnull(english,0),isnull(shuxue,0) from a1 left join a2 
on a1.code = a2.code
union
select a2.code,a2.name,isnull(yuwen,0),isnull(english,0),isnull(shuxue,0) from a1 right join a2 
on a1.code = a2.code

Answer 3

select a1.code,a1.name,isnull(yuwen,0) yuwen,isnull(english,0) english,isnull(shuxue,0) shuxue from a1 left join a2 
on a1.code = a2.code
union
select a2.code,a2.name,isnull(yuwen,0) yuwen,isnull(english,0) english,isnull(shuxue,0) shuxue from a1 right join a2 
on a1.code = a2.code
-------------------------------------
code,name,yuwen,english,shuxue
001,张三,65,88,77
002,李四,78,50,44
003,王五,90,0,0
004,陈六,0,60,0
006,梁九,0,55,66