在 Google BigQuery 标准 SQL 中，NOT IN 不起作用。

Question

我正在使用Google BigQuery，尝试从'table2'中查找'excluding'在'table1'中存储了2次或更多次的'userid'。

这是代码：

#standardSQL
WITH t100 AS (
SELECT count_table.userid 
From(
SELECT userid,COUNT(`project.dataset.table1`.userid) as notification_count 
FROM `project.dataset.table1`
 GROUP BY userid) as count_table 
where notification_count >= 2 
)

SELECT userid FROM `project.dataset.table2` WHERE userid NOT IN (SELECT userid  FROM t100)

问题在于这个语句返回的是'table1'中存储了两次或更多次的'userid'，我尝试将WHERE userid IS NOT NULL添加到SELECT userid FROM t100，但没有任何变化。为了让一切更清晰，SELECT userid FROM t100不是空的，由于某种原因，在上述第一个代码的结果中仍然显示了返回的结果。

- jaafar Nasrallah

---

在FROM t100之后添加“where user_id is not null and notification_count >= 2”。 - smoore4

Answer 1

由于空值处理的原因。我们的 问题跟踪器 上有一篇类似的帖子，关于NOT IN和NOT EXISTS的区别。IN文档中说明：

在IN列表中包含NULL时，只能返回TRUE或NULL，永远不会返回FALSE

要实现您想要的语义，您应该使用反半连接（NOT EXISTS）。例如：

#standardSQL
WITH t100 AS (
  SELECT
    userid,
    COUNT(userid) as notification_count 
  FROM `project.dataset.table1`
  GROUP BY userid
  HAVING notification_count >= 2 
)
SELECT userid
FROM `project.dataset.table2` AS t2
WHERE NOT EXISTS (SELECT 1 FROM t100 WHERE userid = t2.userid);

- Elliott Brossard

---

这是我的第一个想法，但OP指出：“我尝试将 WHERE userid IS NOT NULL 添加到 SELECT userid FROM t100 中，但没有任何区别”。 - oulenz

要么在那个版本的查询中还有其他错误，要么就是存在一个错误，但这似乎不太可能。 - Elliott Brossard

你可以查看Mikhail Berlyant的回答，不知何故它起作用了，我的原始查询和你的回答以及第一个回答都没有起作用，无论如何还是谢谢。 - jaafar Nasrallah

Answer 2

我尝试在SELECT userid FROM t100中添加WHERE userid IS NOT NULL，但没有任何影响。这当然没有作用，因为当你使用COUNT(userid) as notification_count时，userid为NULL时总是返回0，因此被HAVING notification_count >= 2过滤掉了。
如果你使用COUNT(1)，那么你可能会在t100的输出中得到空用户ID。所以，userid为NULL在这里绝对不是一个问题。
正如其他人指出的那样，你的查询应该可以工作。所以如果你仍然遇到问题，你需要更深入地研究这个问题并向我们提供更多细节。
同时，尝试以下作为你查询的另一个版本（看起来很好）。

#standardSQL
WITH t100 AS (
  SELECT userid
  FROM `project.dataset.table1`
  GROUP BY userid
  HAVING COUNT(userid) >= 2 
)
SELECT userid
FROM `project.dataset.table2` AS t2
LEFT join t100 ON t100.userid = t2.userid
WHERE t100.userid IS NULL

- Mikhail Berlyant

---

这个有效了，我不知道为什么我的查询不起作用，或者为什么其他答案中包含的查询也不起作用。但是谢谢。 - jaafar Nasrallah

Answer 3

我不确定为什么这个不起作用，但基本原则上，我从不在选择语句中使用(not) in。相反，我会使用左外连接子查询并在其中过滤null值：

#standardSQL

with t100 as (
select
  count_table.userid

from(
select
  userid
  ,count(`project.dataset.table1`.userid) as notification_count 

from `project.dataset.table1`

group by
  userid
) as count_table 

where notification_count >= 2 
)

select
  t2.userid as userid

from `project.dataset.table2` t2
left outer join t100
  on t100.userid = t2.userid

where t100.userid is null

- oulenz