CODEWITHSUNDEEP
sqljoingoogle-bigqueryleft-joinwith-clause
solomon paul
solomon paul

Reputation: 11

Does BigQuery allow mulitple-table joins with a SQL `with` clause?

Does BigQuery allow mulitple-table joins with a SQL with clause?

Example:

WITH t1 as (select tab1, tab2 from t1 group by 1,2), 
t2 as ( select tab1, tab2, tab3, from t2 group by 1,2,3)
t3 as ( select tab1, tab2, from t3 group by 1,2,3)

I can make the query below work by repeating the same query multiple times, but do not want to, and want to leverage it with with as:

SELECT a.tab1, a.tab2, b.tab1, b. tab3, c.tab1, c.tab2
FROM  t1 a
LEFT JOIN t2 b
ON a.tabl = b.tab1 and a.tab2 = b.tab3
LEFT JOIN t3 c
ON a.tab1 = c.tab1

SELECT a.tab1, a.tab2, b.tab1, b. tab3, c.tab1, c.tab2
FROM  t1 a
LEFT JOIN t2 b
ON a.tabl = b.tab1 and a.tab2 = b.tab3
LEFT JOIN t3 c
ON a.tab1 = c.tab1

Upvotes: 0

Views: 66

Answers (0)

Related Questions