SQL实现：筛选Result表与Ref表num组合匹配及不匹配的result_id

常见踩坑原因 #

你之前用左连接没得到预期结果，基本逃不开这三个问题：

• 连接顺序搞反：如果从Ref表出发左连接Result表，会丢失Result表中无匹配组合的result_id，拿不到全量的结果列表
• 连接条件不全：只拿单个num字段做关联，剩下三个num的等值判断没写，会出现大量交叉匹配的错误结果
• 条件位置错放：把Ref和Result的num等值判断写在了WHERE子句里，左连接产生的Ref字段为NULL的不匹配行会被WHERE直接过滤，最后只剩匹配成功的记录

正确实现方案 #

因为需求是输出所有Result表的result_id，同时标记每个result_id的num四元组是否在Ref表存在完全匹配，主表需要固定为Result表，基于全四字段做等值关联。

写法1：左连接关联 #

SELECT 
  r.result_id,
  CASE 
    WHEN rf.num_1 IS NOT NULL THEN '组合完全匹配Ref表'
    ELSE '组合无匹配'
  END AS match_result
FROM Result r
LEFT JOIN Ref rf
  ON r.num_1 = rf.num_1
 AND r.num_2 = rf.num_2
 AND r.num_3 = rf.num_3
 AND r.num_4 = rf.num_4;

注意：如果Ref表存在完全重复的num四元组，上面的写法会让Result的同一个result_id被重复输出，需要在SELECT后加DISTINCT去重。

写法2：EXISTS子查询判断 #

如果Ref表重复组合较多，更推荐用EXISTS实现，不会产生重复行，大数据量下性能更优：

SELECT 
  r.result_id,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM Ref rf
      WHERE rf.num_1 = r.num_1
        AND rf.num_2 = r.num_2
        AND rf.num_3 = r.num_3
        AND rf.num_4 = r.num_4
    ) THEN '组合完全匹配Ref表'
    ELSE '组合无匹配'
  END AS match_result
FROM Result r;

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