数据准备:员工表,部门表
建表语句: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,