MySQL关联两表查询指定条件下非匹配行的实现方法

已知条件 #

• 表字段映射规则：
  • Table1的subject字段与Table2的subect字段（注意Table2该字段拼写为subect，非subject）存储内容一致
  • Table1的topic字段与Table2的name字段存储内容一致
• 筛选规则：匹配subject = 'Accounting'且grade = 'grade 10'的范围
• 返回规则：仅返回Table2中未在Table1匹配到对应topic的记录，排除已存在关联的Financial Accounting条目，最终返回Managerial Accounting、Managing resources两条数据

测试数据参考 #

Table1 结构与测试数据 #

Table2 结构与测试数据 #

预期返回结果 #

实现语句 #

优先使用NOT EXISTS写法，该写法在存在索引的场景下查询性能更稳定，不会受字段NULL值影响结果：

SELECT t2.*
FROM Table2 t2
WHERE 
  t2.subect = 'Accounting'
  AND t2.grade = 'grade 10'
  AND NOT EXISTS (
    SELECT 1
    FROM Table1 t1
    WHERE
      t1.subject = t2.subect
      AND t1.topic = t2.name
      AND t1.grade = t2.grade
  );

也可以使用左连接判断空值的写法，返回结果完全一致：

SELECT t2.*
FROM Table2 t2
LEFT JOIN Table1 t1
  ON t1.subject = t2.subect
  AND t1.topic = t2.name
  AND t1.grade = t2.grade
WHERE
  t2.subect = 'Accounting'
  AND t2.grade = 'grade 10'
  AND t1.subject IS NULL;

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