T-SQL查询实现：筛选主表与子表ID匹配但seq不匹配的记录

原查询逻辑错误原因 #

你编写的查询逻辑存在偏差：EXISTS子句的判断规则是只要同id下存在任意一条sub_table的seq与当前main_table的seq不等，就会返回当前记录。这会导致例如A1的seq=1这类本应排除的记录，因为同id下存在sub_table的seq=2满足不等条件而被错误返回。

正确实现方案 #

下面给出3种常用的可实现需求的写法，均可返回预期结果：

写法1：使用NOT EXISTS（最推荐，性能最优） #

SELECT
    a.id, a.seq 
FROM 
    main_table a
WHERE 
    -- 保证当前id在sub_table中存在
    EXISTS (SELECT 1 FROM sub_table b WHERE b.id = a.id)
    -- 保证同id下没有匹配的seq
    AND NOT EXISTS (SELECT 1 FROM sub_table b WHERE b.id = a.id AND b.seq = a.seq)

写法2：使用LEFT JOIN加空值判断 #

SELECT
    a.id, a.seq
FROM
    main_table a
-- 筛选仅在sub_table中存在的id
INNER JOIN (SELECT DISTINCT id FROM sub_table) c ON a.id = c.id
-- 关联匹配seq
LEFT JOIN sub_table b ON a.id = b.id AND a.seq = b.seq
-- 仅保留没有匹配到seq的记录
WHERE b.id IS NULL

写法3：使用EXCEPT求差集 #

-- 取sub_table中存在的id对应的所有main_table记录
SELECT id, seq FROM main_table WHERE id IN (SELECT id FROM sub_table)
EXCEPT
-- 排除sub_table中已存在的同id同seq记录
SELECT id, seq FROM sub_table

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