在使用SQL中的“NOT IN”时要小心

避免在SQL中使用“NOT IN”的陷阱

+ 确保您不会被抓到的3个简单解决方案

最近,我在Benjamin Thürer大神的博客里看到了一篇很好的文章:

如何避免在Google BigQuery / SQL中犯五个常见错误

多年来,我一直在使用BigQuery,我发现即使是经验丰富的数据科学家也常犯这5个问题

towardsdatascience.com

…这篇文章中,他提醒我们在BigQuery中使用NOT IN SQL子句时要小心。

在这篇文章中,我将通过提供更多示例、解决办法和实践问题来进一步阐述他的观点。

如果您想了解为什么NOT IN子句存在风险,以及如何应对,请继续阅读!

问题:NOT IN对空值的处理不符合您的期望

INNOT IN运算符提供了一种逻辑方式来比较数组。例如,如果您写:

SELECT   3 IN (1, 2, 3) # 输出 = true

BigQuery会返回true。如果您写:

SELECT   3 NOT IN (1, 2, 3) # 输出 = false

BigQuery会返回false

看起来很简单,对吗?但是存在一个问题:INNOT IN在查找数组包含NULL值时会出现奇怪的情况。例如,下面的代码将返回NULL,而不是false

SELECT  3 NOT IN (1, 2, NULL) # 输出 = NULL

为了说明这一点的问题,让我们来看看这三个包含姓名列表的表:

`table_1`      `table_2`      `table_3`+---------+    +---------+    +---------+| name    |    | name    |    | name    |+---------+    +---------+    +---------+| Matt    |    | Matt    |    | Matt    || Sam     |    | Sam     |    | Sam     || Frankie |    +---------+    | NULL    || Ben     |                   +---------++---------+

如果您想要找出在table_1中出现但不在table_2中出现的所有姓名,我们可以使用NOT IN子句:

SELECT nameFROM table_1WHERE name NOT IN (SELECT name FROM table_2)# 输出# +---------+# | name    |# +---------+# | Frankie |# | Ben     |# +---------+

NOT IN运算符允许我们找到两个正确的姓名:“Frankie”和“Ben”。在技术上,这种操作被称为“反半连接”…