sql - 三个表中的SQL Select语法错误

Question

SELECT Surname
FROM Worker
WHERE NOT EXISTS
(
SELECT *
FROM Project, Works_at
WHERE NOT EXISTS
(
SELECT *
FROM Works_at
WHERE Works_at.WorkerId =Worker.Id
AND Works_at.PNum = Project.PNumber
)
);

要么

SELECT Surname
FROM Worker
WHERE NOT EXISTS (
SELECT *
FROM Works_at
WHERE Id = WorkerId
);

当我运行查询时，它说“SELECT”附近有语法错误
有什么问题吗？
首先选择在所有项目中工作的 worker 。
第二个选择 worker 不在任何项目中工作。
提前致谢

表是

Worker = {Name, Surname, Id, Salary, DptNum}

Department = {DptName, DptNumber}

Works_at = {WorkerId, PNum, Hours}

Project={PName, PNumber, DptNum} and

Works_at(WorkerId) → Worker(Id)

Works_at(PNum) → Projekt(PNumber)

Project(DptNum) → Department(DptNumber)

Worker(DptNum) → Department(DptNumber)

Answer 1

NOT IN子选择的性能很糟糕。最好使用LEFT-JOIN进行测试并测试NULL(即:没有匹配项)

SELECT 
      W.Surname
   FROM 
      Worker W
         LEFT JOIN Works_At WA
            on W.ID = WA.WorkerID
            LEFT JOIN Project P
               on WA.PNum = P.PNumber
   WHERE
      P.PNumber IS NULL

这应该为您提供与项目无关的人员列表。这是基于有可能在“工作地点”进行的，但未分配任何项目。如果表中的作品中没有记录，那么使用它甚至会更简单

SELECT 
      W.Surname
   FROM 
      Worker W
         LEFT JOIN Works_At WA
            on W.ID = WA.WorkerID
   WHERE
      WA.WorkerID IS NULL