left semi join 与join 的区别

LEFT SEMI JOIN:左半开连接会返回左边表的记录，前提是其记录对于右边表满足ON语句中的判定条件。对于常见的内连接（INNER JOIN），这是一个特殊的，优化了的情况。大多数的SQL方言会通过in.......exists结构来处理这种情况。

准备表：

create table dcx1107(
id bigint
);
insert into dcx1107 values(-1);
insert into dcx1107 values(1);

create table dcx_2(
id bigint
,role string
);

insert into dcx_2 values(-1,'C1');
insert into dcx_2 values(1,'C1');
insert into dcx_2 values(1,'C2');

查询数据：

--join的select的结果中可以有t1（左表）,t2（右表）两张表的字段
select 
t1.id,t2.role
from dcx1107 t1
join dcx_2 t2
on t1.id=t2.id;

结果：

--left semi join的select的结果中只允许出现t1（左表）表的字段
select 
t1.id
from dcx1107 t1
left semi join dcx_2 t2
on (t1.id=t2.id);

--等价于
 select 
t1.id
from dcx1107 t1
where id in (select id from dcx_2)
;

--等价于
 select 
t1.id
from dcx1107 t1
where EXISTS (select 1 from dcx_2 t2 where t1.id=t2.id)

结果：

这样写会报错
select 
t1.id,t2.role
from dcx1107 t1
left semi join dcx_2 t2
on (t1.id=t2.id);

总结：

对待右表中重复key的处理方式差异：因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join on 则会一直遍历。
left semi join 中最后 select 的结果只许出现左表，因为右表只有 join key 参与关联计算了，而 join on 默认是整个关系模型都参与计算了。