如何简化表连接查询的交叉验证，筛选无对应授课资质的讲师

问题根源 #

你当前查询返回行数过多的核心原因是LEFT JOIN仅关联了讲师ID维度，没有同步匹配科目，导致单条授课记录会和该讲师名下所有持证科目记录做笛卡尔积关联，产生大量冗余数据。

优化方案 #

方案1：关联条件新增科目匹配，直接过滤无资质记录

在原有LEFT JOIN逻辑中新增科目的匹配规则，再通过WHERE条件仅保留没有匹配到资质的记录即可，写法如下：

SELECT
    t.instructor,
    t.subject_taught,
    c.subject_cert
FROM taught_courses AS t
LEFT JOIN certification AS c
    ON t.instructor = c.instructor_id 
    AND t.subject_taught = c.subject_cert
WHERE c.subject_cert IS NULL
ORDER BY t.instructor, t.subject_taught ASC;

方案2：用NOT EXISTS实现，大表场景下性能更优

如果两张表的数据量较大，用NOT EXISTS的写法执行效率通常更高：

SELECT
    t.instructor,
    t.subject_taught,
    NULL AS subject_cert
FROM taught_courses AS t
WHERE NOT EXISTS (
    SELECT 1 
    FROM certification AS c
    WHERE c.instructor_id = t.instructor 
    AND c.subject_cert = t.subject_taught
)
ORDER BY t.instructor, t.subject_taught ASC;

两种方案的输出结果都和你预期的结构完全一致，无需再手动交叉校验数据。

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