Select all columns except one or multiple in Snowflake

Learn how to write SQL queries that select all columns in Snowflake except for one or more, by using the exclude and rename functions.

Stephen Allwright

10 Jan 2023

👉

This post will go through the following:

1. What is Snowflake's exclude function
2. How to use exclude to select all but one column
3. How to use exclude to select all but two or more columns
4. How to give columns aliases whilst using exclude

The examples in this post will use the following table, which is called orders

order_id	customer_id	order_value	number_products	date
1528	4920	98	4	2023-01-06
1529	6590	156	7	2023-01-06
1530	2408	25	2	2023-01-07

What is Snowflake's exclude function?

In order to select all but one or more columns in your query, you need to use the exclude function. This allows you to define columns that you do not want to be returned from a select statement.

The syntax for this function is as follows:

select * exclude (<col_1>, <col_2>, ...) ...

Use exclude to select all columns except one

If you want to return all columns except for customer_id, then run the following SQL query:

select * exclude customer_id from orders;

Which would return the following:

order_id	order_value	number_products	date
1528	98	4	2023-01-06
1529	156	7	2023-01-06
1530	25	2	2023-01-07

💡

Previously, to get this result you would have had to write out every column you wanted to return.

This method is fine for a small table like ours, but when you have tens or hundreds of columns, it becomes tedious. So, the exclude function is a very welcome addition.

Select all columns except two or multiple using exclude

If you want to return all columns except order_id, customer_id, and number_products , all you need to change from the previous example is to wrap the column names in brackets:

select * exclude (order_id, customer_id, number_products) from orders;

Which would return the following data:

order_value	date
98	2023-01-06
156	2023-01-06
25	2023-01-07

How to use column aliases whilst using exclude

A logical question that arises from this exclude functionality is how one uses column aliases. This has thankfully been handled by an additional function called rename which was released alongside exclude.

Essentially, rename makes it possible to create column aliases when you're not calling columns individually.

The rename function has the following syntax:

select * rename (<col_1> as <col_alias>, <col_2> as <col_alias>, ...) ...

Use rename and exclude together

To illustrate how these functions work together, let's select all columns except customer_id and order_id , whilst giving aliases to date and order_value.

The SQL query would look like this:

select * exclude (customer_id, order_id) rename (date as order_date, order_value as order_value_usd) from orders;

💡

You always need to call the exclude function before rename

This would result in the following data:

order_value_usd	number_products	order_date
98	4	2023-01-06
156	7	2023-01-06
25	2	2023-01-07

Summary of using exclude and rename functions in Snowflake

Let's summarise what we have looked at in this post:

Select all columns except one or more:

select * exclude (order_id, customer_id) from orders;

Select all columns except some and give some columns an alias:

select * exclude (order_id, customer_id) rename (date as order_date) from orders;

How to create a table from a select query in Snowflake
How to create or replace a table in Snowflake
How to create a view in Snowflake

References

Snowflake EXCLUDE and RENAME release notes
Snowflake SELECT documentation

Snowflake

Stephen Allwright Twitter

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.