As a data analyst, it is always a good practice to only select required columns instead of select all columns (SELECT *). This is even more important when querying systems like BigQuery which charges based on the amount of data read from storage for each query (Analysis Pricing).
However, sometimes it comes to handy if you only want to exclude certain columns from the result set. To do this, BigQuery provides an EXCEPT clause. This saves time to type the long list of column names in a SELECT statement.
SELECT \* EXCEPT example
The following code snippet select all the columns except column CustomerIDfrom dim_customertable.
SELECT
* EXCEPT (CustomerID)
FROM
`test`.dim_customer
The output looks similar to the following screenshot:
Exclude multiple columns
We can also exclude multiple columns in EXCEPT clause:
SELECT
* EXCEPT (IsCurrent, StartDate, EndDate)
FROM
`test`.dim_customer
The result won't include column IsCurrent, StartDateand EndDate.