大数据之Hive：hive中的join函数

最新推荐文章于 2024-05-11 08:46:25 发布

浊酒南街

最新推荐文章于 2024-05-11 08:46:25 发布

阅读量1.7k

点赞数 5

分类专栏： # 大数据系列二

本文链接：https://blog.csdn.net/weixin_43597208/article/details/117375023

版权

110 篇文章 0 订阅

订阅专栏

1.hive中join与mysql中join的异同

mysql中没有left semi join，full join ,但是hive中有；
其他join，left join right join 两者都有；

full join 使用on条件时，select * 相当于把两个表(左表有m列p行和右表有n列q行)的所有列拼接成了一个有m+n列的结果表。

select * from table1 full join table2 on(table1.student_no=table2.student_no);

2）union 相当于把两个查询结果(左查询结果表有m列p行和右查询结果表有n列q行)的所有行进行了拼接，形成具有p+q行的查询结果。

select student_no tb1_student_no,student_name from table1 union select student_no as tb2_student_no,class_no from table2;

备注：union要求两个表的列数相同（要查取的列数）且列的数据类型相同；
3）union和union all的区别
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
Union All：对两个结果集进行并集操作，包括重复行，不进行排序。

测试子查询：在Hive 2.1.1版本中，是支持where子句中的子查询 in 和 not in；

SELECT table1.student_no, table1.student_name FROM table1 WHERE table1.student_no in (SELECT table2.student_no FROM table2);

SELECT table1.student_no, table1.student_name FROM table1 LEFT SEMI JOIN table2 on ( table1.student_no =table2.student_no);

上面两个语句查询结果相同；
此外，需要注意以下几项：
1、left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
对右表的过滤条件只能写在on子句中：

hive> SELECT * FROM table1 LEFT SEMI JOIN table2 on ( table1.student_no =table2.student_no and  table2.student_no>3);

在这里插入图片描述

select * from table1  join table2 on table1.student_no=table2.student_no;

select * from table1  join table2 on table1.student_no=table2.student_no where table2.student_no is not null ;

在这里插入图片描述

select * from table1 left join table2 on(table1.student_no=table2.student_no);

在这里插入图片描述

select * from table1 left outer join table2 on table1.student_no=table2.student_no where table2.student_no is null;

在这里插入图片描述

select * from table1 right join table2 on(table1.student_no=table2.student_no);

在这里插入图片描述

select * from table1 right join table2 on(table1.student_no=table2.student_no) where table1.student_no is  null;

在这里插入图片描述

select * from table1 full  join table2 on(table1.student_no=table2.student_no);

在这里插入图片描述

select * from table1 full join table2 on table1.student_no=table2.student_no where table2.student_no is null or table1.student_no is null ;

关注