union/union all
用于合并两个或多个 select / exec 查询的结果集。union 会将重复的记录删去,union all 保留所有记录。支持在分布式查询中使用。
注意:
-
union / union all 连接的 select / exec 语句必须查询相同数量的列,且对应列的类型必须能够相互转换;
-
第一个查询的列名和类型决定了结果集的列名和列类型。
例子
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 |