BigQuery - unsupported subquery with table in join predicate

Question

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.

Answer 1

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;

Answer 2

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