'BigQuery - unsupported subquery with table in join predicate

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.

sqlgoogle-bigquerybigquery-udf


Solution 1:[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;

Solution 2:[2]

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;

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 vitooh
Solution 3 selvakrishnan

Related Questions