Snowflake Introduces SQL Spread Operator (**)
Snowflake is introducing the SQL spread operator (**), a new construct that allows arrays to be expanded inline as a list of scalar values. Many SQL functions and operators accept a variable number of arguments — whether in an IN clause, functions like COALESCE or LEAST, or in bind variables for SQL Stored Procedures or user-defined functions (UDFs). Handling a varying number of arguments might require techniques that can become cumbersome, introduce security risks and/or cause performance issues.
To address this challenge, we’re launching the SQL spread operator (**). It simplifies query writing, improves efficiency and enhances security when working with SQL queries.
The problem
Consider a common scenario where you need to write a SQL Stored Procedure (or UDF) that accepts a variable list of input values. One typical approach, as shown below, is to extract the individual element in the array into variables and use them as binds in the IN clause. As you might notice, this can quickly become cumbersome and difficult to manage.
--- Extract values from array ----
LET val1 VARCHAR := INPUT_ARRAY[0];
LET val2 VARCHAR := INPUT_ARRAY[1];
LET val3 VARCHAR := INPUT_ARRAY[2];
LET val4.................
LET res RESULTSET := (
SELECT COL1, COL2
FROM MY_TABLE
WHERE COL1 IN (:val1, :val2, :val3, .......)
);Another common approach is to FLATTEN the input array in the query. However, this “parse at runtime” method can degrade performance because the SQL optimizer sees a subquery that must be evaluated at execution time — limiting its ability to prune, optimize or rewrite the query effectively.
LET res RESULTSET := (
SELECT COL1, COL2
FROM MY_TABLE
WHERE COL1 IN (
SELECT VALUE
FROM TABLE(FLATTEN(:INPUT_ARRAY)))
);Other techniques may involve generating placeholders (?) for each value, either statically or dynamically, and then piecing them together into a query string. While these techniques work, they are unwieldy and prone to errors, and worse yet, they can introduce security risks.
Solution: The SQL spread operator (**)
With the new spread operator (**), instead of constructing SQL strings or relying on JSON subqueries, you can now write:
LET res RESULTSET := (
SELECT COL1, COL2
FROM MY_TABLE
WHERE COL1 IN (**:INPUT_ARRAY)
);This approach solves multiple issues at once:
Simplified syntax: No complex string manipulations and awkward workarounds
Performance gains: The spread operator allows the optimizer to work with the expanded values directly, improving query planning and execution
Clean code and readability: Readable and maintainable SQL
Better security: Eliminates the need for string concatenation in query construction
Other use cases
Beyond stored procedures, the spread operator is versatile in many scenarios. Here are some examples:
- Dynamic IN clauses: Easily pass arrays or multiple arrays of values into IN clause.
SELECT *
FROM MY_TABLE
WHERE COL1 IN (**[1,2,3,4], **[5, 6]);- Function arguments: Expand arrays as arguments in SQL functions.
-- Returns default ---
SELECT COALESCE(**[NULL, 'default', 'value']);
-- Returns SnowflakeSQL ---
SELECT CONCAT(**['Snow', 'flake', 'SQL']);
-- Returns 0 ---
SELECT LEAST(**[1, 2, 3], **[0, 100]);- UDFs: In scenarios where you have a UDF that accepts many parameters, it becomes simpler to call when the arguments are already in an array, as shown below.
CREATE OR REPLACE FUNCTION MY_UDF(a FLOAT, b FLOAT, c FLOAT, d FLOAT, e FLOAT)
RETURNS FLOAT
AS
$$
a + b + c + d + e
$$
;
-- Instead of:
SELECT MY_UDF(1, 2, 3, 4, 5 );
-- You can do:
SELECT MY_UDF(**[1, 2, 3], **[4, 5]);Conclusion
The SQL spread operator empowers you to work with arrays in SQL more naturally and efficiently. It bridges the gap between client-side data structures and SQL queries, simplifying your development process while enhancing security. We encourage you to explore the spread operator and experience firsthand how it can simplify your workflows and analytics. Please refer to our documentation to learn more.
Authors
