MySQL JOIN查询结果异常：筛选无指定影片关联的人员

你需要筛选出参与影片tconst=tt0058461，且在film_berufe_cross_tbl表中无该影片关联记录的人员，但现有查询仅返回1条结果（实际应为29条）——问题出在当人员存在其他影片的关联记录时，会被错误排除。

正确查询写法 #

以下两种写法都能准确筛选出符合条件的人员，核心是明确排除的是该人员与目标影片的关联记录，而非所有关联记录：

写法1：使用NOT EXISTS

SELECT d.*
FROM darsteller_kf d
JOIN kauffilme k ON d.tconst = k.tconst
WHERE k.tconst = 'tt0058461'
AND NOT EXISTS (
    SELECT 1
    FROM film_berufe_cross_tbl f
    -- 关联人员ID，并限定是目标影片的关联记录
    WHERE f.person_id = d.person_id 
    AND f.tconst = 'tt0058461'
);

写法2：使用LEFT JOIN + IS NULL

SELECT d.*
FROM darsteller_kf d
JOIN kauffilme k ON d.tconst = k.tconst
-- 左关联时直接限定目标影片的tconst
LEFT JOIN film_berufe_cross_tbl f 
    ON d.person_id = f.person_id 
    AND f.tconst = 'tt0058461'
WHERE k.tconst = 'tt0058461'
-- 筛选出无目标影片关联记录的人员
AND f.person_id IS NULL;

原查询的问题分析 #

你的现有查询大概率犯了以下错误之一：

• 错误1：将film_berufe_cross_tbl的tconst条件放到了WHERE子句中，而非JOIN的关联条件里。这会导致LEFT JOIN退化为INNER JOIN，过滤掉所有有其他影片关联记录的人员。
• 错误2：在NOT EXISTS子查询中未限定tconst='tt0058461'，导致只要人员存在任何影片的关联记录就被排除，而非仅排除有目标影片关联记录的人员。

错误写法示例：

-- 错误：WHERE子句中限定f.tconst，导致过滤掉有其他影片关联的人员
SELECT d.*
FROM darsteller_kf d
JOIN kauffilme k ON d.tconst = k.tconst
LEFT JOIN film_berufe_cross_tbl f ON d.person_id = f.person_id
WHERE k.tconst = 'tt0058461'
AND f.tconst = 'tt0058461'
AND f.person_id IS NULL;

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