如何编写SELECT查询以排除存在指定第三表特定引用的行？

原查询问题说明 #

你的原有写法存在两处逻辑缺陷：

• 两次关联均使用INNER JOIN，天然会过滤掉没有关联任何fruit_data、data记录的fruit行
• 仅用WHERE data.name != 'glass'做筛选，只要某条fruit同时关联了名称为glass和非glass的data记录，仍然会被返回，不符合「关联的所有data名称都不为glass」的要求

推荐实现方案 #

首推NOT EXISTS写法，逻辑清晰、数据库优化友好，可完美匹配你的需求：

SELECT DISTINCT f.fruit_id
FROM fruit f
WHERE NOT EXISTS (
    -- 子查询判断当前fruit是否关联了名称为glass的data
    SELECT 1
    FROM fruit_data fd
    INNER JOIN data d 
        ON fd.data_id = d.data_id
    WHERE fd.fruit_id = f.fruit_id
      AND d.name = 'glass'
);

逻辑说明：只要子查询没有匹配结果，就代表当前fruit要么没有关联任何data，要么关联的所有data名称都不为glass，完全符合筛选要求。

可选实现方案 #

如果需要同时统计关联的其他信息，可以用左连接+分组聚合的写法：

SELECT f.fruit_id
FROM fruit f
LEFT JOIN fruit_data fd 
    ON f.fruit_id = fd.fruit_id
LEFT JOIN data d 
    ON fd.data_id = d.data_id
GROUP BY f.fruit_id
-- 统计每个fruit关联的名称为glass的data数量，为0即符合要求
HAVING COUNT(CASE WHEN d.name = 'glass' THEN 1 END) = 0;

也可以用NOT IN写法，但需要注意避免子查询返回空值导致整个筛选失效：

SELECT DISTINCT fruit_id
FROM fruit
WHERE fruit_id NOT IN (
    SELECT DISTINCT fd.fruit_id
    FROM fruit_data fd
    INNER JOIN data d 
        ON fd.data_id = d.data_id
    WHERE d.name = 'glass'
      AND fd.fruit_id IS NOT NULL -- 规避空值导致NOT IN返回空的问题
);

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