BigQuery中UNNEST数组工作原理：为何CROSS JOIN UNNEST仅行级关联

核心原因：你用到的CROSS JOIN UNNEST(field)本质是CROSS JOIN LATERAL的语法糖 #

几乎所有支持数组类型的SQL引擎（PostgreSQL、BigQuery、Spark SQL、Presto等）都会默认给跟在JOIN后的UNNEST隐式添加LATERAL关键字，而LATERAL连接的核心特性就是允许右侧的表表达式引用左侧表当前迭代行的字段，执行逻辑是逐行处理的：

1. 遍历左侧main表的每一行
2. 取当前行的parents字段值传给UNNEST，仅展开这一行的数组得到对应行数的临时表
3. 当前行和这个临时表做交叉连接，得到这一行对应的展开结果
4. 所有行的展开结果合并就是你第一张图的输出：

和全量交叉连接的差异 #

你第二个写法里的derived_from_array是独立的派生表，和左侧main表没有行级关联，执行交叉连接时会直接计算「左表全量行数 × 右表全量行数」的笛卡尔积，2行×4行自然得到8行结果，和逐行展开的逻辑完全不同。

如果要主动实现全量数组元素交叉连接的效果，需要先把全表所有数组元素提前聚合展开，再做关联：

with main as (
    select "David" as name, ["Joan", "Rick"] as parents union all
    select "Kyle" as name, ["Cathy", "Rick"] as parents
),
-- 先展开全表所有parents元素得到独立派生表
all_parents as (
    select parent from main cross join unnest(parents) as parent
)
select name, parent from main cross join all_parents

关于UNNEST的执行逻辑补充 #

UNNEST本身是表函数，没有「判断要不要按当前行展开」的能力，是隐式的LATERAL关键字决定了执行逻辑：如果没有LATERAL修饰，JOIN右侧的表达式无法引用左侧表的字段，直接写CROSS JOIN UNNEST(parents)会直接报parents字段不存在的错误。

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