SQL 如何在 MS Access 中正确使用“不等于”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2210797/
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 "Not Equal" in MS Access?
提问by Bryan
Objective:
客观的：
The intent of this query is to select all of the distinct values in one column that don't exist in a similar column in a different table.
此查询的目的是选择一列中的所有不同值，这些值不存在于不同表的相似列中。
Current Query:
当前查询：
SELECT DISTINCT Table1.Column1
FROM Table2, Table1
WHERE Table1.Column1 <> Table2.Column1
Results From Query:
查询结果：
What happens when I try to run this query is the progress bar fills up almost immediately but then it pretty much freezes and doesn't do anything else as far as I can see. When I use an = sign instead of <> it outputs the values that are equal just fine and if I replace Table2.Column1 with an actual actual value it works just fine.
当我尝试运行此查询时会发生什么情况是进度条几乎立即填满，但随后它几乎冻结并且就我所见没有做任何其他事情。当我使用 = 符号而不是 <> 时，它输出的值正好相等，如果我用实际的实际值替换 Table2.Column1，它就可以正常工作。
I just ran it again while typing this question and the above query gave me an answer this time but it has all of the DISTINCT values for the column not all of the values unique to just that table like it should.
我只是在输入这个问题时再次运行它，上面的查询这次给了我一个答案，但它具有该列的所有 DISTINCT 值，而不是该表所特有的所有值。
Any ideas on what I'm doing wrong or missing here?
关于我在这里做错或遗漏的任何想法？
回答by RBarryYoung
Like this
像这样
SELECT DISTINCT Table1.Column1
FROM Table1
WHERE NOT EXISTS( SELECT * FROM Table2
WHERE Table1.Column1 = Table2.Column1 )
You want NOT EXISTS, not "Not Equal"
你想要不存在，而不是“不相等”
By the way, you rarely want to write a FROM clause like this:
顺便说一句，你很少想像这样写一个 FROM 子句：
FROM Table1, Table2
as this means "FROM allcombinations of every row in Table1 with every row in Table2..." Usually that's a lot more result rows than you ever want to see. And in the rare case that you really do want to do that, the more accepted syntax is:
因为这意味着“从表 1 中每一行的所有组合与表 2 中的每一行......”通常这比您想看到的结果行多得多。在极少数情况下，您确实想这样做，更容易接受的语法是：
FROM Table1 CROSS JOIN Table2
回答by Fionnuala
In Access, you will probably find a Join is quicker unless your tables are very small:
在 Access 中，除非您的表非常小，否则您可能会发现 Join 更快：
SELECT DISTINCT Table1.Column1
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 Is Null
This will exclude from the list all records with a match in Table2.
这将从列表中排除表 2 中具有匹配项的所有记录。
回答by Fred Mason
I have struggled to get a query to return fields from Table 1 that do not exist in Table 2 and tried most of the answers above until I found a very simple way to obtain the results that I wanted.
我一直在努力让查询返回表 2 中不存在的表 1 中的字段，并尝试了上面的大部分答案，直到找到一种非常简单的方法来获得我想要的结果。
I set the join properties between table 1 and table 2 to the third setting (3) (All fields from Table 1 and only those records from Table 2 where the joined fields are equal) and placed a Is Null in the criteria field of the query in Table 2 in the field that I was testing for. It works perfectly.
我将表 1 和表 2 之间的连接属性设置为第三个设置 (3)（表 1 中的所有字段以及表 2 中连接字段相等的那些记录），并在查询的条件字段中放置了一个 Is Null在我正在测试的领域的表 2 中。它完美地工作。
Thanks to all above though.
感谢以上所有。
