hive的几种join到底有何区别

待我称王封你为后i 2023-10-04 17:38 47阅读 0赞

#### 内容目录 ####

*   *   *  数据：
         *  1. left join
         *  2. join
         *  3. full join
         *  4. Join...on 1=1
         *  5. union
         *  6. union all

hive中，几种join的区别

#### 数据： ####

tom,1
    jey,2
    lilly,7
    lilly,8

tom,1
    lilly,3
    may,4
    bob,5

以上两个为数据，没有什么意义，全是为了检测join的使用

看一下两张表，其实可以看出来，在name一行有重复的，也有不重复的，在id一行1表完全包含2表

#### 1. left join ####

left join会把左边的表所有数据列出来，当左边表有而右边表没有的时候，就会用null代替  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]

select *
    from jn1
    left join jn2
    on jn1.name=jn2.name;

jn1.name	jn1.id	jn2.name	jn2.id
    tom	1	tom	1
    jey	2	NULL	NULL
    lilly	7	lilly	3
    lilly	8	lilly	3

而右表有左表没有的就不会显示了

#### 2. join ####

join会把两个表共有的部分筛选出来  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]

select *
    from jn1
    join jn2
    on jn1.name=jn2.name;

jn1.name	jn1.id	jn2.name	jn2.id
    tom	1	tom	1
    lilly	7	lilly	3
    lilly	8	lilly	3

可以看到，共有的部分筛选了出来

#### 3. full join ####

会把两者没有的有的全部数据都选出来，没有的显示空值  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]

select *
    from jn1
    full join jn2
    on jn1.name = jn2.name;

jn1.name	jn1.id	jn2.name	jn2.id
    tom	1	tom	1
    NULL	NULL	bob	5
    jey	2	NULL	NULL
    lilly	7	lilly	3
    lilly	8	lilly	3
    NULL	NULL	may	4

#### 4. Join…on 1=1 ####

这种情况会有笛卡尔积的产生，就是表1的每一行都会和表2匹配一下，这样就会产生指数级的增长  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]

select *
    from jn1
    join jn2
    on 1=1;

jn1.name	jn1.id	jn2.name	jn2.id
    tom	1	lilly	3
    tom	1	bob	5
    tom	1	may	4
    tom	1	tom	1
    jey	2	lilly	3
    jey	2	bob	5
    jey	2	may	4
    jey	2	tom	1
    lilly	7	lilly	3
    lilly	7	bob	5
    lilly	7	may	4
    lilly	7	tom	1
    lilly	8	lilly	3
    lilly	8	bob	5
    lilly	8	may	4
    lilly	8	tom	1

#### 5. union ####

会把查询结果拼接起来，但是要求两个查询结果的行数必须保持一致

否则会报错

FAILED: SemanticException Schema of both sides of union should match

select *
    from jn1
    union
    select * 
    from jn2;

_u1.name	_u1.id
    jey	2
    lilly	7
    bob	5
    lilly	3
    lilly	8
    tom	1
    may	4

#### 6. union all ####

union会组合起来，那么union all可以不

select *
    from jn1
    union all
    select * 
    from jn2;

lilly	3
    tom	1
    may	4
    bob	5
    tom	1
    jey	2
    lilly	7
    lilly	8

1.  union和union all的区别
    
    从上述的两个结果就可以看出来了两者的区别，union会自动去重处理，所以结果把重复的数据去掉了，而union all则不会去重。  
    ![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
    ![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]  
    注意tips：left join会用之后，right join不用说了吧

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/2f26e138b4094e40aea46bb8f34051b6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/458589056c684a5096dccdf2c92971b3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/be56ef78c2584895b3eb3ab2050d34ac.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/193f8f7a70e44c1db4bd145ecb60b3d5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/c0bca65a3a8e4de2bb7b8e16c0be7ff0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/81790eafdcf64e5b8b81badd855a402d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16