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

Question

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

Sergeant

Answer 1

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'.

Bolling

Answer 2

Both are working almost same

lets try with below example

1. creating a table in snowflake with semi structure(json) data

---

/* created a table to perfrom query */
create or replace table gs_table_customer as
select column1 as cust_id, parse_json(column2) as cust_details
from values
(29711,'{ name:  [{ first: "Ram", last: "Ji"}],
 contact: [
 { business:[
   { type: "phone", content:"123-3210" },
   { type: "email", content:"[email protected]" } ] } ] }'),
(29722,'{ name:[{ first: "Hanuman", last: "Ji"}],
 contact: [
 { business:[
   { type: "phone", content:"234-4321" },
   { type: "email", content:"[email protected]" } ] } ] }') v;

2. Verify result set

---

/* Verify result */
select * from gs_table_customer

3. Example 1 - Difference between LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake

---

/* query to fetch contact details via lateral flatten */
 SELECT cust_id as "ID",
--f.value AS "Contact",
f1.value:type AS "Type",
f1.value:content AS "Details"
FROM gs_table_customer tbl,
lateral flatten(input => tbl.cust_details, path => 'contact') f,
lateral flatten(input => f.value:business) f1;

/* query to fetch contact details via TABLE(FLATTEN) */
SELECT cust_id as "ID",
   --f.value AS "Contact",
   f1.value:type AS "Type",
   f1.value:content AS "Details"
 FROM gs_table_customer tbl,
   TABLE(FLATTEN(input => tbl.cust_details, path => 'contact')) f,
   TABLE(FLATTEN(input => f.value:business)) f1;

4. Example 2 - Difference between LATERAL FLATTEN(...) and TABLE(FLATTEN(...)) in Snowflake

---

/* query to fetch name details via lateral flatten */
 SELECT cust_id as "ID",
   --f.value AS "name_details",
   f.value:first,
   f.value:last
 FROM gs_table_customer tbl,
   lateral flatten(input => tbl.cust_details, path => 'name') f

/* query to fetch name details via TABLE(FLATTEN()) */
 SELECT cust_id as "ID",
   --f.value AS "name_details",
   f.value:first,
   f.value:last
 FROM gs_table_customer tbl,
      TABLE(FLATTEN(input => tbl.cust_details, path => 'name')) f 

Perpend