Lateral Join is a powerful feature in Snowflake that allows you to join a table with a table-valued function (TVF). A TVF is a function that returns a table, and it can take one or more arguments. A Lateral Join is similar to a regular join, but it allows you to join a table with a TVF, which can be very useful in certain scenarios.
To demonstrate how to use Lateral Join in Snowflake, let’s create a sample table called “freshers_in” with the following schema:
CREATE TABLE freshers_in (
id INT,
name VARCHAR(50),
hobbies VARIANT
);
The “freshers_in” table contains information about several freshers, including their IDs, names, and hobbies. The “hobbies” column is a VARIANT data type, which can store an array of values.
Let’s insert some sample data into the “freshers_in” table:
INSERT INTO freshers_in VALUES
(1, 'John Doe', ['reading', 'cooking']),
(2, 'Jane Smith', ['hiking', 'swimming']),
(3, 'Bob Johnson', ['playing guitar', 'painting']),
(4, 'Mary Davis', ['dancing', 'singing']);
Now let’s say we want to find all the freshers who have the hobby “reading”. We could use a Lateral Join with the FLATTEN function, which is a TVF that takes a VARIANT data type and returns a table with one row per array element.
Here’s how we can use Lateral Join to achieve this:
SELECT *
FROM freshers_in
, LATERAL FLATTEN(hobbies) h
WHERE h.value = 'reading';
In this query, we’re using the LATERAL keyword to specify that we want to use a Lateral Join. We’re also using the FLATTEN function with the “hobbies” column to convert the array of hobbies into a table with one row per hobby. Then we’re filtering the results to only include rows where the hobby is “reading”.
The result of this query will be:
+----+---------+----------------------+-------+
| ID | NAME | HOBBIES | VALUE |
+----+---------+----------------------+-------+
| 1 | John Doe | ['reading','cooking'] | reading |
+----+---------+----------------------+-------+
As you can see, we’ve successfully used Lateral Join to join the “freshers_in” table with a TVF (FLATTEN), and we’ve filtered the results to only include rows where the hobby is “reading”.
Lateral Join is a powerful feature in Snowflake that allows you to join a table with a TVF. It can be very useful in scenarios where you need to manipulate complex data types, such as VARIANT or OBJECT. With the help of a Lateral Join, you can easily convert these data types into tables and manipulate them as needed.
Snowflake important urls to refer
