Clean way to construct cartesian joins in BigQuery (SQL)

I have multiple lists, and I want to construct cartesian joins of these lists using SQL. I’m wondering if you know a cleaner method than the one below. Thanks

with letters_table AS (Select letters from UNNEST(['A','B','C']) letters)

,numbers_table AS (Select numbers from UNNEST([1,2,3]) numbers)

select letters,numbers,concat(letters,'-',numbers) as concat
from letters_table
cross join
numbers_table

​x
 
with letters_table AS (Select letters from UNNEST(['A','B','C']) letters)​,numbers_table AS (Select numbers from UNNEST([1,2,3]) numbers)​select letters,numbers,concat(letters,'-',numbers) as concatfrom letters_tablecross joinnumbers_table​

Answer

Your query looks fine, but there is no reason to use CTEs:

select letter, number, concat(letter, '-', number) as concat
from unnest(['A', 'B', 'C']) letter cross join
     unnest([1, 2, 3]) number

 
select letter, number, concat(letter, '-', number) as concatfrom unnest(['A', 'B', 'C']) letter cross join     unnest([1, 2, 3]) number​

Advertisement

Answer