NOT IN 与 NOT EXISTS
在编程界，有一个常用的术语称为“反模式（anti-pattern）”。它指的是解决反复出现的问题的方法，这种方法不仅无效，而且有可能适得其反。这个术语最初是由计算机程序员 Andrew Koenig 于 1995 年在他的《设计模式》一书中创造的，作为被认为既可靠又有效的设计模式的相反词。
尽管 SQL 并不是真正的编程语言，但事实证明它同样容易受到反模式的影响，尤其是当问题的查询相当复杂时。有时错误很难发现，直到查询用于生产环境时才会暴露出来。
为了尽早发现 SQL 错误，接下来的几篇文章将重点介绍一些最常见的错误。我们将使用 MySQL 来作为今天的示例，但这些概念在任何类型的 SQL中都同样有效。
NOT IN 与 NOT EXISTS
一种常见类型的 SELECT 查询可以检索未包含在值列表中的数据。为了说明，这里有两个在 Navicat for MySQL 16 中创建的非常简单的表。第一个表包含颜色数据：
第二个表包含产品数据：
我们想要做的是选择所有尚未与任何产品关联的颜色。换句话说，我们需要构造一个查询，它只返回那些产品没有的颜色。我们可以尝试使用 NOT IN 谓词来获取相关记录。
我们预计以下查询返回两行（“black”和“green”），而实际上查询返回的是一个空白结果集：
哪里有问题？products 表的 color 列中存在 NULL 值，由 NOT IN 谓词转换为：
color NOT IN (Red, Blue, NULL)
或
NOT(color=Red OR color=Blue OR color=NULL)
表达式“color=NULL”的计算结果为 UNKNOWN，许多数据库开发人员忽略的是，NOT UNKNOWN 的计算结果也为 UNKNOWN！结果，所有行都被过滤掉，查询会返回零行。
如果需求发生变化，这个问题也会浮出水面，并且不可为 NULL 的列会被更新为允许 NULL。因此，即使列在初始设计中不允许使用 NULL，以防万一你也应该确保查询在使用 NULL 时可继续正常工作。
最简单的解决方案是使用 EXISTS 谓词而不是 IN：
问题解决了！
那么为什么会这样呢？IN 关键字将比较相应子查询列中的所有值，而 EXISTS 则评估真或假。因此，使用 IN 运算符时，SQL 引擎将扫描从内部查询获取的所有记录。另一方面，如果我们使用 EXISTS，SQL 引擎将在找到匹配项后立即停止扫描过程。
总结
在有关常见的 SQL 查询错误的第一部分中，我们了解了 SELECT 查询中如何出现反模式，首先是错误使用 NOT IN 谓词。
如果你想试用 Navicat 16 for MySQL，你可以在这里下载 14 天试用版。
