如何查询关联表中未配对记录并获取关联表其他字段

这是个很典型的「找出未关联记录」的SQL问题，我给你两种常用且高效的解决方案，都能满足你获取讲师和科目表其他字段的需求：

方法1：LEFT JOIN + IS NULL 筛选 #

这种写法逻辑直观，适合新手理解，通过左关联关联表后筛选无匹配的记录：

SELECT 
    i.instructor_id,
    i.name AS instructor_name, -- 示例讲师表字段，可替换为实际字段
    i.email,
    s.subject_id,
    s.subject_name, -- 示例科目表字段，可替换为实际字段
    s.description
FROM instructors i
-- 先将目标讲师与所有科目做笛卡尔积，确保覆盖所有可能的科目
CROSS JOIN subjects s
-- 左关联讲师科目关联表，保留所有讲师-科目组合
LEFT JOIN instructor_subject isub 
    ON isub.instructor_id = i.instructor_id 
    AND isub.subject_id = s.subject_id
WHERE 
    i.instructor_id = 1 -- 指定目标讲师ID
    AND isub.instructor_id IS NULL; -- 筛选关联表中无匹配的记录（即未关联科目）

逻辑说明： #

1. 用CROSS JOIN把讲师ID1和所有科目（1、2、3）组合成三条记录；
2. 通过LEFT JOIN关联讲师科目表，此时已关联的科目（1、2）会匹配到关联表的记录，未关联的科目3则会得到NULL值；
3. 最后筛选出关联表字段为NULL的记录，就是讲师1未关联的科目。

方法2：NOT EXISTS 子查询 #

这种写法在多数数据库中性能更优（尤其是数据量较大时），通过子查询判断是否存在关联记录：

SELECT 
    i.instructor_id,
    i.name AS instructor_name,
    i.email,
    s.subject_id,
    s.subject_name,
    s.description
FROM instructors i
JOIN subjects s
WHERE 
    i.instructor_id = 1
    AND NOT EXISTS (
        -- 子查询检查当前讲师-科目是否存在关联记录
        SELECT 1 
        FROM instructor_subject isub 
        WHERE isub.instructor_id = i.instructor_id 
          AND isub.subject_id = s.subject_id
    );

逻辑说明： #

1. 先关联目标讲师和所有科目；
2. NOT EXISTS子查询会针对每条讲师-科目组合，检查是否在关联表中存在对应记录；
3. 只有当子查询返回空（即无关联记录）时，这条讲师-科目组合才会被保留。

两种方法最终都会返回你需要的结果：讲师ID1的所有字段 + 科目ID3的所有字段。你可以根据自己使用的数据库（如MySQL、PostgreSQL等）和数据量大小选择合适的写法。

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