疑問 - 有關SQL語法

[juju73729]

請教各位前輩

表A
name | number
aaa | 1
bbb | 2
ccc | 3

表B
NAME | grade
aaa | 90
bbb | 80
ddd | 60

如還才能表現
name | grade | number
aaa | 90 | 1
bbb | 80 | 2
ccc | 0 | 3
ddd | 60 | 0

(NAME 不重複,無對應值代0)
我試過UNION ALL 但似乎也不是我要的

請教各位前輩了 在此先感謝

[劍痞憶秋年]

參考看看

select case when a.name is null then b.name else a.name end ,case when b.grade is null then 0 else b.grade end as grade ,
case when a.number is null then 0 else a.number end as number from a full join b on a.name=b.name

[juju73729]

很抱歉 前輩
我忘了跟你講, 我是使用access
好像不支援full join 和 select case
是否能再請您提點提點

[劍痞憶秋年]

差點就想說不可能 達到 --> 查了一下如下

SELECT a.name, b.grade, a.number
FROM a INNER JOIN b ON a.name = b.name
UNION ALL
SELECT a.name, IIF(b.grade IS NULL,0,b.grade), a.number
FROM a LEFT JOIN b ON a.name = b.name
WHERE (((b.grade) Is Null))
UNION ALL
SELECT b.name, b.grade, IIF(a.number IS NULL,0,a.number)
FROM a RIGHT JOIN b ON a.name = b.name
WHERE a.name is null