谨慎使用 SQL 的 NOT IN

Use SQL "NOT IN" With Caution

根据我的经验，库/工具/语言中许多出乎意料的错误都可以归咎于缺失值的存在。例如，考虑一个含有 NaN 值的一维 NumPy 数组。

当我们对这个 NumPy 数组进行聚合来计算比如它的总和时，会得到以下输出：

很奇怪，对吧？虽然在这里输出 6 可能更合理，但 NumPy 却产生了一个 NaN 值。好了，这并不是本章的主题，但我希望你能领会到这一点。在 SQL 中同样存在类似的“静默错误”，特别是在 NOT IN 子句的使用上，而很多 SQL 用户并没有意识到这一点。让我们来弄明白吧！

假设我们的数据库中有以下两张表（students 和 names）：

任务是从 students 表中选择 first_name 不在 names 表中的记录。一种做法是使用 NOT IN 子句：

这也能给出预期的结果。现在，假设我们的 names 表里有一个 NULL 值：

如果我们再次运行上面的查询，这次却得不到任何记录：

顺便说一下，如果我们改用 IN 子句，从 students 表中选择 first_name 在 names 表中的行，我们会发现它按预期工作：

这很奇怪，不是吗？

我们使用 NOT IN 时得不到记录、而 IN 子句却能按预期工作的原因，与这两个子句在内部的工作方式有关。

为简单起见，假设我们当前正在检查 students 表中的第一条记录（其中 first_name 为 “John”）：

WHERE 子句需要一个布尔值来决定某条记录是否应当通过过滤（filter）。当我们使用 “IN” 时，这个布尔值是用 OR 运算符按如下方式求值的：

只要有一个条件为 TRUE，该行就会通过过滤。然而，当我们使用 “NOT IN” 时，这个布尔值是用 AND 运算符按如下方式求值的：

要使上面的表达式为 TRUE，所有单个条件都必须为 TRUE。但 (JOHN != None) 这个条件会产生冲突，因为通常该条件的求值结果为 UNKNOWN。因此，整个表达式的求值结果为 UNKNOWN——从而不产生任何记录。这种情况对 students 表中的每条记录都会发生。结果就是，该查询不返回任何记录：

有很多方法可以避免这种情况。由于我们的任务是从 students 表中选择 first_name 不在 names 表中的记录，我们可以：

• 在子查询（subquery）中过滤掉 NULL 值：
• 使用反连接（anti join）：反连接只返回左表中那些在右表里找不到匹配的行。这正是我们这个场景所需要的，其实现如下：
• 有时候，如果你事先不了解这类错误，调试它们会耗费相当长的时间。如果你想动手实验我们讨论的 NOT IN 子句相关内容，可以下载这个 Jupyter Notebook：https://bit.ly/3xVOIXF。