vlambda博客
学习文章列表

前言

上一篇文章，我们分享了SQL数据查询语句中的常见函数和分组查询的用法，详细内容可查看链接：

下面将继续分享SQL数据查询的连接查询、子查询，以及联合查询。

 1   

连接查询

我们知道，为了方便处理数据，拥有更大的可伸缩性，以及防止数据冗余，我们会将数据进行分类存储。这时，连接查询在查询数据中就起到巨大作用了。连接查询又称多表查询，当查询的字段来自多个表时，就会用到该查询，分为内连接和外连接两大类，其中，内连接包括：等值连接、非等值连接、自连接；外连接包括：左外连接、右外连接，连接方式类似于Excel中的vlookup函数。在日常工作中，用得最多的是内连接（inner join）、左连接（left join）、右连接（right join）。

▌内连接

内连接的语法如下：

SELECT <select_list>

FROM A 

INNER JOIN B 

ON A.key = B.key

其意思是，以key作为连接条件合并两个表，返回A表和B表的交集部分。例如：

SELECT 学员信息表.*, 学员成绩表. *FROM 学员信息表 INNER JOIN 学员成绩表 ON 学员信息表.学号 = 学员成绩表.学号

下方的表是上方两个表的交集内容，其中，a学员分别在两个表都存有两条数据，所以返回的结果中有4个（笛卡尔乘积，2*2）；e和f学员只存在其中一个表中，所以不返回他们的数据。

▌左连接

左连接的语法如下：

SELECT <select_list>

FROM A 

LEFT JOIN B 

ON A.key = B.key

其意思是，以key作为连接条件合并两个表，返回A表的全部和B表中与A相交部分。例如：

SELECT 学员信息表.*, 学员成绩表.*FROM 学员信息表 LEFT JOIN 学员成绩表 ON 学员信息表.学号 = 学员成绩表.学号

返回的结果包含了信息表的全部和成绩表中与信息表相交的内容，e学员在信息表，不在成绩表中，所以返回e学员的结果时，成绩表中显示null。

▌右连接

右连接的语法如下：

SELECT <select_list>

FROM A 

RIGHT JOIN B 

ON A.key = B.key

其意思是，以key作为连接条件合并两个表，返回B表的全部和A表中与B相交部分，与左连接是相反的。例如：

SELECT 学员信息表.*, 学员成绩表.*FROM 学员信息表 RIGHT JOIN 学员成绩表 ON 学员信息表.学号 = 学员成绩表.学号

返回的结果包含了成绩表的全部和信息表中与成绩表相交的内容，f学员在成绩表，不在信息表中，所以返回f学员的结果时，信息表中显示null。

▌on where 和 on and 的区别

通常情况下，我们会将连接查询和条件查询结合使用，这时，在两表的连接条件（on）后面可以用where或and进行条件筛选。例如：

SELECT 学员信息表.*, 学员成绩表.*FROM 学员信息表 LEFT JOIN 学员成绩表 ON 学员信息表.学号 = 学员成绩表.学号WHERE 学员成绩表.学号 = 'c'   或[ AND 学员成绩表.学号 = 'c' ]

如上图显示，很明显on后面接where和接and的结果是不一样的。原因是：两表相连接后，相当于产生了一个虚拟表，where是在产生虚拟表之后（连接后），再从虚拟表做条件筛选；而当使用and时，是在产生虚拟表之前（连接前），通过筛选 “ 学员成绩表.学号 = 'c' ”作为连接（此时相当于成绩表只有c这一条数据），于是其他筛选条件外的返回null，这就是on后面接where和接and的区别：and连接前筛选，where连接后筛选。

 2   

子查询

出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询，类似于嵌套函数，执行顺序是由里到外。子查询有两大分类：

1、按结果集的行列数不同分为：

• 标量子查询（结果集只有一行一列）

• 列子查询（结果集只有一行多列）

• 行子查询（结果集有一行多列）

• 表子查询（结果集一般为多行多列）

2、按子查询出现的位置分为：

• select后面：仅仅支持标量查询

• from后面：支持表子查询

• where或having后面：支持标量子查询、列子查询、行子查询

• exists后面（相关查询）：支持表子查询

▌select后面

例如：查询员工号 = 102的部门名。

SELECT ( SELECT department_name FROM departments d INNER JOIN employees e ON d.department_id =e.department_id WHERE e.employee_id = 102) 部门名;

括号内的即为子查询，意思是：departments表和employees表进行内连接，department_id作为连接条件，筛选出employee_id为102信息，返回部门名，然后再将子查询放在select后面。

▌from后面

例如：查询每个部门的平均工资的工资等级。

SELECT ag_dep.*,g.grade_levelFROM ( SELECT department_id,AVG(salary) ag FROM employees GROUP BY department_id) ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

首先，查询每个部门的平均工资，也是from后面括号内的语句；其次，连接子查询的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal，子查询放在from后面时必须要起别名。

▌where或having后面

例如：谁的工资比Abel高？

SELECT *FROM employeesWHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel');

首先，查询Abel的工资，也是where后面括号内的语句；其次，查询员工信息，满足工资>子查询的结果。这个也叫标量子查询，一般搭配着单行操作符使用（>  <  >=  =  <>）。

例如：返回location_id是1400或1700的部门中的所有员工姓名。

SELECT last_nameFROM employeesWHERE department_id IN ( SELECT DISTINCT department_id  FROM departments WHERE location_id IN (1400,1700));

首先，查询location_id是1400或1700的部门编号，其次，查询员工姓名，要求部门号是子查询列表中的某一个。这个也叫列子查询，一般搭配着多行操作符使用（in、any/some、all）。

放在having后面也是同理，这里就不举例了。

▌exists后面

例如：查询有员工的部门名。

SELECT department_nameFROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.department_id = e.department_id);

该类查询也叫相关查询，语法：select exists(完整的查询语句)，结果：1 或 0。

 3   

联合查询

联合查询的作用是将多条查询语句的结果合并成一个结果，要求多条查询语句的查询列数是一致的，查询的每一列的类型和顺序最好一致。

▌union 和 union all

union：用于合并多个select语句的结果集，消去任何重复行；

union all：用于合并多个select语句的结果集，保留重复行。例如：

SELECT 学员信息表.学号FROM 学员信息表UNION 或[UNION ALL]SELECT 学员成绩表.学号FROM 学员成绩表

总结

本文主要分享了连接查询、子查询，以及联合查询。其中，连接查询最常用的是内连接（inner join）、左连接（left join）、右连接（right join）；子查询语句可以放在select 、from、where或having、exists后面；联合查询时去重用union，保留重复项用union all。

以上是今天的分享，下一篇文章重点分享常用且面试必考的“窗口函数”，欢迎持续关注哦~