hive实战 之 左连接、右连接

---

结论：左连接以左表做参照，右连接以右表做参照

---

 创建表a: 

create table a(name string,age int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

---

创建表b: 

create table b(name string,count int,year int,mon int ,day int,hour int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

---

创建临时表temp: 

create table temp (name string ,age int,count int,year int,mon int ,day int,hour int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

---

 将数据导入表：

load data local inpath "/root/a.txt" into table a  ;

---

 或者使用overswrite关键字，先删除表中所有数据，然后再添加

 load data local  inpath "/root/a.txt"  overwrite  into table a;

---

load data local inpath "/root/b.txt" into table b  ;

---

下面准备两份数据

---

a.txt

---

xuhaitao        36
hunkxu          33
xiaotao         28
xiaohong        3
xiaoshuo        4

---

 b.txt

xuhaitao,15,2020,10,01,6
hunkxu,9,2020,10,01,9
xuhaitao,13,2020,10,01,9
hunkxu,19,2020,10,01,19
xiaotao,2,2020,10,01,10
xuhaitao,15,2020,10,01,6
xiaotao,19,2020,10,01,9
zhangsan,19,2020,10,01,9

---

 

---

下面测试一下  left join

insert overwrite table temp
select a.name,a.age,b.count,b.year,b.mon,b.day,b.hour
from a left join b  on a.name=b.name;

左连接查询结果:    select * from temp;

xuhaitao        36      15      2020    10      1       6
xuhaitao        36      13      2020    10      1       9
xuhaitao        36      15      2020    10      1       6
hunkxu          33      9       2020    10      1       9
hunkxu          33      19      2020    10      1       19
xiaotao         28      2       2020    10      1       10
xiaotao         28      19      2020    10      1       9
xiaohong        3       NULL    NULL    NULL    NULL    NULL
xiaoshuo        4       NULL    NULL    NULL    NULL    NULL

---

 

---

下面测试一下  right join： 

 insert overwrite table temp
 select a.name,a.age,b.count,b.year,b.mon,b.day,b.hour
 from a right join b  on a.name=b.name;

 右连接查询结果:    select * from temp;

xuhaitao        36      15      2020    10      1       6
hunkxu          33      9       2020    10      1       9
xuhaitao        36      13      2020    10      1       9
hunkxu          33      19      2020    10      1       19
xiaotao         28      2       2020    10      1       10
xuhaitao        36      15      2020    10      1       6
xiaotao         28      19      2020    10      1       9
NULL            NULL    19      2020    10      1       9

---

---

 

FR：徐海涛（hunk Xu)
QQ技术交流群：386476712