SQL - IN与NOT IN的区别

Question

假设我有一个表，其中一列的取值范围是1到10。我需要选择除了9和10之外的所有值。如果我使用以下查询语句，是否会有性能方面的差异：

SELECT * FROM tbl WHERE col NOT IN (9, 10)

还有这一个呢？

SELECT * FROM tbl WHERE col IN (1, 2, 3, 4, 5, 6, 7, 8)

- kyooryu

---

“BETWEEN 1 AND 8”可能更可取，但考虑到这样一个小表格，甚至不值得花时间考虑这个问题。你的实际情况是否不同？ - Martin Smith

抱歉，你是正确的。正在删除。 - Royi Namir

@Martin Smith - 我只是把它当作一个理论情况来考虑，我不会使用那些查询语句 :) - kyooryu

根据使用的数据库管理系统（DBMS），性能差异也会很大。始终首先检查执行计划，然后您就会明白。 - user330315

会有性能差异吗？很可能，但哪个更快取决于你使用的SQL服务器，其优化器的优良程度，你的索引，以及它的行统计数据最近更新的时间和准确性以及列值的分布情况。 - Robert McKee

啊，我刚刚注意到表本身并不一定小。它只有10个不同的值。每个值可能有100万行匹配。在这种情况下，如果有一个包含主要列col1的覆盖索引，则我更喜欢使用BETWEEN而不是IN。 - Martin Smith

Answer 1

使用"IN"，因为它很可能会让DBMS使用相应列上的索引。

"NOT IN"理论上也可以被转换为索引使用，但这种方式更加复杂，DBMS可能不会花费额外的开销时间进行使用。

- Serge

---

+1 我刚在 SQL Server 中遇到了这个问题。查询的 IN 版本只有 NOT IN 版本的 1% 成本。此外，NOT IN 查询还抱怨缺失索引。你可能认为优化器应该足够聪明以识别等价性，但显然并不是这样。IN 胜利！ - MarredCheese

Answer 2

关于性能问题，您应该始终对代码进行剖析（即运行查询数千次，并使用某种秒表来测量每个循环的性能。 示例）。

但是在这里，我强烈建议使用第一个查询以便更好地进行未来的维护。逻辑是您需要所有记录，但不包括9和10。如果您将值11添加到表中并使用第二个查询，则应用程序的逻辑将被破坏，这将导致错误。

编辑：我记得这被标记为php语言，所以我提供了php语言的示例，但我可能错了。我猜重写到您所使用的语言不会很难。

- Leri

Answer 3

我发现如果列是可空的，Oracle在优化某些NOT IN查询时会遇到问题。如果您可以两种方式编写查询，则我认为IN更好。

- StilesCrisis

Answer 4

对于常量列表，MySQL 将在内部将您的代码扩展为：

SELECT * FROM tbl WHERE ((col <> 9 and col <> 10))

对于另一个，同样使用 8 次 =。

所以是的，第一个会更快，需要比较的次数更少。然而，可测量的机会几乎可以忽略不计，因为与解析 SQL 和检索数据的一般开销相比，少量常量比较的开销微不足道。

- Niels Keurentjes

---

OP没有告诉我们他们使用的是哪种RDBMS，但据我所知，在MySQL中，使用常量列表的IN操作是通过对列表进行二进制搜索来实现的，而不是8个=比较。 - Martin Smith

即使它确实这样做了（我从不指望MySQL进行任何类型的“高级”优化，最安全的假设），它在理论上仍然会稍微慢一些，因为还需要进行更多的比较。 - Niels Keurentjes

Answer 5

"

IN

"语句在内部的工作方式类似于一系列的"OR"语句。

例如：

SELECT * FROM tbl WHERE col IN (1, 2, 3)

它等于

SELECT * FROM tbl WHERE col = 1 OR col = 2 OR col = 3

"

“OR”语句可能会导致一些性能问题，正如这篇文章所解释的那样： https://bertwagner.com/2018/02/20/or-vs-union-all-is-one-better-for-performance/

当你使用NOT IN语句时，情况都是一样的，但结果具有逻辑否定。但是，您可以编写一个性能更好的等效查询。在您的示例中：

"

SELECT * FROM tbl WHERE col NOT IN (9, 10)

它等于

SELECT * FROM tbl WHERE col <> 9 AND col <> 10

使用“AND”语句时，当所有条件中的一个为假时，数据库停止分析，因此，在“IN”语句中使用“OR”比“AND”在性能上要好得多。

- pablolabin

---

这个参数无效，因为它是双向的：如果前一个条件返回 true，则 AND 链必须继续，而 OR 链则会立即停止。 - Onno Rouast