在LEFT JOIN的ON条件中使用UNNEST是否合理？如何优化BigQuery查询？

1. 原查询写法的正确性 #

原查询语法是正确的，能实现你需要的关联逻辑，但性能极差，这是导致6小时超时的核心原因。在JOIN的ON条件中直接使用UNNEST(table2.field2)，BigQuery会对table2的每一行实时展开数组再与table1行匹配，相当于执行了笛卡尔积级别的关联，数据量稍大就会触发超时。

2. 优化实现方式 #

方案一：提前展开数组再关联（最推荐） #

先将table2的数组字段展开为扁平结构，再与table1做常规JOIN，BigQuery可利用哈希连接等高效策略大幅提升性能：

SELECT *
FROM table1
LEFT JOIN (
  -- 提前展开table2数组，生成扁平表
  SELECT t2.*, field_val
  FROM table2 t2, UNNEST(t2.field2) AS field_val
) t2_flattened 
ON table1.field1 = t2_flattened.field_val

如果table2数组中存在重复的field_val，导致关联后出现重复行，可在子查询中添加DISTINCT去重：

SELECT *
FROM table1
LEFT JOIN (
  SELECT DISTINCT t2.*, field_val
  FROM table2 t2, UNNEST(t2.field2) AS field_val
) t2_flattened 
ON table1.field1 = t2_flattened.field_val

方案二：使用EXISTS子查询（适合无需返回table2全量字段的场景） #

如果仅需判断table1行是否存在匹配的table2行，或只返回部分table2字段，EXISTS子查询可能更高效：

SELECT t1.*, t2.*
FROM table1 t1
LEFT JOIN table2 t2 
ON EXISTS (
  SELECT 1 FROM UNNEST(t2.field2) f WHERE f = t1.field1
)

不过这种写法的性能优化不如方案一稳定，具体效果取决于BigQuery优化器的执行计划。

优化原理 #

原写法时间复杂度为O(M*N*K)（M为table1行数，N为table2行数，K为数组平均长度），提前展开数组的写法将复杂度降为O(M + N*K)，再通过哈希连接快速匹配，避免了大量重复的数组展开操作，从根本上减少计算量。

内容的提问来源于stack exchange，提问作者Yas