SQL NOT EXISTS 与 NOT IN 与 LEFT JOIN WHERE IS NULL 之间有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2246772/
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
What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?
提问by froadie
It seems to me that you can do the same thing in a SQL query using either NOT EXISTS, NOT IN, or LEFT JOIN WHERE IS NULL. For example:
在我看来，您可以使用 NOT EXISTS、NOT IN 或 LEFT JOIN WHERE IS NULL 在 SQL 查询中做同样的事情。例如：
SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)
SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)
SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL
I'm not sure if I got all the syntax correct, but these are the general techniques I've seen. Why would I choose to use one over the other? Does performance differ...? Which one of these is the fastest / most efficient? (If it depends on implementation, when would I use each one?)
我不确定我的所有语法是否正确，但这些是我见过的一般技术。为什么我会选择使用一种而不是另一种？性能有区别吗...？其中哪一个是最快/最有效的？（如果这取决于实现，我什么时候使用每一个？）
采纳答案by Quassnoi
In a nutshell:
简而言之：
NOT INis a little bit different: it never matches if there is but a single NULLin the list.
NOT IN有点不同：如果NULL列表中只有一个，它永远不会匹配。
In
MySQL,NOT EXISTSis a little bit less efficientIn
SQL Server,LEFT JOIN / IS NULLis less efficientIn
PostgreSQL,NOT INis less efficientIn
Oracle, all three methods are the same.
在
MySQL,NOT EXISTS效率稍低在
SQL Server，LEFT JOIN / IS NULL效率较低在
PostgreSQL，NOT IN效率较低在 中
Oracle，所有三种方法都是相同的。
回答by Guffa
If the database is good at optimising the query, the two first will be transformed to something close to the third.
如果数据库擅长优化查询，那么前两个会转化为接近第三个的东西。
For simple situations like the ones in you question, there should be little or no difference, as they all will be executed as joins. In more complex queries, the database might not be able to make a join out of the not inand not existsqueryes. In that case the queries will get a lot slower. On the other hand, a join may also perform badly if there is no index that can be used, so just because you use a join doesn't mean that you are safe. You would have to examine the execution plan of the query to tell if there may be any performance problems.
对于像您提出的问题这样的简单情况，应该几乎没有区别，因为它们都将作为连接执行。在更复杂的查询中，数据库可能无法连接not in和not exists查询。在这种情况下，查询会变慢很多。另一方面，如果没有可以使用的索引，连接也可能表现不佳，因此仅仅因为您使用连接并不意味着您是安全的。您必须检查查询的执行计划以判断是否可能存在任何性能问题。
回答by onedaywhen
Assuming you are avoiding nulls, they are all ways of writing an anti-joinusing Standard SQL.
假设您要避免空值，它们都是使用标准 SQL编写反连接的方法。
An obvious omission is the equivalent using EXCEPT:
一个明显的遗漏是等效的使用EXCEPT：
SELECT a FROM table1
EXCEPT
SELECT a FROM table2
Note in Oracle you need to use the MINUSoperator (arguably a better name):
请注意，在 Oracle 中您需要使用MINUS运算符（可以说是更好的名称）：
SELECT a FROM table1
MINUS
SELECT a FROM table2
Speaking of proprietary syntax, there may also be non-Standard equivalents worth investigating depending on the product you are using e.g. OUTER APPLYin SQL Server (something like):
说到专有语法，根据您OUTER APPLY在 SQL Server中使用的产品（例如），可能还有值得研究的非标准等效项：
SELECT t1.a
FROM table1 t1
OUTER APPLY
(
SELECT t2.a
FROM table2 t2
WHERE t2.a = t1.a
) AS dt1
WHERE dt1.a IS NULL;
回答by baleks
When need to insert data in table with multi-field primary key, consider that it will be much faster (I tried in Access but I think in any Database) not to check that "not exists records with 'such' values in table", - rather just insert into table, and excess records (by the key) will not be inserted twice.
当需要在具有多字段主键的表中插入数据时，考虑到它会快得多（我在 Access 中尝试过，但我认为在任何数据库中）不要检查“表中不存在具有‘此类’值的记录”， - 而只是插入到表中，多余的记录（通过键）不会被插入两次。
回答by Lahiru Cooray
Performance perspective always avoid using inverse keywords like NOT IN, NOT EXISTS, ... Because to check the inverse items DBMS need to runs through all the available and drop the inverse selection.
性能角度始终避免使用反向关键字，如 NOT IN、NOT EXISTS、... 因为要检查反向项目 DBMS 需要遍历所有可用项并删除反向选择。
