多表关联查询：按规则返回Table_2与Table_3的F_ID匹配结果

要满足需求，你需要用全外连接（FULL OUTER JOIN）结合主表关联，或者通过UNION ALL合并三种场景（双向匹配、仅Table_2有数据、仅Table_3有数据），这样才能覆盖所有边界情况。以下是两种可行方案：

方案1：使用FULL OUTER JOIN（兼容SQL Server、PostgreSQL等支持该语法的数据库） #

SELECT 
    t1.Global_ID,
    COALESCE(t2.F_ID, t3.F_ID) AS F_ID,
    t2.F_ID AS Table2_F_ID,
    t3.F_ID AS Table3_F_ID
FROM 
    Table_1 t1
LEFT JOIN (
    SELECT Global_ID, F_ID FROM Table_2
) t2 ON t1.Global_ID = t2.Global_ID
FULL OUTER JOIN (
    SELECT Global_ID, F_ID FROM Table_3
) t3 ON t1.Global_ID = t3.Global_ID 
    AND t2.F_ID = t3.F_ID
WHERE 
    t2.F_ID IS NOT NULL OR t3.F_ID IS NOT NULL
ORDER BY 
    t1.Global_ID, F_ID;

说明： #

• 用FULL OUTER JOIN关联Table_2和Table_3，同时匹配Global_ID和F_ID，确保捕获所有匹配/不匹配的F_ID记录
• COALESCE用来统一展示F_ID列，让相同值在同一行呈现
• WHERE子句过滤掉两个表都无数据的行（如果需要保留主表所有Global_ID，即使没有F_ID，可去掉该条件）
• 自动适配仅单表有数据、全匹配、无数据等场景

方案2：使用UNION ALL（兼容所有主流数据库） #

如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以用UNION ALL合并三类结果：

-- 1. Table_2和Table_3中F_ID相同的行
SELECT 
    t1.Global_ID,
    t2.F_ID,
    t2.F_ID AS Table2_F_ID,
    t3.F_ID AS Table3_F_ID
FROM Table_1 t1
JOIN Table_2 t2 ON t1.Global_ID = t2.Global_ID
JOIN Table_3 t3 ON t1.Global_ID = t3.Global_ID AND t2.F_ID = t3.F_ID

UNION ALL

-- 2. 仅Table_2有F_ID的行
SELECT 
    t1.Global_ID,
    t2.F_ID,
    t2.F_ID AS Table2_F_ID,
    NULL AS Table3_F_ID
FROM Table_1 t1
JOIN Table_2 t2 ON t1.Global_ID = t2.Global_ID
LEFT JOIN Table_3 t3 ON t1.Global_ID = t3.Global_ID AND t2.F_ID = t3.F_ID
WHERE t3.F_ID IS NULL

UNION ALL

-- 3. 仅Table_3有F_ID的行
SELECT 
    t1.Global_ID,
    t3.F_ID,
    NULL AS Table2_F_ID,
    t3.F_ID AS Table3_F_ID
FROM Table_1 t1
JOIN Table_3 t3 ON t1.Global_ID = t3.Global_ID
LEFT JOIN Table_2 t2 ON t1.Global_ID = t2.Global_ID AND t3.F_ID = t2.F_ID
WHERE t2.F_ID IS NULL

ORDER BY Global_ID, F_ID;

说明： #

• 拆分三类场景分别查询后合并，确保所有情况都被覆盖
• 保留主表的Global_ID关联，避免遗漏主表记录
• 对MySQL这类不支持全外连接的数据库更友好

场景验证 #

• 全匹配场景：同一Global_ID下Table_2和Table_3的F_ID完全一致，会合并为一行展示
• 仅单表有数据：仅Table_2或Table_3有F_ID时，对应列显示值，另一列为NULL，单独成行
• 无数据场景：如果主表某Global_ID在两个子表都无F_ID，方案1去掉WHERE条件后会显示该行，F_ID及子表列均为NULL

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