Snowflake EXCLUDE: How to Exclude Columns in Queries

The EXCLUDE function in SQL allows you to selectively omit one or more columns from the output of a SELECT statement. This is especially helpful when working with tables that have many columns, and you need most but not all of them.

Syntax

Here’s how to use the EXCLUDE function in a SQL query:


_10SELECT <table_name>.* EXCLUDE (column_name, ...)
_10FROM <table_name>;

Key points

Usage with Multiple Tables: When selecting from multiple tables, use table_name.* to specify that you want to select all columns from a particular table, and then specify the columns to exclude.
Order of Clauses: EXCLUDE should be specified before any RENAME or REPLACE clauses in your query.
Restrictions: You cannot use EXCLUDE on the same column that you are renaming.

Examples

Excluding a Single Column

Suppose we have a table called galactic_travelers that records information about characters and their interstellar travels. If we want to select all details except for the planet of origin, we might write:

Input

Table: galactic_travelers

character_id	name	planet_of_origin	known_associates
1	Arthur Dent	Earth	4
2	Ford Prefect	Betelgeuse	5

Snowflake SQL Query


_10-- Query to exclude planet of origin
_10SELECT gt.* EXCLUDE planet_of_origin 
_10FROM galactic_travelers gt;

Output

character_id	name	known_associates
1	Arthur Dent	4
2	Ford Prefect	5

Excluding Multiple Columns

If we need to exclude both the planet_of_origin and known_associates to focus only on character names and IDs:

Input

Table: galactic_travelers

character_id	name	planet_of_origin	known_associates
1	Arthur Dent	Earth	4
2	Ford Prefect	Betelgeuse	5

Snowflake SQL Query


_10-- Query to exclude multiple columns
_10SELECT gt.* EXCLUDE (planet_of_origin, known_associates) 
_10FROM galactic_travelers gt;

Output

character_id	name
1	Arthur Dent
2	Ford Prefect

Excluding a Column and Renaming Columns

We can further refine our ouput, for example if we we want to exclude the known_associates column and rename character_id to id:

Input

Table: galactic_travelers

character_id	name	planet_of_origin	known_associates
1	Arthur Dent	Earth	4
2	Ford Prefect	Betelgeuse	5

Snowflake SQL Query


_10-- Query to exclude one column and rename another
_10SELECT gt.* EXCLUDE known_associates RENAME (character_id AS id) 
_10FROM galactic_travelers gt;

Output

id	name	planet_of_origin
1	Arthur Dent	Earth
2	Ford Prefect	Betelgeuse

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started