如何使用 Access SQL VBA 在 where 子句中正确使用两个 Not Exists 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2563693/
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 correctly use two Not Exists statements in a where clause using Access SQL VBA?
提问by Bryan
I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address.
我有 3 个表：NotHeard、analyzed、analyzed2。在这些表中的每一个中，我都有两列名为 UnitID 和 Address。
What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows:
我现在想要做的是从 NotHeard 中选择 UnitID 和 Address 列的所有记录，这些记录没有出现在分析或分析 2 中。我创建的SQL语句如下：
SELECT UnitID, Address
INTO [NotHeardByEither]
FROM [NotHeard]
Where NOT EXISTS( Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID)
or NOT EXISTS( Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID)
Group BY UnitID, Address
I thought this would work since I've used the single NOT EXISTS subquery line and it has worked just fine for me in the past. The above query however returns the same data that is in the NotHeard table whereas if I take out the or NOT EXISTS part it works correctly.
我认为这会起作用，因为我使用了单个 NOT EXISTS 子查询行，并且过去对我来说效果很好。然而，上面的查询返回与 NotHeard 表中相同的数据，而如果我取出 or NOT EXISTS 部分，它可以正常工作。
Any ideas as to what I'm doing wrong or how to do what I'm wanting to do?
关于我做错了什么或如何做我想做的任何想法？
回答by Unreason
Regarding you original query, try
关于您的原始查询，请尝试
NOT (
EXISTS(
Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID)
AND EXISTS(
Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID)
)
which would mean not in either. But that's equal to what you had originally (tested on sample data). Are you sure that you don't mean not in A neither in B? That would be
这意味着两者都不是。但这与您最初拥有的相同（在样本数据上进行测试）。你确定你不是说不在 A 也不在 B 中？那将是
NOT (
EXISTS(
Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID)
OR EXISTS(
Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID)
)
Do realize that the EXISTS solution is using correlated subqueries which might perform worse then LEFT JOIN and NULLs, here's a sample.
请意识到 EXISTS 解决方案正在使用相关子查询，这些子查询的性能可能比 LEFT JOIN 和 NULL 差，这是一个示例。
SELECT NotHeard.UnitID, NotHeard.Address
FROM (NotHeard LEFT JOIN analyzed ON NotHeard.UnitID = analyzed.UnitID)
LEFT JOIN analyzed2 ON NotHeard.UnitID = analyzed2.UnitID
WHERE analyzed.UnitID Is Null OR analyzed2.UnitID Is Null
GROUP BY NotHeard.UnitID, NotHeard.Address;
Notice that I used OR in the condition as compared to Austin's solution, AND would give you neither in analyzed nor in analyzed2.
请注意，与 Austin 的解决方案相比，我在条件中使用了 OR，AND 既不会给你分析，也不会给你分析2。
回答by Austin Salonen
It looks like you want left joins instead. I may be a little off on my Access syntax.
看起来你想要左连接。我的 Access 语法可能有点偏差。
SELECT UnitID, Address
INTO [NotHeardByEither]
FROM [NotHeard]
LEFT JOIN [analyzed] ON [NotHeard].UnitID = [analyzed].UnitID
LEFT JOIN [analyzed2] ON [NotHeard].UnitID = [analyzed2].UnitID
WHERE IsNull([analyzed].UnitID)
AND IsNull([analyzed2].UnitID)
Group BY UnitID, Address
