代码之家 › 专栏 › 技术社区 › Felipe Hoffa

我的查询有一个不在,突然停止显示结果

google-bigquery

Felipe Hoffa · 技术社区 · 7 年前

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

请注意,此查询包括 NOT IN 操作人员

1 回复 | 直到 7 年前

Felipe Hoffa 7 年前

NOT IN

MySQL "Not IN" query suddenly stopped returning results

使用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中有空值:

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

推荐文章