如何双向查找两个SQL表中互不存在的合同及付款记录

以下方案适配SQL Server环境，匹配t1/t2表contractID、paymentID均为nvarchar(255)类型、单合同对应多笔付款的业务前提。

需求1：双向查找仅存在于单表的合同记录 #

原全外连接写法存在两个问题：一是连接条件加入了paymentID，匹配粒度为「合同+付款」而非合同维度；二是未添加空值过滤，会返回两表内连接匹配成功的记录。

推荐实现（差集写法，大表性能更优） #

-- 返回仅在t1存在的合同下所有付款记录
SELECT * FROM t1
WHERE contractID NOT IN (SELECT DISTINCT contractID FROM t2)

UNION ALL

-- 返回仅在t2存在的合同下所有付款记录
SELECT * FROM t2
WHERE contractID NOT IN (SELECT DISTINCT contractID FROM t1)

如果只需要返回差异合同ID列表，把SELECT *替换为SELECT DISTINCT contractID即可。
如果要沿用全外连接写法，参考如下：

SELECT 
  COALESCE(t1.contractID, t2.contractID) AS contractID,
  CASE WHEN t1.contractID IS NOT NULL THEN '仅t1存在' ELSE '仅t2存在' END AS diff_position
FROM t1
FULL OUTER JOIN t2 ON t1.contractID = t2.contractID
WHERE t1.contractID IS NULL OR t2.contractID IS NULL
GROUP BY COALESCE(t1.contractID, t2.contractID), CASE WHEN t1.contractID IS NOT NULL THEN '仅t1存在' ELSE '仅t2存在' END

需求2：共有合同下的单表独有付款记录 #

该查询不需要手动遍历合同，SQL为集合级查询，引擎会自动覆盖所有两表共有的合同完成匹配，以下为两种可直接使用的实现：

推荐实现（EXISTS判断，逻辑清晰性能好） #

-- 共有合同下仅t1存在的付款
SELECT contractID, paymentID, '仅t1存在' AS diff_position
FROM t1
WHERE 
  EXISTS (SELECT 1 FROM t2 WHERE t2.contractID = t1.contractID)
  AND NOT EXISTS (SELECT 1 FROM t2 WHERE t2.contractID = t1.contractID AND t2.paymentID = t1.paymentID)

UNION ALL

-- 共有合同下仅t2存在的付款
SELECT contractID, paymentID, '仅t2存在' AS diff_position
FROM t2
WHERE 
  EXISTS (SELECT 1 FROM t1 WHERE t1.contractID = t2.contractID)
  AND NOT EXISTS (SELECT 1 FROM t1 WHERE t1.contractID = t2.contractID AND t1.paymentID = t2.paymentID)

全外连接写法 #

SELECT
  COALESCE(t1.contractID, t2.contractID) AS contractID,
  t1.paymentID AS t1_paymentID,
  t2.paymentID AS t2_paymentID,
  CASE WHEN t1.paymentID IS NOT NULL THEN '仅t1存在' ELSE '仅t2存在' END AS diff_position
FROM t1
FULL OUTER JOIN t2 
  ON t1.contractID = t2.contractID AND t1.paymentID = t2.paymentID
WHERE
  -- 排除两表完全匹配的合同付款对
  (t1.paymentID IS NULL OR t2.paymentID IS NULL)
  -- 排除仅单表存在的合同，仅保留两表共有合同
  AND EXISTS (SELECT 1 FROM t1 t_a WHERE t_a.contractID = COALESCE(t1.contractID, t2.contractID))
  AND EXISTS (SELECT 1 FROM t2 t_b WHERE t_b.contractID = COALESCE(t1.contractID, t2.contractID))

优化提示：为两表的contractID、paymentID建立联合索引，可以大幅提升以上查询的执行速度，注意查询时保持两表字段的排序规则一致，避免隐式转换导致匹配错误。

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