下面哪个查询更快?
不存在:
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE NOT EXISTS (
SELECT 1
FROM Northwind..[Order Details] od
WHERE p.ProductId = od.ProductId)
或不在:
SELECT ProductID, ProductName
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
SELECT ProductID
FROM Northwind..[Order Details])
查询执行计划表示它们都做相同的事情。如果是这样的话，推荐哪种形式?
这是基于北风的数据库。
(编辑)
刚找到这篇有用的文章: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
我想我还是用“不存在”吧。
当前回答
还要注意，当NOT IN为null时，NOT IN并不等同于NOT EXISTS。
这篇文章解释得很好
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
When the subquery returns even one null, NOT IN will not match any rows. The reason for this can be found by looking at the details of what the NOT IN operation actually means. Let’s say, for illustration purposes that there are 4 rows in the table called t, there’s a column called ID with values 1..4 WHERE SomeValue NOT IN (SELECT AVal FROM t) is equivalent to WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1) AND SomeValue != (SELECT AVal FROM t WHERE ID=2) AND SomeValue != (SELECT AVal FROM t WHERE ID=3) AND SomeValue != (SELECT AVal FROM t WHERE ID=4) Let’s further say that AVal is NULL where ID = 4. Hence that != comparison returns UNKNOWN. The logical truth table for AND states that UNKNOWN and TRUE is UNKNOWN, UNKNOWN and FALSE is FALSE. There is no value that can be AND’d with UNKNOWN to produce the result TRUE Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned
其他回答
还要注意，当NOT IN为null时，NOT IN并不等同于NOT EXISTS。
这篇文章解释得很好
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
When the subquery returns even one null, NOT IN will not match any rows. The reason for this can be found by looking at the details of what the NOT IN operation actually means. Let’s say, for illustration purposes that there are 4 rows in the table called t, there’s a column called ID with values 1..4 WHERE SomeValue NOT IN (SELECT AVal FROM t) is equivalent to WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1) AND SomeValue != (SELECT AVal FROM t WHERE ID=2) AND SomeValue != (SELECT AVal FROM t WHERE ID=3) AND SomeValue != (SELECT AVal FROM t WHERE ID=4) Let’s further say that AVal is NULL where ID = 4. Hence that != comparison returns UNKNOWN. The logical truth table for AND states that UNKNOWN and TRUE is UNKNOWN, UNKNOWN and FALSE is FALSE. There is no value that can be AND’d with UNKNOWN to produce the result TRUE Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned
事实上，我相信这是最快的:
SELECT ProductID, ProductName
FROM Northwind..Products p
outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null
如果优化器说它们是相同的，那么就考虑人为因素。我更喜欢看NOT EXISTS:)
它们非常相似，但并不完全相同。
在效率方面，我发现左join is null语句更有效(当要选择大量行时)
在您的特定示例中，它们是相同的，因为优化器已经发现您正在尝试做的事情在两个示例中是相同的。但在非平凡的例子中，优化器可能不会这样做，在这种情况下，有时有理由更喜欢其中一个。
如果在外层选择中测试多行，则首选NOT IN。可以在执行开始时计算NOT IN语句中的子查询，并且可以根据外部选择中的每个值检查临时表，而不是像NOT EXISTS语句那样每次都需要重新运行子选择。
如果子查询必须与外部选择相关联，那么NOT EXISTS可能更可取，因为优化器可能会发现一种简化，可以防止创建任何临时表来执行相同的功能。
