SQL 如何在 MS Access 中获取一个表中不在另一个表中的所有行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/724341/
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
How do I get all the rows in one table that are not in another in MS Access?
提问by Dave Barker
I have tried a bunch of different things but always get syntax errors.
我尝试了很多不同的东西，但总是遇到语法错误。
I have two tables - tableA and tableB. They both have a con_number field as a unique ID. I want to select all of the rows in tableB that do not exist in tableA.
我有两个表 - tableA 和 tableB。它们都有一个 con_number 字段作为唯一 ID。我想选择 tableB 中 tableA 中不存在的所有行。
Can anyone please give me this query as it would be in MS Access?
任何人都可以给我这个查询，就像在 MS Access 中一样吗？
I know that using NOT IN is quite inefficient in this case so if there is a better way then that would be great.
我知道在这种情况下使用 NOT IN 效率很低，所以如果有更好的方法那就太好了。
Thanks.
谢谢。
回答by Dave Barker
SELECT TableB.con_number
FROM TableB
WHERE NOT EXISTS (SELECT 1
FROM TableA
WHERE TableA.con_number = TableB.con_number);
回答by Fionnuala
There is a Find Unmatched wizard that will set this up. The SQL is:
有一个 Find Unmatched 向导可以进行设置。该 SQL 是：
SELECT TableB.con_number
FROM TableB LEFT JOIN TableA
ON TableB.con_number = TableA.con_number
WHERE TableA.con_number Is Null
回答by soulmerge
NOT IN version (slow but sure):
NOT IN 版本（缓慢但确定）：
SELECT con_number
FROM TableB
WHERE con_number NOT IN (SELECT con_number FROM tableA);
experimental version (don't know if this is any faster, just try it out):
实验版（不知道这是否更快，请尝试一下）：
SELECT B.con_number, MAX(A.con_number) AS check
FROM tableB B LEFT JOIN tableA A ON B.con_number = A.con_number
GROUP BY B.con_number
HAVING check IS NULL;
Note: Both should be fairly standard SQL, I don't know any ms-access specific features
注意：两者都应该是相当标准的 SQL，我不知道任何 ms-access 特定功能
回答by Philippe Grondier
I remember something like this one:
我记得类似这样的一件事：
SELECT * FROM TableA.* LEFT JOIN TableB _
ON TableA.con_number = TableB.con_number WHERE 'criteria'
But I don't remember which 'criteria' to use
但我不记得使用哪个“标准”
... TableA.con_number <> TableB.con_Number
... TableB.con_number IS NULL
... TableA.con_number NOT like TableB.con_Number
