BigQuery - unsupported subquery with table in join predicate-CodePudding

Recently I am starting to work on BigQuery and there's something that make 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 in Big Query doesn't support the subquery in join, I've been tried many alternatives but the result doesn't match to each other.

CodePudding user response：

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;

CodePudding user response：

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;