In Snowflake, the Select * statement is a quick way to retrieve all columns from a table. However, there may be times when you want to exclude certain columns from the Select. In this article we will discuss the options that we have for doing it.

For doing it you will have to use the exclude function.

Step 1. Using the exclude function

Exclude function helps us to bannish columns from a select * statement.

select * exclude (col1,col2,...,coln)

For example we were making comparisons in two datasets on an article on How To Compare Two Tables In Snowflake.

The first dataset had the following information

The second dataset had this information

For example if we wanted to make a comparison of both datasets without ‘EMAIL’ column without using the exclude function we would need to run the following query

SELECT EMPLOYEE_ID
      ,FIRST_NAME
      ,LAST_NAME
      ,DEPARTMENT
FROM TEST.TEST.EMPLOYEE
EXCEPT 
SELECT EMPLOYEE_ID
      ,FIRST_NAME
      ,LAST_NAME
      ,DEPARTMENT
FROM TEST.TEST.EMPLOYEE_012923;

Then if we are using exclude function this would be much easier as you will only need to call the following sql query.

SELECT * EXCLUDE(EMAIL)
FROM TEST.TEST.EMPLOYEE
EXCEPT 
SELECT * EXCLUDE(EMAIL)
FROM TEST.TEST.EMPLOYEE_012923;

Both queries are going to return the same result.

Conclusion

This function was built in order to help you to save time by avoiding writing all column names when there is no need of doing it, Imagine if you had a table with 100 columns, you will need to mention all the columns and the query would be longer due column names.