union/union all

union/union all is used to combine the result sets of 2 or more select / exec statements. The union keyword removes duplicate records between various statements, while union all returns all records.

The SQL keywords union/union all support distributed queries to access data from DFS tables.

Note:

Every select / exec statement must have the same number of columns.

The columns must have convertible data types.

The column names and types in the result set are determined by the column queried in the first SELECT statement.

Examples

t1= table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as v1)
t2 = table(5 3 3 as id,  3.2 5.1 0.1 as v2);
select * from t1 union select * from t2


id	v1
1	7.8
2	4.6
3	5.1
3	0.1
5	3.2

select * from t1 union all select * from t2


id	v1
1	7.8
2	4.6
3	5.1
3	0.1
5	3.2
3	5.1
3	0.1

t3 = table(3 3 4 as id,  5.1 0.2 1.1 as v3);
(select * from t1 where id=3) union all (select * from t2 where id=3) union (select * from t3 where id=3)


id	v1
3	5.1
3	0.1
3	0.2