注:技术交流可以加我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 时 当右表众不存在时,不显示任何信息。