CODEWITHSUNDEEP
google-bigquery
Brad Davis
Brad Davis

Reputation: 1170

Joining multiple tables in bigquery

I would like to be able to join multiple tables in bigquery. Joining two is pretty trivial.

SELECT
t1.field1 AS field1,
t2.field2 AS field2,
t1.field3 AS field3
FROM [datasetName.tableA] t1
JOIN [datasetName.tableB] t2
ON t1.somefield = t2.anotherfield

But what if I want to join three or more tables? Can I just do it as

SELECT
t1.field1 AS field1,
t2.field2 AS field2,
t1.field3 AS field3,
t3.field4 as field4
FROM [datasetName.tableA] t1
JOIN [datasetName.tableB] t2
JOIN [datasetName.tableC] t3
ON t1.somefield = t2.anotherfield AND t1.somefield=t3.yetanotherfield

I've tried that and it doesn't work. I think I need to do something like

SELECT 
t12.field1 as field1,
t12.field2 as field2,
t3.field3 as field3,
FROM
(SELECT
t1.field1 AS field1,
t2.field2 AS field2,
t1.field3 AS field3
FROM [datasetName.tableA] t1
JOIN [datasetName.tableB] t2
ON t1.somefield = t2.anotherfield) t12
JOIN
[datasetName.tableC] t3
ON t12.field1 = t3.field1

But is there a simpler way to accomplish this?

Thanks, Brad

Upvotes: 14

Views: 43218

Answers (2)

dinigo
dinigo

Reputation: 7448

You can also use the USING(field) notation in Standard SQL. Which is actually just sugar for @mikhail-berlyant answer. Docs here

#standardSQL
SELECT
  t1.field1 AS field1,
  t2.field2 AS field2,
  t1.field3 AS field3
FROM `datasetName.tableA` t1
JOIN `datasetName.tableB` t2 USING(commonfield_AB)
JOIN `datasetName.tableC` t3 USING(commonfield_AC)

Upvotes: 7

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

I think you are looking for something like below 

SELECT
  t1.field1 AS field1,
  t2.field2 AS field2,
  t1.field3 AS field3,
  t3.field4 AS field4
FROM [datasetName.tableA] t1
JOIN [datasetName.tableB] t2 ON t1.somefield = t2.anotherfield
JOIN [datasetName.tableC] t3 ON t1.somefield = t3.yetanotherfield

Upvotes: 32

Related Questions