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.

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_id

The result has every column of fact_mot_test except the two named ones.

The columns keep their order from the table.

All BigQuery lessons