Bigquery If field exists

Question

Short: Is there a way to query in BQ fields that don't exist, receiving nulls for these fields?

I have almost the same issue that BigQuery IF field exists THEN but sometimes my APIs can query tables were there are not some particular fields (historic tables) and this approach fails because it needs a table with that field:

SELECT a, b, c, COALESCE(my_field, 0) as my_field
FROM
(SELECT * FROM <somewhere w/o my_field>),
(SELECT * FROM <somewhere with my_field>)

Is there a way to do something like:

SELECT IFEXISTS(a, NULL) as the-field
FROM <somewhere w/o my_field>

Answer 1

Let's assume your table has x and y fields only!
So below query will perfectly work

SELECT x, y FROM YourTable

But below one will fail because of non-existing field z

SELECT x, y, z FROM YourTable

The way to address this is as below

#legacySQL
SELECT x, y, COALESCE(z, 0) as z
FROM 
(SELECT * FROM YourTable),
(SELECT true AS fake, NULL as z)
WHERE fake IS NULL

EDIT: added explicit #legacySQL to not to confuse those who is trying to apply this exact approach to Standard SQL :o)

Answer 2

Like @phaigeim, I wasn't able to use Mikhail's answer in 2019 - I got "Column name z is ambiguous".

I wound up using the BigQuery Information Schema tables to check if the column exists, and otherwise do SELECT NULL as z. I did this in dbt using a jinja macro since I couldn't figure out a way to do it in straight SQL. That restricts its applicability, but it may be an option in some use cases.