从NOT EXISTS转换为NOT IN

Question

我有三张桌子：

• sailor（姓名，等级）；
• 船（名称、颜色、等级）；
• 预订（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));

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' )));

Answer 1

这很有趣；在将not EXISTS(...)替换为(...)时，可以维护语法结构（相关子查询）：

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
       )
    );