Google BigQuery SQL: Prevent column prefix renaming after join

Question

Say you have a table "table_with_100_columns."

And you want to add one more column with a simple join... without changing all of the column names. In other words, you wish to write something like

SELECT a.* as <a's columns without prefix>, additional_field
FROM [table_with_100_columns] a
JOIN [table_with_2_columns] b
ON a.col1 = b.key

You should be able to do this to generate a new table with 101 columns, without having to rename every single column by hand. Right now the only way I know how to do this as follows:

SELECT
  a.col1 as col1,
  a.col2 as col2,
  a.col3 as col3,
  ...
  a.col100 as col100,
  b.additional_field as additional_field
FROM [table_with_100_columns] a
JOIN [table_with_2_columns] b
ON a.col1 = b.key

Having to write 100 unnecessary lines of code simply to add one more column to a table is unbelievably inefficient - so I'm hoping there is a better way to preserve column names while joining?

UPDATE

It appears this is not yet possible in BigQuery. It is very easy to implement and I suggest the following to the Google BigQuery team:

if no fields share a name in SELECT clause:
  if no subtable reference names given:
    Do not rename fields after JOIN

This will not break any current functionality and adds simple support for a very useful feature.

Answer 1

I think this problem is specific to BigQuery Legacy SQL.
If you will use Big Standard SQL - you will not have this issue - see example below

#standardSQL
WITH table_with_100_columns AS (
  SELECT 11 AS col1, 21 AS col2, 31 AS col3 UNION ALL 
  SELECT 12 AS col1, 22 AS col2, 32 AS col3 UNION ALL
  SELECT 13 AS col1, 23 AS col2, 33 AS col3 UNION ALL
  SELECT 14 AS col1, 24 AS col2, 34 AS col3 UNION ALL
  SELECT 15 AS col1, 25 AS col2, 35 AS col3   
),
table_with_2_columns AS (
  SELECT 11 AS key, 17 AS additional_field UNION ALL
  SELECT 12 AS key, 27 AS additional_field UNION ALL
  SELECT 13 AS key, 37 AS additional_field UNION ALL
  SELECT 14 AS key, 47 AS additional_field UNION ALL
  SELECT 15 AS key, 57 AS additional_field   
)
SELECT a.*, additional_field
FROM `table_with_100_columns` AS a
JOIN `table_with_2_columns` AS b
ON a.col1 = b.key  

See Migrating from legacy SQL in case if you need rewrite the rest of the query to be in Standard SQL

The output will be as below with original column names (w/o prefixes)

col1    col2    col3    additional_field     
13      23      33      37   
11      21      31      17   
15      25      35      57   
12      22      32      27   
14      24      34      47