Using SELECT * with EXCEPT and REPLACE

SELECT * is not a good practice in production, but I still use it for spot checks, when debugging, analyzing or validating data - especially when working with wide tables.

Here are two useful clauses to keep in mind when working with SELECT * in BigQuery: EXCEPT and REPLACE.

➡ EXCEPT will exclude one or more columns from the output.
➡ REPLACE will swap the enumerated columns with the new definitions you provide.

Also, since SELECT DISTINCT * won't work when you have a STRUCT column, you can use EXCEPT to exclude struct columns.

Let's look at an example. Say we'd like to select all the columns but exclude the Salary and modify the CustomerId.

Here's how the code would look:

SELECT * 

EXCEPT(Salary) 

REPLACE(CONCAT('SystemA','-',CustomerId) AS CustomerId) 

FROM `learning.Customers`

This would produce the following output!

Thanks for reading!

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.