HIVE的join，left join，in等使用

注：技术交流可以加我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，需求，在2019-06-30日那些用户没有登陆？
分析：从用户登录表看，在2019-06-30日，只有A登陆了，用户表中有A和B，那么想要的结果是显示理财规划师B没有登陆
语句：

   

select u.* ,t.login_day
from tmp_user u --u tmp_user表的别名，用来关联其他表好用
left join tmp_login t --t tmp_login表的别名，用来关联其他表好用
on u.uid=t.uid --关联条件是 tmp_user表和tmp_login表的uid相等
and t.login_day='2019-06-30' --登陆日是2019-06-30
where t.uid is null --此表中tmp_login不存在的记录

结果：理财规划师B没有登陆，登陆日是NULL

uid	name	login_day
B	理财规划师B	NULL

注意：此处left  join可以换成left outer join，两种写法都可以，没有啥区别。

4，需求，在2019-06-30日那些用户登陆了？

分析：从用户登录表看，在2019-06-30日，只有A登陆了，用户表中有A和B，那么想要的结果是显示理财规划师A有登陆

语句：

select u.*,t.login_day
from tmp_user u --u tmp_user表的别名，用来关联其他表好用
left join tmp_login t --t tmp_login表的别名，用来关联其他表好用
on u.uid=t.uid --关联条件是 tmp_user表和tmp_login表的uid相等
and t.login_day='2019-06-30' --登陆日是2019-06-30
where t.uid is not null --此表中tmp_login存在的记录

结果：理财规划师A有登陆，登陆日是2019-06-30

uid	name	login_day
A	理财规划师A	2019-06-30

注意：观察需求4和需求3的语句，只是把需求3的where t.uid is null换成where t.uid is not null，就达到了想要的结果。

此处left  join可以换成left outer join，也可以换成join。但是在需求3里不能换成join，有兴趣的可以试一试，体会其中奥义。

另一种写法，以登陆表为主表。

语句：

select u.*,t.login_day
from tmp_login t  --t tmp_login表的别名，用来关联其他表好
left join tmp_user u --u tmp_user表的别名，用来关联其他表好用用
on u.uid=t.uid --关联条件是 tmp_user表和tmp_login表的uid相等
where t.login_day='2019-06-30' --登陆日是2019-06-30
;

结果：理财规划师A有登陆，登陆日是2019-06-30

uid	name	login_day
A	理财规划师A	2019-06-30

 

注意：此处left  join可以换成left outer join，也可以换成join。将登陆表作为主表以后，代码就更简洁了，而且更易读懂。

5，需求，查出uid是A的所有登陆消息。

语句：

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

结果：

uid	login_day
A	2019-06-30
A	2019-07-01

 

另一种写法，关联用户表的一种写法

语句：

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

结果：

uid	login_day
A	2019-06-30
A	2019-07-01

 

第三种写法，

语句：

select t.*,u.name
from tmp_login t  --t tmp_login表的别名，用来关联其他表好
join tmp_user u --u tmp_user表的别名，用来关联其他表好用用
on u.uid=t.uid  --关联条件是 tmp_user表和tmp_login表的uid相等
where u.uid='A'
;

结果：

uid	login_day	name
A	2019-06-30	理财规划师A
A	2019-07-01	理财规划师A

 

注意：观察另一种写法和第三种写法，其实就是join和in的转换写法，in可以转换成join，但是join要是转换成in，就会少一些信息，例如第三种写法里能看到理财规划师A的名字，另一种写法里，只会展示出两列。名字无法展示出来，所以需要活学活用。

留下一个问题，如果在另一种写法里，in(select uid from tmp_user u where u.uid='A')的括号里面有两条A的记录，会是什么结果呢？