我的查询有一个NOT IN，并且突然停止显示结果

Question

我有一个每天运行的查询，它突然停止输出结果。它不会输出错误，只是没有结果。

请注意，此查询包含一个NOT IN运算符。

Answer 1

这是NOT IN行为的一个已知问题，请查看它的MySQL版本：

• MySQL"非IN"；查询突然停止返回结果

使用BigQuery:

WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
3

但是如果你在data2:中有一个null

WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2,null]) x
)
SELECT * FROM data
WHERE x NOT IN (SELECT * FROM data2)
# no results

相反，您可以执行LEFT JOIN或NOT EXISTS:

WITH data AS (
SELECT * FROM UNNEST([1,2,3]) x
), data2 AS (
SELECT * FROM UNNEST([1,2]) x
)
SELECT * FROM data a
WHERE NOT EXISTS (SELECT * FROM data2 b WHERE a.x=b.x)
3