Snowflake
Exclude Columns from Queries

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:


_10
SELECT <table_name>.* EXCLUDE (column_name, ...)
_10
FROM <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_idnameplanet_of_originknown_associates
1Arthur DentEarth4
2Ford PrefectBetelgeuse5

Snowflake SQL Query


_10
-- Query to exclude planet of origin
_10
SELECT gt.* EXCLUDE planet_of_origin
_10
FROM galactic_travelers gt;

Output

character_idnameknown_associates
1Arthur Dent4
2Ford Prefect5

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_idnameplanet_of_originknown_associates
1Arthur DentEarth4
2Ford PrefectBetelgeuse5

Snowflake SQL Query


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

Output

character_idname
1Arthur Dent
2Ford 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_idnameplanet_of_originknown_associates
1Arthur DentEarth4
2Ford PrefectBetelgeuse5

Snowflake SQL Query


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

Output

idnameplanet_of_origin
1Arthur DentEarth
2Ford PrefectBetelgeuse
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free