mysql - 检索在部门编号 5 控制的所有项目中工作的每位员工的姓名

Question

本题摘自《数据库系统基础》第 6 版(Elmasri，Navathe)第 5 章查询 3b。作者回答这个问题的一种方法是使用存在量词:

SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
                  FROM WORKS_ON B
                  WHERE ( B.Pno IN ( SELECT Pnumber
                                    FROM PROJECT
                                    WHERE Dnum=5 )
                  AND
                  NOT EXISTS ( SELECT *
                               FROM WORKS_ON C
                               WHERE C.Essn=Ssn
                               AND C.Pno=B.Pno )));

作者将解决方案解释为:

Select each employee such that there does not exist a project controlled by department 5 that the employee does not work on

我无法理解第二个内部相关子查询如何与另一个不相关子查询和外部查询相关以给出正确的结果。
感谢您的帮助。

Answer 1

SELECT fname, lname FROM employee
WHERE NOT EXISTS
(SELECT pnumber FROM project WHERE dnum=5
AND pnumber NOT IN
(SELECT pno FROM works_on WHERE ssn=essn));

请注意 Except 在 mysql 中不起作用