Select all columns except a few specific column in snowflake [New Feature]. 

We all know that SELECT * FROM table_name gives us all the columns from table but sometimes we need to select all the columns except a few columns. One of the easiest ways is to manually specify columns and exclude unwanted columns, like:

SELECT column_1,column_2, column_3,...column_n FROM table_name;

This is really time consuming and boring if the table has more than 5/6  columns. There are few alternative ways such as creating a view/temporary table excluding unwanted columns or Select the column querying from information_schema. 

These are manual ways and require lots of effort but supported by most of the traditional databases. In snowflake we can achieve it very easily by adding the EXCLUDE clause within the SELECT statement as shown below. 

Syntax:

SELECT * EXCLUDE (column_1_to_exclude, column_2_to_exclude,….column_n_to_exclude) FROM table_name;

Example:

Suppose we have a employee table as below and need to retrieve all the columns except dept_id and email :

employee:

employee_id	last_name	first_name	address	phone_no	email	dept_id
101	Sigdel	Sanjog	Kathmandu	4324721	ssg@tqblog.com	1
102	Shrestha	Samish	New York	132432	ss@tqblog.com	2
103	Gyawali	Rupak	Tokyo	0024324	rg@tqblog.com	3

In this case we can select all the column except dept_id and email by adding them inside exclude clause as below:

SELECT * EXCLUDE (email, dept_id) FROM employee;

[Output]:

Which gives the result as below:

employee_id	last_name	first_name	address	phone_no
101	Sigdel	Sanjog	Kathmandu	4324721
102	Shrestha	Samish	New York	132432
103	Gyawali	Rupak	Tokyo	0024324

Setting data for above example 

create table employee (
         employee_id integer,
         last_name varchar,
         first_name varchar,
         address varchar,
         phone_no double,
         email varchar,
         dept_id integer
    );

insert into employee (employee_id, last_name, first_name,address,phone_no,email,dept_id) 
values (101, 'Sigdel', 'Sanjog','kathmandu',4324721,'ssg@tqblog.com' 1),
    (102, 'Shrestha', 'Samish','New York',132432,'ss@tqblog.com', 2),
    (103, 'Gyawali', 'Rupak', 'Tokyo',0024324,'rg@tqblog.com',3);