从 NOT EXISTS 转换为 NOT IN

我有三张桌子：

• sailor（姓名、评级）；
• boat（名称、颜色、等级）；
• reservation（sname、bname、工作日、开始、结束）；

为了获得预订每艘红船的水手名单，我有：

select s.sname from sailor s 
where not exists(  
    select * from boat b  
    where b.color = 'red'  
    and not exists (  
        select * from reservation r  
        where r.bname = b.bname  
        and r.sname = s.sname));

我现在需要用 NOT IN 而不是 NOT EXISTS 重写此查询。这是我到目前为止所拥有的：

select s.sname from sailor s
where s.sname not in (select s2.sname from sailor s2 where
    s2.sname not in (select r.sname from reservation r where r.bname not in (
            select b.bname from boat b where b.color <> 'red' )));

然而，这会返回所有预订了红船的水手的列表（不一定是全部）。我很难检查列表中的名称是否预订了每艘船（我也不能使用 COUNT()）。

任何帮助表示赞赏

这很好笑;您可以在替换时保持语法结构（相关子查询）NOT EXISTS ( ... ) by IN ( ...) :

SELECT s.sname from sailor s
WHERE 13 NOT IN (
    SELECT 13 FROM boat b
    WHERE b.color = 'red'
    AND 42 NOT IN (
        SELECT 42 from reservation r
        WHERE r.bname = b.bname
        AND r.sname = s.sname
       )
    );