How do I cross-join a table with a list?

Question

If I have a table mytable and a list

set vals = (1,2,3,4);

and I want to cross-join the table with the list (getting a new table which has 4 time as many rows as the original table and an extra val column), do I have a better option than creating an explicit temp table?

What I can do is:

select a.*, b.val
from mytable a cross join
(select stack(4,1,2,3,4) as (val) from 
 (select * from mytable limit 1) z) b;

EDIT: My main use case would be passing -hiveconf vals='4,1,2,3,4' to hive and replacing stack(4,1,2,3,4) with stack(${hiveconf:vals}) in the above code.

Necrotomy

Can't you use Union? like (select 1 as stg union select 2 union select 3 union selec 4)? – Candelabrum

@PeterRing: I think union would be even worse than what I have, but please do post an answer using it! – Necrotomy

Answer 1

select a.*, b.val
from a lateral view explode(array(1,2,3,4)) b as val;

Holmic

Answer 2

I dont know this will help.

SELECT *
from mytable cross join
(select 1 as p
union 
select 2 
union 
select 3
union 
select 4) as x

Candelabrum

+1, but what if I have 20 instead of 4 values? I think your code explodes in size. – Necrotomy

Answer 3

select a.*, b.val
from mytable a cross join
(values (1), (2), (3), (4), (5))b(val);

Dwelt