Is it possible to use UNION ALL in CREATE query?

Question

crate version: 1.1.3
I can't find union all syntax in CREATE to do complex query, I also search the doc, but got nothing

(SELECT col1, col2, col3 FROM tab1) UNION ALL (SELECT col1, col2, col3 FROM tab2)

how to execute this sql in crate

AFAIK `Union/Union all` is only applicable to SELECT. – Enzokie May 18 '17 at 01:53 — Enzokie, May 18 '17 at 01:53
yes, I do select, not insert or update – MayI May 19 '17 at 08:49 — MayI, May 19 '17 at 08:49

score 1 · Accepted Answer · answered Jan 30 '18 at 17:31

You can use UNION ALL with CrateDB 2.3.x, available here: https://crate.io/download/#stable

You can run queries like:

SELECT id1, name1 from t1 
UNION ALL 
SELECT id2, name2 from t2 
ORDER BY name1;

Or use subselects on either side:

SELECT * from (SELECT id1, name1 from t1 order by id1 limit 2) 
UNION ALL 
SELECT id2, name2 from t2;

Or join multiple tables:

SELECT id1, name1 from t1 
UNION ALL 
SELECT id2, name2 from t2 
UNION ALL 
SELECT id3, name3 from t3 
ORDER BY name1;

1 Answers1