在BigQuery中区分表A与表B的互异数据并标记场景

要实现区分「A表有但B表无（标记extra）」和「B表有但A表无（标记missing）」的需求，你可以通过以下两种方式修改SQL：

方法1：分场景查询后合并（直观易维护） #

这种方式分别查询两种差异情况，再用UNION ALL合并结果，逻辑清晰：

-- 筛选A表独有的数据，标记为'extra'
SELECT 
    CONCAT(A.c1, A.c2) AS unique_key,
    'extra' AS diff_scenario
FROM A
WHERE NOT EXISTS (
    SELECT 1 
    FROM B 
    WHERE CONCAT(B.b1, B.e) = CONCAT(A.c1, A.c2)
)
UNION ALL
-- 筛选B表独有的数据，标记为'missing'
SELECT 
    CONCAT(B.b1, B.e) AS unique_key,
    'missing' AS diff_scenario
FROM B
WHERE NOT EXISTS (
    SELECT 1 
    FROM A 
    WHERE CONCAT(A.c1, A.c2) = CONCAT(B.b1, B.e)
);

关键点： #

• 用NOT EXISTS替代原SQL的NOT IN：NOT IN在子查询返回NULL时会直接返回空结果，NOT EXISTS则不会有这个问题，逻辑更可靠
• 保留了你原有的字段拼接匹配逻辑，如果字段本身可以直接对比（比如A.c1 = B.b1 AND A.c2 = B.e），建议直接用字段对比，比拼接字符串更高效

方法2：全外连接一次性获取结果 #

如果你的数据库支持FULL OUTER JOIN（如MySQL 8.0+、SQL Server、PostgreSQL等），可以用一次连接查询完成：

SELECT 
    COALESCE(CONCAT(A.c1, A.c2), CONCAT(B.b1, B.e)) AS unique_key,
    CASE
        WHEN A.c1 IS NULL THEN 'missing'  -- B表有，A表无
        WHEN B.b1 IS NULL THEN 'extra'    -- A表有，B表无
    END AS diff_scenario
FROM A
FULL OUTER JOIN B 
    ON CONCAT(A.c1, A.c2) = CONCAT(B.b1, B.e)
WHERE A.c1 IS NULL OR B.b1 IS NULL; -- 只保留两边不匹配的记录

关键点： #

• FULL OUTER JOIN会返回两个表的所有记录，匹配失败的一侧字段会为NULL
• 通过CASE语句判断NULL所在的表，标记对应的差异场景
• COALESCE用来提取非空的拼接键值，确保结果列有值

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