How do you compare two arrays in BigQuery?

Here is an alternative solution, which avoids running JOIN in correlated subquery, and instead relies on IN UNNEST() expression - this should give better performance:

#standardSQL
WITH a AS (
  SELECT 1 AS id, [2,4] AS a_arr UNION ALL
  SELECT 2, [3,5]
),
b AS (
  SELECT 11 AS value, [1,2,3,4] AS b_arr UNION ALL
  SELECT 12, [1,3,5,6]
)
SELECT a.id, b.value
FROM a , b
WHERE (SELECT LOGICAL_AND(a_i IN UNNEST(b.b_arr)) FROM UNNEST(a.a_arr) a_i)

Try below example (BigQuery Standard SQL)

#standardSQL
WITH a AS (
  SELECT 1 AS id, [2,4] AS a_arr UNION ALL
  SELECT 2, [3,5]
),
b AS (
  SELECT 11 AS value, [1,2,3,4] AS b_arr UNION ALL
  SELECT 12, [1,3,5,6]
)
SELECT a.id, b.value
FROM a , b , UNNEST([(SELECT ARRAY_LENGTH(a.a_arr) - COUNT(1) 
                      FROM UNNEST(a.a_arr) AS x 
                      JOIN UNNEST(b.b_arr)  AS y 
                      ON x = y)]) AS z
WHERE z = 0

it mimics below pseudo-code:

SELECT a.id, b.value
FROM a INNER JOIN b
ON a.array IN b.array  

Let me know if you want me to apply this to your example - or you will try by yourself first :o)