CODEWITHSUNDEEP
sqlgoogle-bigquerybigquery-udf
hushhush
hushhush

Reputation: 157

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.

Upvotes: 3

Views: 8377

Answers (4)

Akhilesh Siddhanti
Akhilesh Siddhanti

Reputation: 100

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.

Upvotes: 0

selvakrishnan
selvakrishnan

Reputation: 21

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 3

vitooh
vitooh

Reputation: 4262

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;

Upvotes: 0

Related Questions