What is Snowflake Lateral Join and How to use it?

The lateral join in Snowflakes is similar to that of Hive lateral views. Lateral joins in snowflake behaves more like a correlated sub queries than normal snowflake joins. In this article, we will check what is Snowflake lateral join and how to use it.

We will also check the lateral view with FLATTEN keyword and an example.

What is Snowflake Lateral Join?

In Snowflake, lateral keyword allows an in-line view to reference columns from a table expression that precedes that in-line view, but in some cases it may not refer columns from the left table. The output from the Snowflake lateral joins includes only the rows generated from the in-line view or sub query.

Snowflake Lateral Join Syntax

The lateral join can be invoked by using LATERAL keyword.

Following is the lateral join keyword.

SELECT column-1, column-2,... column-n
FROM <left_hand_table_expression>, LATERAL ( <in_line_view> )
...

Where left_hand_table_expression – can be, a user table, view, subquery, table function or the result of an earlier join.

in_line_view – can be, an in-line view, subquery or table functions (FLATTEN or a user defined table functions (UDTF)).

Snowflake Lateral Joins Examples

Following example demonstrate the usage of lateral joins in Snowflake.

Test Data

E_EMP
+-------+----------+-----+-----------------+
| EMPID | LASTNAME | DID | PROJECTNAMES    |
|-------+----------+-----+-----------------|
|   101 | ABC      |   1 | [               |
|       |          |     |   "IT",         |
|       |          |     |   "PROD"        |
|       |          |     | ]               |
|   102 | BCD      |   1 | [               |
|       |          |     |   "PS",         |
|       |          |     |   "PRODSupport" |
|       |          |     | ]               |
|   103 | CDE      |   2 | NULL            |
+-------+----------+-----+-----------------+

E_DEPT
+-----+-------------+
| DID | NAME        |
|-----+-------------|
|   1 | Engineering |
|   2 | Support     |
+-----+-------------+

For example, consider lateral join

select * 
from e_dept as d, lateral (select * from e_emp as e where e.did = d.did) as LV 
order by empid;

+-----+-------------+-------+----------+-----------------+
| DID | NAME        | EMPID | LASTNAME | PNAME           |
|-----+-------------+-------+----------+-----------------|
|   1 | Engineering |   101 | ABC      | [               |
|     |             |       |          |   "IT",         |
|     |             |       |          |   "PROD"        |
|     |             |       |          | ]               |
|   1 | Engineering |   102 | BCD      | [               |
|     |             |       |          |   "PS",         |
|     |             |       |          |   "PRODSupport" |
|     |             |       |          | ]               |
|   2 | Support     |   103 | CDE      | NULL            |
+-----+-------------+-------+----------+-----------------+

Snowflake LATERAL with FLATTEN Table Function

The FLATTEN function is a table function which takes an object or array object and explodes the values into rows. The flatten function produces a lateral view. Flatten function is most commonly used in converting array values to table rows.

For examples, consider an example of creating rows out of PROJECTNAMES columns from the e_emp table. Note that, projectnames columns is an array construct column.

select emp.empid, emp.lastName, index as array_index, value as projectNames 
from e_emp as emp, lateral flatten(input => emp.projectNames) as proj_names 
order by empid;	

+-------+----------+-------------+---------------+
| EMPID | LASTNAME | ARRAY_INDEX | PROJECTNAMES  |
|-------+----------+-------------+---------------|
|   101 | ABC      |           0 | "IT"          |
|   101 | ABC      |           1 | "PROD"        |
|   102 | BCD      |           0 | "PS"          |
|   102 | BCD      |           1 | "PRODSupport" |
+-------+----------+-------------+---------------+

As a result of the flatten tables function, array values are exploded into rows. You can also see he array index of project names.