--并集
query1 UNION [ALL] query2
--交集
query1 INTERSECT [ALL] query2
--差集
query1 EXCEPT [ALL] query2
join 和using
select a.key,b.value from a
left join b on a.key = b.key
#on内列相同可以用using
select a.key,b.value from a
left join b using key
clickhouse 还有 SEMI JOIN，ANTI JOIN，ANY JOIN重复值任选其一, ASOF JOIN近似匹配可使用>, >=, <, <=
LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。
select a.key, a.value from a
where a.key in (select b.key from b);
--或者
select a.key, a.value from a
left join b on on a.key = b.key
where b.key is not null;
-- 效率更高，select的列只能是左列
select a.key,a.value from a
left semi join b on a.key = b.key
LEFT ANTI JOIN
select a.key, a.value from a
where a.key not exist (select b.key from b);
--或者
select a.key, a.value from a
left join b on on a.key = b.key
where b.key is null;
-- 效率更高，select的列只能是左列
select a.key,a.value from a
left ANTI join b on a.key = b.key
ASOF JOIN
SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond
SELECT count() FROM table_1
ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t
