A Comprehensive Guide to Snowflake’s ARRAY_INTERSECTION Function
The ARRAY_INTERSECTION function in Snowflake is an array function that allows users to find common elements from two arrays. This utility function can be quite useful when you are trying to identify overlapping values from two different sources.
In this article, we will deep dive into how this function operates, and how you can implement it effectively using a simple example involving a table. We will begin with the creation of a Data Definition Language (DDL) script and populate the table with an insert statement.
The first step in any database operation is the creation of the table using a DDL. For the purpose of this example, let’s create a table named ‘freshers_in’ with two columns ‘interests’ and ‘hobbies’ both of type ARRAY.
CREATE TABLE freshers_in_emp (
id INTEGER,
country ARRAY,
origin_country ARRAY
);Inserting Data into the Table
Next, let’s insert some data into the table using an INSERT statement. We will populate the table with arrays of integers to keep it simple.
INSERT INTO freshers_in_emp (id, country, origin_country)
SELECT 1, ARRAY_CONSTRUCT('China','India','Japan','Vietnam','USA','UK','CANADA'), ARRAY_CONSTRUCT('China','India','AUSTRALIA') UNION ALL
SELECT 2, ARRAY_CONSTRUCT('USA','UK','CANADA'), ARRAY_CONSTRUCT('Japan','Vietnam','USA','UK','CANADA') UNION ALL
SELECT 3, ARRAY_CONSTRUCT('China''Japan','USA','UK','CANADA'), ARRAY_CONSTRUCT('UK','CANADA');
Using the ARRAY_INTERSECTION Function
With the table and data in place, we can now proceed to use the ARRAY_INTERSECTION function. The syntax for this function is quite simple.
ARRAY_INTERSECTION(<array_expression_1>, <array_expression_2>)
This function will return a new array consisting of the common elements from array_expression_1 and array_expression_2. Here is how we can use it with our table ‘freshers_in_emp’:
SELECT
id,
country,
origin_country,
ARRAY_INTERSECTION(country, origin_country) AS common_country
FROM
freshers_in_emp;This SELECT statement will return the id, country, origin_country, and an array of common elements between interests and hobbies. The result of the ARRAY_INTERSECTION function will be represented in a new column named ‘common_elements’.
ID COUNTRY ORIGIN_COUNTRY
1 ["China","India","Japan","Vietnam","USA","UK","CANADA" ] ["China","India","AUSTRALIA" ]
2 ["USA","UK","CANADA" ] ["Japan","Vietnam","USA","UK","CANADA" ]
3 ["China'Japan","USA","UK","CANADA" ] ["UK","CANADA" ]The ARRAY_INTERSECTION function in Snowflake is a powerful tool for identifying common elements between two arrays. Whether you are analyzing data in arrays, lists, or any other collection type, this function can help you streamline your data analysis process. As with any SQL function, practice is key to mastering its application, so be sure to experiment with different inputs to get a feel for how it works.
