SQL里联接怎么写?

[TroubleShotting]

现在有两个表A,B
A
a b
---
1 3
2 4

B
a c
---
1 3
3 7

想把他们合并成下表,应该怎么写呢?
a b c
-----
1 3 3
2 4 0
3 0 7

谢了

BTW, VB+ACCESS+SQL

[华芸智森]

seelct a.a,a.b,b.c from b right join a on b.a=a.a order by a.a

[TroubleShotting]

太感谢了。我运行了代码,但是报告 iff函数未定义
我应该怎么修改呢

[mlxboy]

select ISNULL(A.a,B.a),ISNULL(A.b,'0'),ISNULL(B.c,'0')
from A full outer join B
on A.a=B.a

'0' 可替换为你需要的字符
不加空值判断没有的字段为NULL
ISNULL(A.b,'0'),ISNULL(B.c,'0')

[haipingma]

SELECT A.[a], A.[b],B.[c]
FROM A
left join B ON A.[a]=B.[a]
union
SELECT B.[a], A.[b],B.[c]
FROM B
left join A ON B.[a]=A.[a]

[TroubleShotting]

多谢了，但还是存在两个小问题
1
我想把查询结果写入另一个表D中:
SELECT A.[a], A.[b],B.[c] INTO D
FROM A
left join B ON A.[a]=B.[a]
union
SELECT B.[a], A.[b],B.[c] INTO D
FROM B
left join A ON B.[a]=A.[a]
这时出错:动作查询不能作为行的来源

2
原来没有的数据(如结果第二行的c)在结果中是空值,而不是0

这个怎么解决呢

[haipingma]

select *
into D
from (SELECT A.[a], A.[b],iif(B.[c] is null,0,B.[c])
FROM A
left join B ON A.[a]=B.[a]
UNION SELECT B.[a], iif(A.[b] is null,0,A.[b]),B.[c]
FROM B
left join A ON B.[a]=A.[a])

[华芸智森]

但是报告 iff函数未定义,haipingma(ToJava) 是ACCESS数据库的.在SQL里用CASE .... WHEN ... END
'------------------------------------------------
select *
into D
from (SELECT A.[a], A.[b],CASE WHEN B.[c] is null THEN 0 ELSE B.[c] END,
FROM A
left join B ON A.[a]=B.[a]
UNION SELECT B.[a], CASE WHEN A.[B] is null THEN 0 ELSE A.[B] END,B.[c]
FROM B
left join A ON B.[a]=A.[a])

[artoksxb]

在VB下能使用iif函数
在sql下没有iif函数

[TroubleShotting]

实在是太谢谢各位了，但这回还是不能运行
用case...when...else...end时,会出现这个错误:
对象'Open'的方法'_Recordset'失败
用IsNull函数出现这个提示:
ISNULL参数的个数不对

BTW,我用的是Access+VB6,没用SQL Server