Is there a way to prepend joined tablename in BigQuery Standard SQL?

Question

I would like to know if it is possible to force standard SQL to prepend joined tablenames, so that I can get all fields from a and b with a_ and b_ prepended, without manually naming each field. Ie. I want to do a SELECT *, I don't want to generate each fieldname.

I know that if you switch from Legacy SQL to Standard SQL, a

SELECT *
FROM first_table a
JOIN second_table b
ON a.key = b.key

won't automatically prepend the a_ and b_ to each outputted variable, as described in this question: How to remove/avoid prepended tablename in bigquery? But I want to know if that behavior can be modified.

Answer 1

The only option is to "separate" the names by using a dot, i.e. returning a struct with the columns from each side:

SELECT a, b
FROM first_table a
JOIN second_table b
ON a.key = b.key;

If you only want certain columns from each table, you can use subselects:

SELECT a, b
FROM (SELECT x, y, key FROM first_table) a
JOIN (SELECT foo, bar, key FROM second_table) b
ON a.key = b.key;

Answer 2

The question is a little generic - so my answer have chance to not to address PO's specific practical needs, but I thought it would be interesting to consider below approach in some cases

Idea is to have table (template table - it can be just empty) with needed columns names and than use it as first table in union all with your real query

Something like this:

#standardSQL
SELECT * FROM template WHERE NULL UNION ALL
SELECT * 
FROM first_table AS a
JOIN second_table AS b 
ON a.key = b.key

You can try/test this approach with below dummy tables

#standardSQL
WITH first_table AS (
  SELECT 777 AS key, 1 AS x, 2 AS y, 3 AS z
),
second_table  AS (
  SELECT 777 AS key, 4 AS x, 5 AS y, 6 AS z
),
template AS (
  SELECT NULL AS a_key, NULL AS a_x, NULL AS a_y, NULL AS a_z, NULL AS b_key, NULL AS b_x, NULL AS b_y, NULL AS b_z
)
SELECT * FROM template WHERE NULL UNION ALL
SELECT * 
FROM first_table AS a
JOIN second_table AS b 
ON a.key = b.key

with result as below

a_key   a_x a_y a_z b_key   b_x b_y b_z  
777     1   2   3   777     4   5   6