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.

@Tushar I think It doesn't because I added `#standardSQL` and it still got an error — hushhush, Sep 23 '21 at 04:22
@hushhush Add to the question the query you are running in BQ and also the error — Mr.Batra, Sep 23 '21 at 07:00

score 3 · Answer 1 · answered Sep 23 '21 at 11:40

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;

score 2 · Answer 2 · answered Apr 25 '22 at 14:10

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

answered Apr 25 '22 at 14:10

selvakrishnan

21
3

score 0 · Answer 3 · answered Sep 23 '21 at 10:56

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;

score 0 · Answer 4 · answered Jul 18 '23 at 20:42

I am not sure about this part fully, but if your usecase permits this, it seems like I could get this working:

SELECT a.abc, c.xyz
FROM table1 as a
LEFT JOIN table2 as c
ON a.abc = c.abc
WHERE c.date = (select t.date from table3 t)

Again, it's quite possible that it could decrease query performance since I am not sure if WHERE() is applied after the JOIN() is complete, which means a more expensive query. For my usecase, it worked well.

BigQuery - unsupported subquery with table in join predicate

4 Answers4