如何查询表A中未在表B存在的共享主键记录？SQL纠错

咱们先从问题最明显的第二个SQL说起：

• 你用A, B的写法会生成笛卡尔积——也就是表A的每一条记录都会和表B的每一条记录配对一次。你的WHERE条件B.country <> A.country and B.color <> A.color逻辑完全不对：只要表B里存在任意一条和当前A记录不匹配的条目，这条A记录就会被筛选出来，哪怕B里明明有和它完美匹配的记录。举个例子：A有一条('red','US')，B里既有('red','US')（匹配），又有('blue','UK')（不匹配），那么笛卡尔积里A的这条记录和B的('blue','UK')配对时会满足条件，导致这条本应被排除的A记录出现在结果里。这个写法直接pass就好。

再看第一个SQL，逻辑方向是对的，但可能有几个细节导致结果不符合预期：

1. 多余的GROUP BY：你加了group by A.country, A.color，这会把表A里重复的(color,country)组合合并成一条。如果你要的是表A中所有符合条件的记录（包括重复的），这个GROUP BY会让你丢失重复项；如果只是要去重后的组合，用DISTINCT会比GROUP BY更直观。
2. NULL值的隐形坑：如果表A或B的color/country字段存在NULL值（虽然你说它们是主键，但万一设计没严格约束呢？），那么B.country = A.country这类比较会返回UNKNOWN，NOT EXISTS会把这种情况判定为“没有匹配”，导致本该被排除的记录被保留。比如A的某条记录color是NULL，哪怕B里有完全相同的(NULL, 'US')，B.color = A.color也会不成立，NOT EXISTS就会把这条A记录选出来。
3. 字段类型/格式不一致：比如A的country是带空格的'US '，B的是'US'，或者字段类型一个是CHAR一个是VARCHAR，都会导致看起来相同的内容比较不相等，误判为“不存在匹配”。

推荐的正确写法 #

写法1：NOT EXISTS（最直观，性能通常也不错）

如果要获取表A中所有符合条件的记录（包括重复项）：

SELECT A.color, A.country, A.other_columns  -- 按需添加其他需要的字段
FROM A
WHERE NOT EXISTS (
    SELECT 1
    FROM B
    WHERE 
        -- 处理NULL的情况，如果确定字段无NULL，可简化为 B.country = A.country AND B.color = A.color
        (B.country IS NOT DISTINCT FROM A.country)
        AND (B.color IS NOT DISTINCT FROM A.color)
);

如果只需要去重后的(color,country)组合：

SELECT DISTINCT A.color, A.country
FROM A
WHERE NOT EXISTS (
    SELECT 1
    FROM B
    WHERE 
        (B.country IS NOT DISTINCT FROM A.country)
        AND (B.color IS NOT DISTINCT FROM A.color)
);

写法2：LEFT JOIN（另一种常用思路）

和NOT EXISTS逻辑等价，适合习惯用JOIN写法的场景：

SELECT A.color, A.country, A.other_columns
FROM A
LEFT JOIN B 
    ON (B.country IS NOT DISTINCT FROM A.country)
    AND (B.color IS NOT DISTINCT FROM A.color)
WHERE B.color IS NULL;  -- LEFT JOIN后无匹配的记录，B的字段会是NULL

注：如果你的数据库不支持IS NOT DISTINCT FROM，可以用以下方式处理NULL比较：

WHERE 
    (B.country = A.country OR (B.country IS NULL AND A.country IS NULL))
    AND (B.color = A.color OR (B.color IS NULL AND A.color IS NULL))

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