BigQuery SQL - SELECT * EXCEPT Clause

visibility 2,378 access_time 2 years ago languageEnglish timeline Stats
timeline Stats
Page index 5.43

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 CustomerID from dim_customer table.

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, StartDate and EndDate.

info Last modified by Raymond 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext
PySpark: Read File in Google Cloud Storage
visibility 5,085
thumb_up 0
access_time 2 years ago
PySpark: Read File in Google Cloud Storage
BigQuery - Convert Bytes to BASE64 or HEX String
visibility 626
thumb_up 0
access_time 9 months ago
BigQuery - Convert Bytes to BASE64 or HEX String
PostgreSQL: Create and List User
visibility 104
thumb_up 0
access_time 9 months ago