“not exists”和“not in”，哪一个性能更好？

Question

select objectid
      ,name
      ,address
 from library_t l 
 where not exists (select *
                     from d107 d 
                    where l.objectid = d.objectid
                   )

select objectid
      ,name
      ,address
  from library_t l 
  where l.objectid not in (select objectid 
                             from d107
                          )

如果两个objectid列都有索引，那么在Oracle中哪个更好？

- kyleqian

---

除了速度之外，您应该知道它们在每种情况下都不会产生相同的结果集。您不能在每种情况下都将它们用作替代品。 - Ertunç

Answer 1

使用NOT EXISTS。

当前执行计划可能相同，但如果将来更改任一列以允许 NULL ，则 NOT IN 版本将需要执行更多工作。

关于 NOT EXISTS 和 NOT IN 最重要的一点是，与 EXISTS 和 IN 不同，在涉及 NULL 时它们并不等价。具体地说，当子查询返回一个空值时，NOT IN 将不匹配任何行。

- John Woo

Answer 2

[NOT] IN和[NOT] EXISTS操作符的处理方式不同。[NOT] IN更像一个连接，而[NOT] EXISTS更像一个带有IF条件的循环。当然，选择哪个操作符取决于数据驱动查询和被驱动查询返回的数据量。在[NOT] IN运算符的情况下，内部查询(..where id in (select id from table )))是一个驱动查询，而在[NOT] EXISTS的情况下，外部查询是一个驱动查询。因此，如果子查询(内部查询)由于子查询中包含的行数较少或应用了强烈的过滤而返回少量数据，[NOT] IN运算符可能会提供更好的性能。如果子查询返回大量的数据或主要的过滤发生在外部查询中，则更适合使用[NOT] EXISTS运算符。

- Nick Krasnov

Answer 3

他们都执行了一个子查询。更快的方法是:

select lib.objectid, lib.name, lib.address
from library_t lib
     left outer join d107 dd on lib.objectid = dd.objectid
where dd is null

- Marlin Pierce

---

这个问题是关于SELECT而不是DELETE，所以我的答案不应该是DELETE。对于DELETE，我会使用子查询DELETE FROM library_t lib_del WHERE lib_del.objectId in (select lib.objectID from ...). 在这里使用子查询的区别在于它不是相关子查询。问题中的示例需要通过子查询的结果来查找不在子查询中的内容，数据库优化器可能无法找到最佳方法来执行此操作。使用上述DELETE，大多数情况下它会找到最佳的方式来执行DELETE。 - Marlin Pierce

是的，你说得对，这个问题是关于select语句的。我正在研究最干净的删除方法，阅读了几篇其他帖子，并在阅读你的答案时仍在考虑我的原始问题。我已经为你的答案点赞，因为我认为它是最干净和最好的解决方案（适用于select和delete）。我在我的删除查询中应用了左连接，在DELETE关键字后指定表名后，它很好地工作了，就像左连接在我之前编写的许多select查询中一样。我已经删除了我的原始评论，因为它与主题无关。 - user3308241