BigQuery select, join from multiple datasets and avoid name conflicts-CodePudding

Imagine I have several datasets and tables.

Format: dataset.table.field

dataset01.table_xxx.field_z
dataset02.table_xxx.field_z

I try to write smth like

select 
dataset01.table_xxx.field_z as dataset01_table_xxx_field_z,
dataset02.table_xxx.field_z as dataset02_table_xxx_field_z
from dataset01.table_xxx
join dataset02.table_xxx on dataset02.table_xxx.field_z = dataset01.table_xxx.field_z

to avoid conflicting names BigQuery says that dataset01.table_xxx.field_xxx is unrecognised name in SELECT clause.

it complains about unrecognised name in join clause too. Query works if I remove dataset01, dataset02 from SELECT clause and on condition

What is the right way to refer fields in such case?

CodePudding user response：

select 
  t1.field_z as dataset01_table_xxx_field_z,
  t2.field_z as dataset02_table_xxx_field_z
from dataset01.table_xxx t1
join dataset02.table_xxx t2
on t2.field_z = t1.field_z