如何带跨表条件多表连接SQL表并得到指定结果？

现有三张数据表：

表A #

表B #

表C #

其中etc.代表需要一并查询的无关列。需要连接这三张表得到如下目标结果：

目标结果 #

尝试的SQL及问题 #

尝试了以下SQL语句（原语句存在字段名错误：c.C_ID应为c.A_ID）：

SELECT a.ID as 'A_ID', b.NAME as 'B_NAME', c.NAME as 'C_NAME', b.etc as 'etc. B', c.etc as 'etc. C'
FROM A a
FULL JOIN B b on a.ID = b.A_ID
FULL JOIN C c on a.ID = c.A_ID and (c.NAME = b.NAME or b.NAME is null)

得到的结果中，当B中无匹配NAME时，A_ID为null，不符合目标要求：

当前错误结果 #

解决方案 #

方法1：使用UNION ALL分三类合并（逻辑清晰） #

将结果分为三类分别查询后合并，确保A_ID始终从表A获取：

-- 1. B和C中NAME匹配的记录
SELECT 
    a.ID AS A_ID,
    b.NAME AS B_NAME,
    c.NAME AS C_NAME,
    b.etc AS `etc. B`,
    c.etc AS `etc. C`
FROM A a
JOIN B b ON a.ID = b.A_ID
JOIN C c ON a.ID = c.A_ID AND b.NAME = c.NAME

UNION ALL

-- 2. 仅B存在的记录（C无对应NAME）
SELECT 
    a.ID AS A_ID,
    b.NAME AS B_NAME,
    NULL AS C_NAME,
    b.etc AS `etc. B`,
    NULL AS `etc. C`
FROM A a
JOIN B b ON a.ID = b.A_ID
WHERE NOT EXISTS (
    SELECT 1 FROM C c WHERE c.A_ID = a.ID AND c.NAME = b.NAME
)

UNION ALL

-- 3. 仅C存在的记录（B无对应NAME）
SELECT 
    a.ID AS A_ID,
    NULL AS B_NAME,
    c.NAME AS C_NAME,
    NULL AS `etc. B`,
    c.etc AS `etc. C`
FROM A a
JOIN C c ON a.ID = c.A_ID
WHERE NOT EXISTS (
    SELECT 1 FROM B b WHERE b.A_ID = a.ID AND b.NAME = c.NAME
)

方法2：修正FULL JOIN关联逻辑并使用COALESCE #

通过COALESCE函数从表A、B、C中取非空的A_ID，同时调整关联条件避免A_ID为空：

SELECT 
    COALESCE(a.ID, b.A_ID, c.A_ID) AS A_ID,
    b.NAME AS B_NAME,
    c.NAME AS C_NAME,
    b.etc AS `etc. B`,
    c.etc AS `etc. C`
FROM B b
FULL JOIN C c ON b.A_ID = c.A_ID AND b.NAME = c.NAME
LEFT JOIN A a ON COALESCE(b.A_ID, c.A_ID) = a.ID

错误原因说明 #

原SQL的问题在于：

1. 字段名错误：将表C的A_ID误写为C_ID；
2. 当仅存在表C的记录时，由于FULL JOIN B没有匹配项，a.ID会变为null，缺少从c.A_ID取值的逻辑。

内容的提问来源于stack exchange，提问作者KSa2