SQL improvements¶

Snowflake is continually introducing enhancements that make it easier to write queries. With these new keywords and functions, you can write simpler, shorter SELECT statements.

SQL improvements in 2024¶

The following SQL improvements were introduced in 2024:

Date released	Improvement	Impact
May 2024	Support for using the TABLE keyword to get a reference to a table, view, secure view, or query and to call a method in a class in the FROM clause.	You can use the TABLE keyword to write simpler SQL statements.
May 2024	New ASOF JOIN construct.	You can write simpler SQL statements to join tables that contain time-series data.
May 2024	Support for specifying the ANY keyword or a subquery with the PIVOT construct.	You can easily pivot on all distinct values or on all values returned by a subquery.
March 2024	New GREATEST_IGNORE_NULLS and LEAST_IGNORE_NULLS functions.	You can return the lowest or highest non-NULL value from a list of expressions.
March 2024	Support for trailing commas in SELECT lists.	You can delete or move the last columns in a SELECT list without having to delete the preceding comma.
February 2024	Support for the `upper`, `lower`, and `trim` collations in additional SQL functions.	You can pass strings that use the `upper`, `lower`, and `trim` collations to these functions without having to change the collation.

SQL improvements in 2023¶

The following SQL improvements were introduced in 2023:

Date released	Improvement	Impact
August 2023	New ARRAY_MIN, ARRAY_MAX, and ARRAY_SORT functions.	You can now easily select the array elements with the lowest value and the highest value. You can easily get a sorted array of elements.
August 2023	New ILIKE and REPLACE parameters in the SELECT command.	You can now select all columns that match a pattern containing SQL wildcards. When selecting all columns, you can replace the value of specific columns with expressions.
July 2023	New ALL keyword in the GROUP BY construct.	You can group results by all non-aggregate columns in the SELECT list without having to specify each column by name.
February 2023	Support for bankers’ rounding (rounding half to even) in the ROUND function.	You can now use bankers’ rounding when rounding values.
January 2023	New MIN_BY and MAX_BY functions.	You can find the row containing the minimum or maximum value in a column and retrieve the value from a different column.

SQL improvements in 2022¶

The following SQL improvements were introduced in 2022:

Date released	Improvement	Impact
November 2022	New EXCLUDE and RENAME parameters in the SELECT command.	You can now select all columns and specify that you want to exclude or rename specific columns.
November 2022	New ARRAY_EXCEPT and ARRAY_DISTINCT functions.	You can now easily select the array elements that are in one array but not in another array. You can easily get the distinct elements in an array.
May 2022	New REGEXP_SUBSTR_ALL function.	You can now easily extract the substrings that match a regular expression from a string..

Date released

Improvement

Impact

November 2022

New EXCLUDE and RENAME parameters in the SELECT command.

You can now select all columns and specify that you want to exclude or rename specific columns.

November 2022

New ARRAY_EXCEPT and ARRAY_DISTINCT functions.

You can now easily select the array elements that are in one array but not in another array.

You can easily get the distinct elements in an array.

May 2022

New REGEXP_SUBSTR_ALL function.

You can now easily extract the substrings that match a regular expression from a string..