SQL查询：列出一个表中所有不在另一个表中出现的项目

Question

我正在开发一个培训跟踪程序，但我无法解决SQL查询的问题。

我有三个表：employees, trainingRecords, masterList。 employees和trainingRecords通过empID fkey相关联。 trainingRecords和masterList通过TID fkey相关联。

目前，由于没有输入任何内容（所有员工都没有接受过培训），因此培训记录表为空。

我想用主列表中未在trainingRecords表中列出的所有项目来填充列表框。

由于trainingRecords表为空，应从employees表返回lName, fName以及主列表中所有条目的docName, docNumber。

我被卡住了。有什么建议吗？

- Sinaesthetic

Answer 1

我假设你想展示所有员工未完成的培训文档，并重复显示他们的信息。

SELECT a.lName, a.fName, b.docNumber, b.docName 
FROM
(SELECT e.lName, e.fName, t.TID 
 FROM employees e
 LEFT JOIN trainingRecords t ON e.empID = t.empID
) AS a,
(SELECT m.docNumber, m.docName, t.TID
 FROM masterList m
 LEFT JOIN trainingRecords t ON m.TID = t.TID
) AS b
WHERE a.TID IS NULL OR b.TID IS NULL
ORDER BY a.lName, b.docNumber

示例结果：

lName     fName  docNumber          docName
Simpson   Homer     1      Nuclear Physics for Dummies
Simpson   Homer     2      Nuclear Physics for Beginners
Simpson   Homer     3      Advanced Nuclear Physics
Simpson   Lisa      3      Advanced Nuclear Physics

- JumpingJezza

---

这起初是有效的，但我添加了一条记录后，现在它返回空表。 - Sinaesthetic

修改了where子句并在b部分添加了另一列。现在应该可以正常工作了 :) - JumpingJezza

哇！就在那里。我甚至能够将其分解并从记忆中重新创建它，谢谢。 - Sinaesthetic

Answer 2

您需要使用LEFT JOIN，左侧的联接表将包含您知道的所有内容，右侧将是您要测试的内容。

select masterList.* from masterList LEFT JOIN trainingRecords ON(masterList.TID = trainingRecords.TID) WHERE trainingRecords.TID IS NULL; 

- Suroot

---

我最终弄清了这个问题的大部分，但是如何将员工姓名加入其中呢？ - Sinaesthetic

Answer 3

好的，你需要将这三个表与中间的trainingRecords表进行JOIN操作，因为它具有链接其他两个表所需的列。你的查询语句应该类似于这样：

 SELECT E.lName, E.fName, ML.docName, ML.docNumber FROM
   (employees E LEFT OUTER JOIN trainingRecords TR ON E.empID = TR.empID)
                RIGHT OUTER JOIN masterList ML ON ML.TID = TR.TID
   WHERE TR.TID IS NULL

这里发生了什么？

首先，您正在使用员工和培训记录的LEFT OUTER JOIN。 LEFT OUTER是为了确保所有来自员工的记录都显示出来，即使在trainingRecords中没有匹配（当然，由于trainingRecords根本没有数据，因此不存在匹配）。

然后，您将该查询的结果与masterList进行RIGHT OUTER JOIN。 RIGHT OUTER保证即使在trainingRecords中没有匹配，也会包括所有masterList记录。

最后，WHERE TR.TID IS NULL过滤掉任何实际上匹配任何（未来的）trainingRecords记录的记录。

- Larry Lustig

---

不确定发生了什么，但是我收到了一个错误提示，说连接不受支持。别名？ - Sinaesthetic

Answer 4

为什么不使用全连接？我使用的是：

Select A.* from A Full Join B on A.ID = B.ID where B.ID is NULL

- Eric Shen

---

刚刚意识到在这种情况下，全连接与左连接具有相同的结果。 - Eric Shen