Exploring Snowflake SQL New Capabilities Using SELECT Statement

Snowflake recently launched new functions that can be used with SELECT statements, which enhances data querying to be more flexible and efficient.

In this article, I will dive into a few of the options focusing on how to leverage new capabilities with SELECT statements using EXCLUDE, Group BY, Rename, ILIKE, etc.

Using EXCLUDE in the SELECT Statement

The EXCLUDE clause allows you to exclude unwanted columns from the result set when using the SELECT * query. This is particularly useful when you are working with a large dataset and want to avoid specific columns or values from the output.

Syntax

SELECT * EXCLUDE (column_name) FROM table_name;

In the example below, I first select all the columns from the table as per the screenshot.

Now, I can avoid column TYPE from the SELECT * statement using the EXCLUDE clause by writing the query as shown in the screenshot below.

SELECT * EXCLUDE TYPE 
FROM DATA.ALL_HOSPITAL_STATIONS;

Using EXCLUDE, the user can also exclude multiple columns in a single query.

SELECT *  
EXCLUDE (col1, col5)  
FROM table_name;

So, using the EXCLUDE clause, you can easily exclude specific columns that easily simplify your queries.

Using GROUP BY ALL in SELECT Statement

With existing grouping functionality, Snowflake has extended the GROUP BY clause to support more complex aggregate scenarios in an easy way.

With the introduction of the new GROUP BY ALL clause, Snowflake now allows you to group all columns in a table except those used in the aggregate function in the SELECT statement. This new clause now saves you from having to explicitly mention every column in the SELECT query.

Syntax

 SELECT * FROM table_name GROUP BY ALL;

In the screenshot below, I selected a few columns from a table and fetched all the data in a single shot.

In the same SQL statement, I aim to calculate the total distance for each city per year. To achieve this, I use the SUM aggregate function on the Distance column and apply GROUP BY ALL without explicitly listing the other columns from the SELECT statement.

Refer to the screenshot below.

GROUP BY ALL can be used with aggregate functions like AVG, COUNT, etc., as you use in a regular GROUP BY clause.

So, this clause is useful when there are many columns to group by, and you don’t want to manually list them all, which makes your query cleaner and easier to write.

Case-insensitive search using ILIKE

Snowflake introduces the ILIKE function, which performs a case-insensitive pattern match. It is similar to the normal LIKE function but simply ignores case differences, which makes the search pattern easier.

ILIKE pattern also works with column search other than searching data in table columns.

In the below example, I tried to search all columns from a table that matches the search pattern mentioned with the ILIKE function.

SELECT * 
FROM RAW.ONETIME_REPORTING 
WHERE COLUMN_NAME ILIKE '%DEST%';

Similarly, the below query will retrieve all employees whose names contain ‘Rahul’, regardless of case sensitivity i.e. upper or lower case.

SELECT * FROM Employee WHERE emp_name ILIKE '%rahul%';

Renaming columns using RENAME in the SELECT statement

Snowflake also introduces the RENAME function, which can help you rename columns directly in your queries.

Syntax

 SELECT * RENAME (column_name) FROM table_name;

Previously, users had to write out every column name as they needed to rename at least a single column when querying data using SELECT * using the AS keyword like below.

SELECT
    Col1,
    Col2,
    Col3 AS col3_new,
    Col4,
    Col5 AS col5_new
FROM table_name;

With the new RENAME functionality, the user can easily select all columns while renaming a few columns directly, as shown below.

SELECT 
    * 
RENAME 
    (Col3 AS col3_new, Col5 AS col5_new) 
FROM 
    table_name;

Conclusion

The above-mentioned EXCLUDE, GROUP BY, ILIKE, and RENAME capabilities are just a few examples of how Snowflake is making it easier to write cleaner, more efficient, and more flexible SQL queries.

I will keep posting on more exciting features from Snowflake in upcoming articles.

Happy learning!