BigQuery - unsupported subquery with table in join predicate

user_15488802 0
Viewed 2061

Recently I started to work on BigQuery and there's something that makes me still confused. What's the alternative for this query on Big Query?

select a.abc, c.xyz
from table1 as a
left join table2 as c
on a.abc = c.abc
and c.date = (select t.date from table3 t)

The things is that Big Query doesn't support the subquery in join. I've tried many alternatives but the result doesn't match to each other.

bigquery-udf google-bigquery sql
3 Answers

Assuming that table3.date is unique, try writing the query like this:

select a.abc, c.xyz
from table1 a left join
     (table2 c join
      table3 t
      on c.date = t.date
     )
     on a.abc = c.abc;

If there are duplicates in table3, you can phrase this as:

select a.abc, c.xyz
from table1 a left join
     (table2 c join
      (select distinct date
       from table3 t
      ) t
      on c.date = t.date
     )
     on a.abc = c.abc;
user_11440350

Indeed at the moment subqueries are not supported in join predicate.

If you really need this you can file Feature Request for that. I am sure that many people will be happy with it.

There is a workaround. You can make a script of it, something like:

declare date TIMESTAMP;
set date = (select t.date from table3 t);

select a.abc, c.xyz
from table1 as a
left join table2 as c
on a.abc = c.abc
and c.date = date;
user_124103320

You can use with clause to resolve this issue, 

WITH TEMP_TB(select t.date as date from table3 t) 
select a.abc, c.xyz
from table1 as a 
left join table2 as c
on a.abc = c.abc
left join c.date = TEMP_TB.date
user_189403610
Related