Snowflake

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_idlast_namefirst_nameaddressphone_noemaildept_id
101SigdelSanjogKathmandu4324721ssg@tqblog.com1
102ShresthaSamishNew York132432ss@tqblog.com2
103GyawaliRupakTokyo0024324rg@tqblog.com3

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_idlast_namefirst_nameaddressphone_no
101SigdelSanjogKathmandu4324721
102ShresthaSamishNew York132432
103GyawaliRupakTokyo0024324

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);