SQL: ... WHERE X IN (SELECT Y FROM ...)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2956048/
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: ... WHERE X IN (SELECT Y FROM ...)
提问by CJ7
Is the following the most efficient in SQL to achieve its result:
以下是在 SQL 中最有效地实现其结果的方法：
SELECT *
FROM Customers
WHERE Customer_ID NOT IN (SELECT Cust_ID FROM SUBSCRIBERS)
Could some use of joins be better and achieve the same result?
是否可以更好地使用连接并达到相同的结果？
采纳答案by Martin Smith
One reason why you might prefer to use a JOINrather than NOT INis that if the Values in the NOT INclause contain any NULLs you will always get back no results. If you do use NOT INremember to always consider whether the sub query might bring back a NULL value!
您可能更喜欢使用 aJOIN而不是的一个原因NOT IN是，如果NOT IN子句中的 Values包含任何NULLs，您将始终得不到任何结果。如果您确实使用，NOT IN请记住始终考虑子查询是否可能带回 NULL 值！
RE: Question in Comments
RE：评论中的问题
'x' NOT IN (NULL,'a','b')
≡ 'x' <> NULL and 'x' <> 'a' and 'x' <> 'b'
≡ Unknown and True and True
≡ Unknown
'x' 不在 (NULL,'a','b')
≡ 'x' <> NULL 和 'x' <> 'a' 和 'x' <> 'b'
≡ 未知和真实
≡ 未知
回答by Matti Virkkunen
Any mature enough SQL database should be able to execute that just as effectively as the equivalent JOIN. Use whatever is more readable to you.
任何足够成熟的 SQL 数据库都应该能够像等效的JOIN. 使用任何对你来说更易读的东西。
回答by Neil Knight
SELECT Customers.*
FROM Customers
WHERE NOT EXISTS (
SELECT *
FROM SUBSCRIBERS AS s
JOIN s.Cust_ID = Customers.Customer_ID)
When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, the query can use an index within the sub-query.
使用“NOT IN”时，查询执行嵌套全表扫描，而使用“NOT EXISTS”时，查询可以使用子查询内的索引。
回答by codingbadger
Maybe try this
也许试试这个
Select cust.*
From dbo.Customers cust
Left Join dbo.Subscribers subs on cust.Customer_ID = subs.Customer_ID
Where subs.Customer_Id Is Null
回答by Rob
If you want to know which is more effective, you should try looking at the estimated query plans, or the actual query plans after execution. It'll tell you the costs of the queries (I find CPU and IO cost to be interesting). I wouldn't be surprised much if there's little to no difference, but you never know. I've seen certain queries use multiple cores on our database server, while a rewritten version of that same query would only use one core (needless to say, the query that used all 4 cores was a good 3 times faster). Never really quite put my finger on why that is, but if you're working with large result sets, such differences can occur without your knowing about it.
如果您想知道哪个更有效，您应该尝试查看估计的查询计划，或者执行后的实际查询计划。它会告诉您查询的成本（我发现 CPU 和 IO 成本很有趣）。如果几乎没有区别，我不会感到惊讶，但你永远不会知道。我已经看到某些查询在我们的数据库服务器上使用多个内核，而同一查询的重写版本只会使用一个内核（不用说，使用所有 4 个内核的查询快了 3 倍）。从来没有真正弄清楚为什么会这样，但是如果您正在处理大型结果集，则可能会在您不知情的情况下发生这种差异。
