语法
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN …]
[GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
[LIMIT [n, ]m]
[UNION ALL …]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]
Join字句
支持的 JOIN 类型
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
ASOF JOIN
ANY 与 ALL
在使用 ALL 修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。
在使用 ANY 修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应，不存在多余的行时， ANY 与 ALL 的结果相同。
示列：
--建表
drop table jion_a;
create table join_a(
user_id UInt64 comment '用户ID',
user_name String comment '用户名称',
start_time DateTime comment '开始时间',
end_time DateTime comment '截止时间'
) engine=Log;
truncate table join_a;
insert into join_a
(user_id ,user_name ,start_time,end_time)
values (1, 'a', toDateTime('2017-09-20 00:00:00'), toDateTime('2018-09-20 00:00:00'))
(1, 'a', toDateTime('2018-09-20 00:00:00'), toDateTime('2019-09-01 00:00:00')),
(1, 'b', toDateTime('2019-09-01 00:00:00'), toDateTime('2020-09-01 00:00:00'));
drop table join_b;
create table join_b(
user_id UInt64 comment '用户ID',
pay_time DateTime comment '支付时间',
pay_channel String comment '支付渠道',
cost UInt64 comment '支付金额'
) engine = Log;
insert into join_b(user_id, pay_time, pay_channel, cost)
values (1, toDateTime('2022-09-25 00:00:00'), 'Vivo', 979797); --关联
假设: 左边记录数为: n 右边为: m
select * from join_a;
select * from join_b;
---------------------------------- any join ----------------------------------
匹配右边表的第一条.
set any_join_distinct_right_table_keys=1;
select b., a.user_name, a.start_time, a.end_time from join_b b any inner join join_a a on b.user_id = a.user_id ;
---------------------------------- all join ----------------------------------
匹配右表所有行
. select b.,
a.user_name,
a.start_time,
a.end_time
from join_b b
all inner join join_a a on b.user_id = a.user_id
where b.pay_time >= a.start_time and b.pay_time < a.end_time;
---------------------------------- ASOF join ----------------------------------
条件关联:
select b.*, a.user_name, a.start_time, a.end_time from join_b b ASOF join join_a a on b.user_id = a.user_id and b.pay_time >= a.start_time;
---------------------------------- any join ----------------------------------
匹配右边表的第一条.
set any_join_distinct_right_table_keys=1;
select b., a.user_name, a.start_time, a.end_time from join_b b any inner join join_a a on b.user_id = a.user_id ;
---------------------------------- all join ----------------------------------
匹配右表所有行. select b.,
a.user_name,
a.start_time,
a.end_time
from join_b b
all inner join join_a a on b.user_id = a.user_id
where b.pay_time >= a.start_time and b.pay_time < a.end_time;
---------------------------------- ASOF join ----------------------------------
条件关联:
select b.*, a.user_name, a.start_time, a.end_time from join_b b ASOF join join_a a on b.user_id = a.user_id and b.pay_time >= a.start_time;