google bigquery - Optimizing SQL Cross Join that checks if any array value in other column

Question

Let's say I have a table events with structure:

id	value_array
XXXX	[a,b,c,d]
...	...

question from:https://stackoverflow.com/questions/65649794/optimizing-sql-cross-join-that-checks-if-any-array-value-in-other-column

Answer 1

Few quick options for BigQuery Standard SQL

Option 1

select id
from `project.dataset.events`
where exists (
  select 1
  from `project.dataset.values_of_interest` 
  where value in unnest(value_array)
)     

Option 2

select id
from `project.dataset.events` t
where (
  select count(1)
  from t.value_array as value
  join `project.dataset.values_of_interest`
  using(value)
) > 0