雪花中的横向FLATTEN(..)和表格(FLATTEN)之间的差异

Question

在Snowflake中使用LATERAL FLATTEN(...)和TABLE(FLATTEN(...))有什么区别？我查看了关于FLATTEN、LATERAL和TABLE的文档，无法对以下查询的功能进行区分。

select
id as account_id,
account_regions.value::string as region
from
salesforce.accounts,
lateral flatten(split(salesforce.accounts.regions, ', ')) account_regions

select
id as account_id,
account_regions.value::string as region
from
salesforce.accounts,
table(flatten(split(salesforce.accounts.regions, ', '))) account_regions

Answer 1

我要说的是，在所提供的查询中没有区别，因为横向连接是通过在一行中的值内操作的结果动态创建表来隐含的。

flatten关键字的真正需求来自这样的查询：

select * 
from departments as d
, lateral (
select * 
from employees as e 
where e.department_id = d.department_id
) as iv2
order by employee_id;
-- https://docs.snowflake.com/en/sql-reference/constructs/join-lateral.html

如果此联接没有lateral关键字，则会得到一个Error: invalid identifier 'D.DEPARTMENT_ID'。