Search code examples
cratedb

Is it possible to use UNION ALL in CREATE query?

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

Solution

  • 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;

    https://crate.io/docs/crate/reference/en/2.3/general/dql/union.html