06：Hive从0到1系列学习：查询之join连接

太过爱你忘了你带给我的痛 2023-10-04 17:19 19阅读 0赞

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

*   *  查询（重点）
     *   *  Join语句
         *   *  ①等值join
             *  ②内连接：join
             *  ③左外连接：left join
             *  ④右外连接：right join
             *  ⑤满外连接：full join
             *  ⑥多表连接
             *  ⑦笛卡尔积

### 查询（重点） ###

查询的基本语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list]
    [ORDER BY col_list]
    [CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]]
    [LIMIT number]

#### Join语句 ####

##### ①等值join #####

根据dept和emp中的deptno相等，查询empno、deptno、ename和dname

select 
        empno,
        dept.deptno,
        dept.ename,
        dname
    from emp
    join dept on emp.deptno=dept.deptno;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]

##### ②内连接：join #####

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]

select 
    	e.empno,
    	e.ename,
    	d.deptno,
    	d.dname
    from emp e
    join
    	dept d
    on
    	e.deptno=d.deptno;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]

##### ③左外连接：left join #####

将左边表的所有内容全部返回，如果右边表不存在，就返回数据null  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]

比如：返回emp表中所有数据的empno、ename、deptno、dname

select 
    	e.empno,
    	e.ename,
    	d.deptno,
    	d.dname
    from emp e
    left join 
    	dept d
    on
    	e.deptno=d.deptno;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]

**思考：如果想要得到只有A表独自有的该怎么办？**  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]

分析以下左外连接的结果  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 6]

如果是A独有的，也就是有关B的赋值就是null，只要把B表为null的过滤出来，就是A所独有的

select 
    	e.empno,
    	e.ename,
    	d.deptno,
    	d.dname
    from emp e
    left join 
    	dept d
    on
    	e.deptno=d.deptno
    where
    	d.deptno is null;

![在这里插入图片描述][cd6530f299734ced8a71d030d7d5f69e.png]

##### ④右外连接：right join #####

返回右边表的所有数据，左边表中如若没有数据的用null代替  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 7]

比如：返回所有部门的deptno、dname、empno、ename

select
    	d.deptno,
    	d.dname,
    	e.empno,
    	e.ename
    from emp e
    right join
    	dept d
    on
    	e.deptno=d.deptno;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 8]

**思考：如果想要得到B表所独有的该怎么办？**  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 9]

同理，也是过滤出A表为空的就可以得到B表所独有的

select
    	d.deptno,
    	d.dname,
    	e.empno,
    	e.ename
    from emp e
    right join
    	dept d
    on
    	e.deptno=d.deptno
    where
    	e.deptno is null;

![在这里插入图片描述][d26cd74a02e4434a97f9fc9ff34d74b4.png]

##### ⑤满外连接：full join #####

会返回两张表所有的数据，若没有就是null代替  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 10]

比如：返回emp和dept两张表的所有数据

select
    	e.empno,
    	e.ename,
    	d.deptno,
    	d.dname
    from emp e
    full join
    	dept d
    on 
    	e.deptno=d.deptno;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 11]

##### ⑥多表连接 #####

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 12]

比如返回emp表和dept表和location表相同的数据

select 
    	e.empno,
    	e.ename,
    	d.deptno,
    	d.dname,
    	l.loc_name
    from emp e
    join dept d on e.deptno=d.deptno
    join location l on d.loc=l.loc;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 13]

##### ⑦笛卡尔积 #####

会使得一个表中的数据逐次全部匹配另一个表中的数据

笛卡尔积产生的原因

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

比如不用连接条件，之间返回emp和dept的数据

select 
    	empno, 
    	dname 
    from emp, dept;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_16_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]: https://img-blog.csdnimg.cn/7036e1f371c24286962864b25a2173c0.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/dbdad2f62edb4563b3968517eabfdcfb.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/b923dd1dd9b3410f9752a908b98b95db.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/c1ad1b6a7c8d45fcb103cb5a1fef6276.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/efef99dd41d5452fad041e4720794627.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/1de0b78da72a434a8f3f26f8b41096a2.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 6]: https://img-blog.csdnimg.cn/1027fc4941434d8b8da8171f6b9f1f20.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[cd6530f299734ced8a71d030d7d5f69e.png]: https://img-blog.csdnimg.cn/cd6530f299734ced8a71d030d7d5f69e.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/22fba9cf938a4c7ab396a652e1f42434.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 8]: https://img-blog.csdnimg.cn/1b07507601ae4bd48182bbd71f1004b4.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 9]: https://img-blog.csdnimg.cn/cc1d500130e94b68a7210f8700ec8782.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[d26cd74a02e4434a97f9fc9ff34d74b4.png]: https://img-blog.csdnimg.cn/d26cd74a02e4434a97f9fc9ff34d74b4.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 10]: https://img-blog.csdnimg.cn/8b075508a5c8494aadfd2bea806889a1.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 11]: https://img-blog.csdnimg.cn/018ca668a7ab4f26918fab7097186974.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 12]: https://img-blog.csdnimg.cn/0915f83424204fc49e93eeae101ade84.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 13]: https://img-blog.csdnimg.cn/152ef2a6b1ed42eaa591f24662d91f99.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_16_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/c63e910bf4954fef8b2a1cd205f84eda.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_16,color_FFFFFF,t_70,g_se,x_16