CODEWITHSUNDEEP
sqlgoogle-bigquery
Please
Please

Reputation: 296

Does BigQuery guarantee column order when performing SELECT * query from subselect?

Does BigQuery guarantee column order when performing SELECT * FROM subquery?
For example, given table t with columns a, b, c, d if I'd execute a query:

SELECT * EXCEPT (a) FROM (SELECT a, b, c, d FROM t)

Will the columns in the result always have the same order (b, c, d in this case)?

Upvotes: 1

Views: 1873

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

I am going to answer "yes"; however, it is not documented to do so. The only guarantee (according to the documentation) is:

SELECT *, often referred to as select star, produces one output column for each column that is visible after executing the full query.

This does NOT explicitly state that the columns are in the order they are defined -- i.e. first by the order of references in the FROM clause and then by the ordering within each reference. I'm not even sure if the SQL standard specifies the ordering (although I have a vague recollection that the 92 standard might have).

That said, I have never seen any database not produce the columns in the specified order. Plus, databases (in general) have to keep the ordering of the column to support INSERT. You can see it yourself in INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION. And, the REPLACE functionality (to replace a column expression in the SELECT list) would make less sense if the ordering were not guaranteed.

I might argue that you are "pretty safe" assuming that the ordering is the same. And that we should lobby Google to fix the documentation to make this clear.

Upvotes: 2

Related Questions