SQL SQL查询：列出一个表中没有出现在另一表中的所有项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4048709/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query: list all items in one table that do not appear in another table
提问by Sinaesthetic
I'm working on a training tracker program and I'm at a point where I can't figure out the SQL query.
我正在开发一个训练跟踪器程序，但我无法弄清楚 SQL 查询。
I have 3 tables: employees, trainingRecords, masterList.
我有 3 张桌子：employees, trainingRecords, masterList.
employeesand trainingRecordsare related through the empIDfkey.
employees并且trainingRecords通过empIDfkey相关联。
trainingRecordsand masterListare related through the TIDfkey.
trainingRecords并且masterList通过TIDfkey相关联。
Right now the training records table is blank because nothing has been entered (all employees have no training).
现在培训记录表是空白的，因为没有输入任何内容（所有员工都没有接受培训）。
I want to populate a listbox with all of the items in the masterList that are unaccounted for in the trainingRecordstable.
我想用 masterList 中未在trainingRecords表中说明的所有项目填充列表框。
Since the trainingRecordstable is blank, it should be returning lName, fNamefrom the employeestable and docName, docNumberfor all entries in the master list.
由于trainingRecords表是空白的，它应该lName, fName从employees表和docName, docNumber主列表中的所有条目返回。
I'm stumped. Any suggestions?
我难住了。有什么建议？
采纳答案by JumpingJezza
I'm assuming you want to display all employees multiple times with the training documents they have not done yet.
我假设您希望多次向所有员工显示他们尚未完成的培训文档。
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
example results:
结果示例：
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
回答by Suroot
You want LEFT JOIN, on the left side of the join will be the table that you know will contain everything and on the right will be what you are testing against.
您需要 LEFT JOIN，连接的左侧将是您知道将包含所有内容的表，右侧将是您正在测试的表。
select masterList.* from masterList LEFT JOIN trainingRecords ON(masterList.TID = trainingRecords.TID) WHERE trainingRecords.TID IS NULL;
回答by Larry Lustig
Okay, you have to JOIN all three tables with the trainingRecords table in the middle because it has the columns necessary to link the other two tables. Your query will look something like this:
好的，您必须将所有三个表与中间的 trainingRecords 表连接起来，因为它具有链接其他两个表所需的列。您的查询将如下所示：
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
What's happening here?
这里发生了什么事？
First, you're doing a LEFT OUTER JOIN of employees and trainingRecords. The LEFT OUTER is to ensure that all the records from employees show up even if there's no match in trainingRecords (which of course don't exist since trainingRecords has no data at all).
首先，您正在对员工和培训记录进行左外联接。LEFT OUTER 是为了确保即使 trainingRecords 中没有匹配项（这当然不存在，因为 trainingRecords 根本没有数据），员工的所有记录也会显示出来。
Then, you're taking the results of that query and RIGHT OUTER JOINing them to masterList. The RIGHT OUTER guarantees that all masterList records will be included even if there are no matches in trainingRecords.
然后，您将获取该查询的结果并将它们右外连接到 masterList。RIGHT OUTER 保证即使 trainingRecords 中没有匹配项，也将包含所有 masterList 记录。
Finally, WHERE TR.TID IS NULL filters out any records that actually matched any (future) records in trainingRecords.
最后，WHERE TR.TID IS NULL 过滤掉与 trainingRecords 中任何（未来）记录实际匹配的任何记录。
回答by Eric Shen
Why not use Full Join? What I use is:
为什么不使用完全连接？我使用的是：
Select A.* from A Full Join B on A.ID = B.ID where B.ID is NULL
