UNION two datasets together instead of JOIN?

Is it possible? I have two SPICE datasets created with Custom SQL with same columns, I want to use a UNION ALL to concatenate these datasets into one, but the only option that seems to be available in the visual editor is JOIN… Why isn’t UNION an option here? Is it hiding somewhere else?
These are both small datasets which each only take about 2 minutes to load from their queries, but using UNION ALL within a single custom query for some reason makes it take so long that it is automatically cancelled, which is the reason I had to split into two datasets…


this is a good question. I wanted to use this in the past, too.

Do you have many columns in your datasets?

You could make a FULL JOIN on a column, where nothing matches, so you get every row from both tables.

col1| col2| col3| col4| col5|col6
x | x | x | | |
| | | y | y | y

after this you could use COALESCE on every column to move them together.
COALESCE(col1,col4) | COALESCE(col2,col5) | COALESCE(col3,col6)
x | x | x
y | y | y

This is not an optimal solution, but could work.

1 Like