如何筛选不含特定限制成分的Recipe？含则返回Null的SQL查询咨询

场景说明 #

现有三张业务表：

• rezepte：存储食谱基础信息，核心字段为REZEPTEID（食谱ID）
• rezeptezutaten：食谱与配料的关联表，通过REZEPTEID关联食谱，ZUTATENNR关联配料编号
• inhaltsstoffe：存储配料的限制属性，ZUTATENNR对应配料编号，INHALTSSTOFFEID是限制类型ID（比如1代表肉类、2代表素食标识等）

需求目标：

1. 筛选出**完全不含指定限制成分（比如INHALTSSTOFFEID=1）**的所有食谱
2. 若食谱包含该限制成分，返回Null（或直接排除这类食谱，可按需选择）

现有语句的问题 #

你当前的SQL写法：

SELECT * 
FROM `rezepte` 
JOIN rezeptezutaten ON rezepte.REZEPTEID = rezeptezutaten.REZEPTEID 
JOIN inhaltsstoffe ON inhaltsstoffe.ZUTATENNR = rezeptezutaten.ZUTATENNR 
WHERE inhaltsstoffe.INHALTSSTOFFEID != 1;

存在逻辑漏洞：如果某个食谱同时包含受限成分和非受限成分，只要有一个配料满足INHALTSSTOFFEID !=1，这条关联记录就会被筛选出来，导致该食谱被错误返回，无法完全排除含受限成分的食谱。

可行解决方案 #

方案1：完全排除含受限成分的食谱（推荐） #

使用NOT EXISTS子查询，直接判断当前食谱是否不存在关联到目标限制成分的配料：

SELECT r.*
FROM rezepte r
WHERE NOT EXISTS (
    -- 检查当前食谱是否有配料属于受限成分
    SELECT 1
    FROM rezeptezutaten rz
    JOIN inhaltsstoffe i ON rz.ZUTATENNR = i.ZUTATENNR
    WHERE rz.REZEPTEID = r.REZEPTEID
      AND i.INHALTSSTOFFEID = 1 -- 替换为你要排除的限制属性ID
);

逻辑说明：NOT EXISTS会确保只有那些没有任何配料关联到目标限制成分的食谱才会被返回，彻底排除含受限成分的食谱。

方案2：含受限成分的食谱返回Null，其余正常返回 #

如果需求是保留所有食谱，但含受限成分的字段返回Null，可使用CASE结合EXISTS判断：

SELECT 
    -- 对每个字段进行判断，含受限成分则返回Null
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM rezeptezutaten rz
            JOIN inhaltsstoffe i ON rz.ZUTATENNR = i.ZUTATENNR
            WHERE rz.REZEPTEID = r.REZEPTEID
              AND i.INHALTSSTOFFEID = 1
        ) THEN NULL
        ELSE r.REZEPTEID
    END AS REZEPTEID,
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM rezeptezutaten rz
            JOIN inhaltsstoffe i ON rz.ZUTATENNR = i.ZUTATENNR
            WHERE rz.REZEPTEID = r.REZEPTEID
              AND i.INHALTSSTOFFEID = 1
        ) THEN NULL
        ELSE r.食谱名称 -- 替换为实际字段名
    END AS 食谱名称,
    -- 其他字段按上述格式依次添加
FROM rezepte r;

也可以用LEFT JOIN优化写法，避免重复子查询：

SELECT 
    IF(restricted_recipes.REZEPTEID IS NULL, r.REZEPTEID, NULL) AS REZEPTEID,
    IF(restricted_recipes.REZEPTEID IS NULL, r.食谱名称, NULL) AS 食谱名称
    -- 其他字段同理
FROM rezepte r
LEFT JOIN (
    -- 先查出所有含受限成分的食谱ID
    SELECT DISTINCT rz.REZEPTEID
    FROM rezeptezutaten rz
    JOIN inhaltsstoffe i ON rz.ZUTATENNR = i.ZUTATENNR
    WHERE i.INHALTSSTOFFEID = 1
) restricted_recipes ON r.REZEPTEID = restricted_recipes.REZEPTEID;

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