SELECT * EXCEPT and REPLACE
A table with many columns makes SELECT * tempting, and reading every column is what costs. EXCEPT and REPLACE keep the * and let you control the columns it expands to.
How it works
SELECT * expands to every top-level column of the table. EXCEPT (a, b) removes columns from that list. REPLACE (expression AS column) computes column from the expression and leaves it at the same position in the list.
SELECT * EXCEPT (_load_timestamp)returns every column except_load_timestamp.SELECT * REPLACE (LOWER(make) AS make)returns every column in its original place, withmakecomputed.- Use both in one
SELECT:SELECT * EXCEPT (defects) REPLACE (LOWER(make) AS make).EXCEPTcomes first. - Double quotes write a string in BigQuery, so
"make"is the text valuemakeand never the columnmake. No error warns you:SELECT "make" AS make_colfollowed byGROUP BY make_colreturns one row, because every row holds the same text. Write column names bare, or in backticks. EXCEPTtakes top-level column names. A nested field such asodometer.unitcannot be listed.- Both words have a second meaning in BigQuery that this lesson does not use.
EXCEPTis also the set operator that subtracts one query from another, taught in the Union all lesson, andREPLACEis also the string function, taught in the Strings lesson.
Common mistake
A name in an EXCEPT list has to exist. A misspelt column is an error, so a column that is renamed in the table breaks every query that excluded it.
Worked example
SELECT * EXCEPT (defects, vehicle_snapshot)
FROM `learn.mot.fact_mot_test`
WHERE completed_date = '2024-06-12'
ORDER BY mot_test_idThe result has every column of fact_mot_test except the two named ones.
The columns keep their order from the table.