join系列

1、前言

一说到join，立马想到 inner join 和left join 以及right join（下图就是这几个join的基本操作） ，
但是对于平时开发，还是效率不够，编写复杂。
下面介绍 left semi join，left anti join，full join的使用提高开发效率。

2、join

2.0 建表和插入数据

use interview_db;
set hive.exec.mode.local.auto=true;
drop table if exists all_users;
create table all_users(
    id int comment '用户id',
    name string comment '用户姓名',
    sex string comment '性别',
    age int comment '年龄'
) comment '银行用户信息表';
insert overwrite table all_users values
(1,'张三','男',20),
(2,'李四','男',29),
(3,'王五','男',21),
(4,'赵六','女',28),
(5,'田七','女',22);

drop table if exists black_list;
create table black_list(
    user_id int comment '用户编号',
    type string comment '风控类型'
)comment '银行黑名单信息表';
insert overwrite table black_list values
(1,'诈骗'),
(2,'逾期'),
(3,'套现');

2.1 inner join内连接

连接两个表符合条件的记录，没什么好说的。

2.2 left join内连接

解释：对于left join，不管on后面跟什么条件，左表的数据全部查出来，
因此要想过滤需把条件放到where后面

下面通过一个例子加深印象：

• 题目：使用left join对所有用户，如果也在黑名单中，则标记为YES，否则标记为NO。

2.3 left semi join

下面直接通过题目说结论
使用left semi join对所有用户，如果也在黑名单中，则挑选出来。

• 结论：

1、LEFT SEMI JOIN 本质上就是 IN/EXISTS 子查询的表现，是IN的一种优化。
从效果上来看有点像inner join之后只返回左表的结果，只不过效率高一些。
2、LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方都不行。
因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条。

3、left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。因为右表只有 join key 参与关联计算了。

2.4 left anti join

题目：
使用 left anti join 对所有用户，不在黑名单中，则挑选出来。

结论：
1、LEFT ANTI JOIN 本质上就是NOT IN/EXISTS 子查询的表现，是NOT IN的一种优化。
当on条件不成立时，才返回左表中的数据，保留在结果集中。

2.5 full join