Hive表连接----内连接，全连接，左连接，右连接

数据准备：员工表，部门表   

建表语句：12个员工，3个部门

create table emp(
    id int,
    name string,
    deptno int
)clustered by (id) into 2 buckets row format delimited fields terminated by ",";

truncate table emp;

insert  into emp (id, name, deptno)
values (1,"zhangsan",10),(2,"lisi",20),(3,"wangwu",30),(4,"zhaoliu",10),(5,"xiaoqi",30),(6,"gouba",10),
       (7,"jiujiu",20),(8,"shishi",30),(9,"shiyi",null),(10,"shier",10),(11,"shisan",10),(12,"shisi",30);

select *
from emp order by id;


truncate table dept;
create table dept(
    deptno int,
    deptname string
)clustered by (deptno) into 3 buckets row format delimited fields terminated by ",";

insert into dept
values (10,"研发部"),(20,"开发部"),(30,"宣传部");

select *
from dept;

1.内连接：只返回两个表中满足连接条件的记录。当某一表中的记录在另一表中没有匹配时，这些记录将被排除在结果之外。

select *
from emp e inner join dept d on e.deptno = d.deptno order by id;


1,zhangsan,10,10,研发部
2,lisi,20,20,开发部
3,wangwu,