SQL如何查询两张表仅共有关联列时另一表无匹配的指定列唯一值

需求梳理 #

• 匹配两张表仅有的共同字段Col1、Col2的组合
• 筛选出Table1中该组合未出现在Table2中的所有行
• 对结果的ColXX、ColYY字段去重输出

实现方案 #

方案1：NOT EXISTS 实现（全数据库兼容） #

重点处理了NULL值无法直接用等号比较的场景，兼容所有主流SQL数据库：

SELECT DISTINCT
    t1.ColXX,
    t1.ColYY
FROM
    Table1 t1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Table2 t2
        WHERE
            -- 处理Col1的相等逻辑，包含NULL匹配
            ((t1.Col1 = t2.Col1) OR (t1.Col1 IS NULL AND t2.Col1 IS NULL))
            AND
            -- 处理Col2的相等逻辑，包含NULL匹配
            ((t1.Col2 = t2.Col2) OR (t1.Col2 IS NULL AND t2.Col2 IS NULL))
    )

方案2：LEFT JOIN 实现 #

和NOT EXISTS逻辑一致，用左连接匹配后过滤未匹配上的行：

SELECT DISTINCT
    t1.ColXX,
    t1.ColYY
FROM
    Table1 t1
LEFT JOIN Table2 t2
ON
    ((t1.Col1 = t2.Col1) OR (t1.Col1 IS NULL AND t2.Col1 IS NULL))
    AND
    ((t1.Col2 = t2.Col2) OR (t1.Col2 IS NULL AND t2.Col2 IS NULL))
WHERE
    t2.ID IS NULL

简化写法（支持IS NOT DISTINCT FROM语法的数据库可用） #

如果使用PostgreSQL、SQL Server 2022及以上版本，可直接用系统自带的NULL值相等判断语法简化代码：

SELECT DISTINCT
    t1.ColXX,
    t1.ColYY
FROM
    Table1 t1
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Table2 t2
        WHERE
            t1.Col1 IS NOT DISTINCT FROM t2.Col1
            AND t1.Col2 IS NOT DISTINCT FROM t2.Col2
    )

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