Snowflake SELECT Statement Unique Features

By: Sergey Gigoyan | Updated: 2023-09-28 | Comments | Related: More > Snowflake

Problem

This article addresses the SQL SELECT statement and sheds light on some of its unique features in Snowflake.

Solution

The syntax of Snowflake's SELECT statement provides some flexibilities that are not available in SQL Server and can be very useful for developers. Let's discuss some of them in detail.

The code below creates a test environment:

CREATE DATABASE TESTDB;
 
CREATE SCHEMA TESTSCHEMA;
 
CREATE TABLE TESTSCHEMA.Item
( ItemID INT,
  ItemCode STRING,
  ItemPrice NUMBER
);
 
CREATE TABLE TESTSCHEMA.SALES (
    SalesID INT,
    ItemID INT NOT NULL,
    SoldDate DATE NOT NULL
);
 
--Inserting data into the Item table
INSERT INTO TESTSCHEMA.Item (ItemID, ItemCode, ItemPrice) VALUES (1, 'A0010', 17455.2900);
INSERT INTO TESTSCHEMA.Item (ItemID, ItemCode, ItemPrice) VALUES (2, 'B0020', 24500.0000);
INSERT INTO TESTSCHEMA.Item (ItemID, ItemCode, ItemPrice) VALUES (3, 'C0030', 12450.3200);
INSERT INTO TESTSCHEMA.Item (ItemID, ItemCode, ItemPrice) VALUES (4, 'D0040', 37784.0000);
INSERT INTO TESTSCHEMA.Item (ItemID, ItemCode, ItemPrice) VALUES (5, 'E0050', 128000.0000);
INSERT INTO TESTSCHEMA.Item (ItemID, ItemCode, ItemPrice) VALUES (6, 'F0060', 92000.0000);
 
--Inserting data into the Sales table
INSERT INTO TESTSCHEMA.Sales (SalesID, ItemID, SoldDate) VALUES (1, 1, '2016-01-20');
INSERT INTO TESTSCHEMA.Sales (SalesID, ItemID, SoldDate) VALUES (2, 1, '2016-02-22');
INSERT INTO TESTSCHEMA.Sales (SalesID, ItemID, SoldDate) VALUES (3, 3, '2016-03-17');
INSERT INTO TESTSCHEMA.Sales (SalesID, ItemID, SoldDate) VALUES (4, 5, '2016-04-01');
INSERT INTO TESTSCHEMA.Sales (SalesID, ItemID, SoldDate) VALUES (5, 3, '2017-01-03');

Including Only Columns Matching a Specific Pattern – ILIKE Parameter

The ILIKE parameter in Snowflake's SELECT statement allows only columns matching the provided pattern to be included in the statement.

For example, if we want to retrieve only "ID" columns from the Sales table, we can use the code below:

SELECT * ILIKE '%id%' 
FROM Sales;

This will return only the SalesID and ItemID columns:

The SELECT statement in SQL Server does not support this kind of parameter.

Exclude Columns from the SELECT – EXCLUDE Parameter

Have you ever encountered a situation where a table contains a dozen columns, and you need to include most of them except one or two? In such cases, listing all columns except the specific ones becomes necessary, rather than using SELECT *. Snowflake offers a solution to this problem through the EXCLUDE parameter. With this feature, you can apply SELECT * and exclude unnecessary columns.

For instance, if you aim to retrieve all columns of the Item table while excluding only the "ItemPrice" column, you can achieve this using the following code:

SELECT * EXCLUDE ItemPrice
FROM Item;

We can also exclude multiple columns:

SELECT * EXCLUDE (ItemPrice, ItemCode)
FROM Item;

Renaming Columns – RENAME Parameter

We can rename one or more columns when we select all columns using the asterisk:

SELECT * RENAME ItemCode as Code
FROM Item;

Here is another example:

SELECT * RENAME (ItemCode as Code, ItemPrice as Price)
FROM Item;

Replacing Column Values – REPLACE Parameter

Similarly, we can replace the values of columns by using the REPLACE parameter:

SELECT * REPLACE (ROUND(ItemPrice,0) AS ItemPrice)
FROM Item;

It is worth mentioning that in SQL Server, as well as in Snowflake, we can rename columns and replace column values just using the following syntax:

SELECT ItemPrice AS OriginalItemPrice, ROUND(ItemPrice,0) AS RoundedItemPrice
FROM Item;

However, if we need to select all columns, we either need to list all the column names one by one in the SELECT statement or add extra (modified) columns on top of all of them (which is redundant). In contrast, the RENAME and REPLACE parameters provide the flexibility of operating on specific columns when all columns are selected via asterisk without the need to list all columns manually for just renaming or replacing the values of some of them.

Note: All the parameters mentioned above work only when SELECT * is used for retrieving all columns from the table.

Combining Syntax

In addition, these parameters can be combined in one SELECT * statement with limitations. For example, EXCLUDE and ILIKE cannot be used together. ILIKE, as well as EXCLUDE, can be used with RENAME or REPLACE, but in that case, ILIKE (EXCLUDE) should be specified first. If we use RENAME and REPLACE together, we should specify REPLACE first.

As an illustration, let's consider a scenario where we combine EXCLUDE, RENAME, and REPLACE within a single statement:

SELECT * EXCLUDE SalesID REPLACE (ROUND(ItemPrice,0) AS ItemPrice) RENAME SOLDDATE AS Date_Sold
FROM
Sales s
INNER JOIN
Item i
ON s.ItemID=s.ItemID

These parameters are also helpful when we join tables, and from the final combined list of columns, we want to exclude shared columns, for example:

SELECT s.* ILIKE '%ID%', i.* EXCLUDE ItemID
FROM
Sales s
INNER JOIN
Item i
ON s.ItemID=s.ItemID

Note: In the example above, ILIKE and EXCLUDE are present in the same SELECT statement, but they are used for separate tables rather than applied to the same table.

Conclusion

In conclusion, the Snowflake SELECT statement introduces some parameters not found in SQL Server's SELECT statement. These parameters can be very handy. For SQL developers switching to Snowflake, becoming familiar with these options is advantageous, as they can significantly simplify the usage of SELECT statements.

Next Steps

For additional information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-09-28

Snowflake SELECT Statement Capabilities