SQL Union horizontally without common key

Question

I have two tables in BigQuery.

TABLE1:

| timestamp | FIELD1 | FIELD2 | ...
| 12345678  | 000000 | 000000 | ...
| 00154789  | 000000 | 000000 | ...

TABLE2:

| temperature |
| 1000000000  |
| 2000000000  |

REQUIRED SELECT OUTPUT

| timestamp | temperature | FIELD1 | FIELD2 | ...
| 12345678  | 1000000000  | 000000 | 000000 | ...
| 00154789  | 2000000000  | 000000 | 000000 | ...

I would like to simply union the two horizontally. There is no common key between the two tables.

Thanks.

Answer 1

Referred to this site, posting this as wikianswer.

You may want to try these queries with desired result.

Cross-join approach:

with table1 as (
  select 12345678 as timestmp, 000000 as field1, 000000 as field2,
  union all select 00154789 as timestmp, 000000 as field1, 000000 as field2
),

table2 as (
  select 1000000000 as temperature,
  union all select 2000000000 as temperature
)

select t1.timestmp,
  t2.temperature,
  t1.field1,
  t1.field2
 from table1 t1
cross join table2 t2
where 
  (t1.timestmp = 12345678 and t2.temperature = 1000000000)
  or
  (t1.timestmp = 154789 and t2.temperature = 2000000000)

Row Number approach:

with table1 as (
  select 12345678 as timestmp, 000000 as field1, 000000 as field2,
  union all select 00154789 as timestmp, 000000 as field1, 000000 as field2
),

table2 as (
  select 1000000000 as temperature,
  union all select 2000000000 as temperature
),

table1_row_num as (
  select *, row_number() over (order by (timestmp))  as row_num from table1
),

table2_row_num as (
  select *, row_number() over (order by (temperature) desc)  as row_num from table2
)

select t1.timestmp,
  t2.temperature,
  t1.field1,
  t1.field2
 from table1_row_num as t1
inner join table2_row_num as t2
  on t1.row_num = t2.row_num

Output of both queries: