HIVE的left semi join （in和exsist）与 join（inner join） 相同点与区别

注：技术交流可以加我VX：k-loop，昵称：默读者。

1，创建用户表

   

create table tmp_user(
uid string comment'用户ID',
name string comment'用户名'
)comment'用户表'
;

插入测试数据

   

insert into tmp_user(uid,name) values('A','理财规划师A');
insert into tmp_user(uid,name) values('B','理财规划师B');

只要列名入插入的数据相对应也可以这样简写

   

insert into tmp_user values('A','理财规划师A');
insert into tmp_user values('B','理财规划师B');

 这个表里有两个会员，理财规划师A和理财规划师B。

2，创建用户登录表

   

create table tmp_login(
uid string comment'用户ID',
login_day string comment'登陆日'
)comment'用户登陆表'
;

 

 插入测试数据
 

   

insert into tmp_login(uid,login_day) values('A','2019-06-30');
insert into tmp_login(uid,login_day) values('A','2019-07-01');
insert into tmp_login(uid,login_day) values('B','2019-07-01');

3，需求，用户A是否登陆过？有登陆过就显示用户A的信息，没有登陆过不显示。

分析：因为在登陆表上看到A有两次登陆记录。所以A登陆过。

语句：

select u.* 
from tmp_user u --u tmp_user表的别名，用来关联其他表好用用
where u.uid in (select uid from tmp_login where uid='A')
and u.uid='A'
;

结果：

uid	name
A	理财规划师A

 

注意：虽然在登录表tmp_login中，A有2条登陆记录，但是in语句，只会显示一条。意思是只要在in语句中出现一次，就不会再去搜索了。

另一种写法：

select u.* 
from tmp_user u --u tmp_user表的别名，用来关联其他表好用用
where  exists (select * from tmp_login t where t.uid=u.uid)
and u.uid='A'
;

结果：

uid	name
A	理财规划师A

 

高效写法：

select u.* 
from tmp_user u --u tmp_user表的别名，用来关联其他表好用用
left semi join tmp_login t--t tmp_login表的别名，用来关联其他表好
on u.uid=t.uid
where  u.uid='A'
;

结果：

uid	name
A	理财规划师A

 

结论：left semi join 是 in和exists的高效写法，hive上建议使用left semi join。

如果把高效写法的semi去掉会是什么样子呢？

语句：

select u.* 
from tmp_user u --u tmp_user表的别名，用来关联其他表好用用
left join tmp_login t--t tmp_login表的别名，用来关联其他表好
on u.uid=t.uid
where  u.uid='A'
;

结果：

uid	name
A	理财规划师A
A	理财规划师A

 

分析，有两条重复的数据，这是left semi join 与left join 的不同点

去重的语句：

select distinct u.* 
from tmp_user u --u tmp_user表的别名，用来关联其他表好用用
left join tmp_login t--t tmp_login表的别名，用来关联其他表好
on u.uid=t.uid
where  u.uid='A'
;

总结：

1，left semi join 可以实现in和exists的实现。

2，left semi join 与 left join 的不同点。

     a，left semi join 只能显示左表（tmp_user）的信息，在select 后面不能出现查询右表（tmp_login）的字段，否则报错。

           where 条件里也不可以有右表的字段出现。

     b，left semi join 时，如果右表有重复数据，对应的左表没有重复数据，那么查询出来的结果就不会有重复数据。

         如果右表和 左表都有重复数据，查出来的结果同左表。

     c，left join 时，如果右表有重复数据，对应的左表没有重复数据，那么查询出来的结果重复数据（条数等于右表的条数）。

         如果右表和左表都有重复数据，查出来的结果重复（条数等于两个表的乘机）。

     d，left join 时，可以显示右表中的字段，在where 条件里可以有右表的字段出现，如果右表的字段出现在了where 条件了，

        那么此时的left join 等同 join，也等同于 inner join 。

     e，left join 时，where 条件里没有右表的字段，那么当右表中的数据不存在时，左表数据存在时，那么只显示左表中的信息。

      f，left semi join 时 当右表众不存在时，不显示任何信息。