sql - BigQuery 标准 SQL 查询返回错误答案

Question

这个查询:

SELECT x 
FROM dataset.table_a 
WHERE x NOT IN (SELECT x FROM dataset.table_b)

返回零记录，即使:

• table_a 中的字段 x 包含 1,326,932 个不同的字符串值

• table_b 中的字段 x 包含 18,885 个不同的字符串值

我不明白为什么。此外，在 BigQuery 遗留 SQL 中，此查询返回正确答案。

Answer 1

我怀疑我知道答案——这是由于在使用遗留 SQL 时对与 NOT IN 相关的 NULL 的不正确处理，而标准 SQL 的行为符合 SQL 标准。有一个 documentation bug open for this to add it to the migration guide但尚未解决。

IN ( https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#in-operators) 的文档指出:

IN with a NULL in the IN-list can only return TRUE or NULL, never FALSE

您可以通过此查询实现所需的行为，使用 NOT EXISTS 代替:

SELECT x
FROM dataset.table_a AS t
WHERE NOT EXISTS (
  SELECT 1 FROM dataset.table_b
  WHERE t.x = x
);