BigQuery JOINs between tables in different projects

12
votes

I have datasets in two distinct projects.

I want to perform join of one table from a dataset in the first project to table in a dataset in the second project.

Can you provide an example of such a query?

1
sqldatabasejoingoogle-bigquery

1 Answers

23
votes

Yes, you certainly can. You need to qualify the table name with project name, i.e. projectname:dataset.table Here is an example of my joining one of my tables against table in publicdata project:

select sum(a.is_male)
from
(select is_male, year from [publicdata:samples.natality]) a
inner join
(select year from [moshap.my_years]) b
on a.year = b.year

Update: The syntax above is for Legacy SQL, with Standard SQL it becomes projectname.dataset.table, i.e.

select sum(a.is_male)
from
(select is_male, year from publicdata.samples.natality) a
inner join
(select year from moshap.my_years) b
on a.year = b.year