[SOLVED] Difference between LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake

Difference between LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake

What is the difference between the use of LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake? I checked the documentation on FLATTEN, LATERAL and TABLE and cannot make heads or tails of a functional difference between the following queries.

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

Solution

I'll say that in the presented queries there's no difference - as the lateral join is implicit by the dynamic creation of a table out of the results of operating within values coming out of a row.

The real need for the lateral keyword comes out of queries like this:

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

Without the lateral keyword for this join, you get an Error: invalid identifier 'D.DEPARTMENT_ID'.