MySQL全面瓦解12：连接查询的原理和应用

爱新觉羅丶高

发布于 2022-7-27 15:50

浏览

0收藏

概述
MySQL最强大的功能之一就是能在数据检索的执行中连接（join）表。大部分的单表数据查询并不能满足我们的需求，这时候我们就需要连接一个或者多个表，并通过一些条件过滤筛选出我们需要的数据。

了解MySQL连接查询之前我们先来理解下笛卡尔积的原理。

数据准备
依旧使用上节的表数据（包含classes 班级表和students 学生表）：

ysql> select * from classes;
+---------+-----------+
| classid | classname |
+---------+-----------+
|       1 | 初三一班  |
|       2 | 初三二班  |
|       3 | 初三三班  |
|       4 | 初三四班  |
+---------+-----------+
4 rows in set

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|         1 | brand       | 97.5  |       1 |
|         2 | helen       | 96.5  |       1 |
|         3 | lyn         | 96    |       1 |
|         4 | sol         | 97    |       1 |
|         7 | b1          | 81    |       2 |
|         8 | b2          | 82    |       2 |
|        13 | c1          | 71    |       3 |
|        14 | c2          | 72.5  |       3 |
|        19 | lala        | 51    |       0 |
+-----------+-------------+-------+---------+
9 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

笛卡尔积
笛卡尔积：也就是笛卡尔乘积，假设两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。

比如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

所以上面的表就是 4（班级表）* 9（学生表） = 36条数据；

笛卡尔积语法格式：

 select cname1,cname2,... from tname1,tname2,...;
 or
 select cname from tname1 join tname2 [join tname...];1.
2.
3.

MySQL全面瓦解12：连接查询的原理和应用-鸿蒙开发者社区

上述两个表实际执行结果如下：

  mysql> select * from classes a,students b order by a.classid,b.studentid;
 +---------+-----------+-----------+-------------+-------+---------+
 | classid | classname | studentid | studentname | score | classid |
 +---------+-----------+-----------+-------------+-------+---------+
 |       1 | 初三一班  |         1 | brand       | 97.5  |       1 |
 |       1 | 初三一班  |         2 | helen       | 96.5  |       1 |
 |       1 | 初三一班  |         3 | lyn         | 96    |       1 |
 |       1 | 初三一班  |         4 | sol         | 97    |       1 |
 |       1 | 初三一班  |         7 | b1          | 81    |       2 |
 |       1 | 初三一班  |         8 | b2          | 82    |       2 |
 |       1 | 初三一班  |        13 | c1          | 71    |       3 |
 |       1 | 初三一班  |        14 | c2          | 72.5  |       3 |
 |       1 | 初三一班  |        19 | lala        | 51    |       0 |
 |       2 | 初三二班  |         1 | brand       | 97.5  |       1 |
 |       2 | 初三二班  |         2 | helen       | 96.5  |       1 |
 |       2 | 初三二班  |         3 | lyn         | 96    |       1 |
 |       2 | 初三二班  |         4 | sol         | 97    |       1 |
 |       2 | 初三二班  |         7 | b1          | 81    |       2 |
 |       2 | 初三二班  |         8 | b2          | 82    |       2 |
 |       2 | 初三二班  |        13 | c1          | 71    |       3 |
 |       2 | 初三二班  |        14 | c2          | 72.5  |       3 |
 |       2 | 初三二班  |        19 | lala        | 51    |       0 |
 |       3 | 初三三班  |         1 | brand       | 97.5  |       1 |
 |       3 | 初三三班  |         2 | helen       | 96.5  |       1 |
 |       3 | 初三三班  |         3 | lyn         | 96    |       1 |
 |       3 | 初三三班  |         4 | sol         | 97    |       1 |
 |       3 | 初三三班  |         7 | b1          | 81    |       2 |
 |       3 | 初三三班  |         8 | b2          | 82    |       2 |
 |       3 | 初三三班  |        13 | c1          | 71    |       3 |
 |       3 | 初三三班  |        14 | c2          | 72.5  |       3 |
 |       3 | 初三三班  |        19 | lala        | 51    |       0 |
 |       4 | 初三四班  |         1 | brand       | 97.5  |       1 |
 |       4 | 初三四班  |         2 | helen       | 96.5  |       1 |
 |       4 | 初三四班  |         3 | lyn         | 96    |       1 |
 |       4 | 初三四班  |         4 | sol         | 97    |       1 |
 |       4 | 初三四班  |         7 | b1          | 81    |       2 |
 |       4 | 初三四班  |         8 | b2          | 82    |       2 |
 |       4 | 初三四班  |        13 | c1          | 71    |       3 |
 |       4 | 初三四班  |        14 | c2          | 72.5  |       3 |
 |       4 | 初三四班  |        19 | lala        | 51    |       0 |
 +---------+-----------+-----------+-------------+-------+---------+
 36 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

这样的数据肯定不是我们想要的，在实际应用中，表连接时要加上限制条件，才能够筛选出我们真正需要的数据。

我们主要的连接查询有这几种：内连接、左(外)连接、右(外)连接，下面我们一一来看。

内连接查询 inner join
语法格式：

select cname from tname1 inner join tname2 on join condition;
 或者
 select cname from tname1 join tname2 on join condition;
 或者
 select cname from tname1,tname2 [where join condition];1.
2.
3.
4.
5.

说明：在笛卡尔积的基础上加上了连接条件，组合两个表，返回符合连接条件的记录，也就是返回两个表的交集（阴影）部分。如果没有加上这个连接条件，就是上面笛卡尔积的结果。

MySQL全面瓦解12：连接查询的原理和应用-鸿蒙开发者社区

  mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid;
  +-----------+-------------+-------+
  | classname | studentname | score |
  +-----------+-------------+-------+
  | 初三一班  | brand       | 97.5  |
  | 初三一班  | helen       | 96.5  |
  | 初三一班  | lyn         | 96    |
  | 初三一班  | sol         | 97    |
  | 初三二班  | b1          | 81    |
1 | 初三二班  | b2          | 82    |
1 | 初三三班  | c1          | 71    |
1 | 初三三班  | c2          | 72.5  |
1 +-----------+-------------+-------+
1 8 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

从上面的数据可以看出，初三四班 classid = 4，因为没有关联的学生，所以被过滤掉了；lala 同学的classid=0，没法关联到具体的班级，也被过滤掉了，只取两表都有的数据交集

 mysql> select a.classname,b.studentname,b.score from classes a,students b where a.classid = b.classid and a.classid=1;
 +-----------+-------------+-------+
 | classname | studentname | score |
 +-----------+-------------+-------+
 | 初三一班  | brand       | 97.5  |
 | 初三一班  | helen       | 96.5  |
 | 初三一班  | lyn         | 96    |
 | 初三一班  | sol         | 97    |
 +-----------+-------------+-------+
 4 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

查找1班同学的成绩信息，上面语法格式的第三种，这种方式简洁高效，直接在连接查询的结果后面进行Where条件筛选。

左连接查询 left join
left join on / left outer join on，语法格式：

 select cname from tname1 left join tname2 on join condition;
1.

说明：left join 是left outer join的简写，全称是左外连接，外连接中的一种。左(外)连接，左表(classes)的记录将会全部出来，而右表(students)只会显示符合搜索条件的记录。右表无法关联的内容均为null。

MySQL全面瓦解12：连接查询的原理和应用-鸿蒙开发者社区

 mysql> select a.classname,b.studentname,b.score from classes a left join students b on a.classid = b.classid;
 +-----------+-------------+-------+
 | classname | studentname | score |
 +-----------+-------------+-------+
 | 初三一班  | brand       | 97.5  |
 | 初三一班  | helen       | 96.5  |
 | 初三一班  | lyn         | 96    |
 | 初三一班  | sol         | 97    |
 | 初三二班  | b1          | 81    |
 | 初三二班  | b2          | 82    |
 | 初三三班  | c1          | 71    |
 | 初三三班  | c2          | 72.5  |
 | 初三四班  | NULL        | NULL  |
 +-----------+-------------+-------+
 9 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

从上面结果中可以看出，初三四班无法找到对应的学生，所以后面两个字段使用null标识。

右连接查询 right join
right join on / right outer join on，语法格式：

select cname from tname1 right join tname2 on join condition;1.

说明：right join是right outer join的简写，全称是右外连接，外连接中的一种。与左(外)连接相反，右(外)连接，左表(classes)只会显示符合搜索条件的记录，而右表(students)的记录将会全部表示出来。左表记录不足的地方均为NULL。

MySQL全面瓦解12：连接查询的原理和应用-鸿蒙开发者社区

 mysql> select a.classname,b.studentname,b.score from classes a right join students b on a.classid = b.classid;
 +-----------+-------------+-------+
 | classname | studentname | score |
 +-----------+-------------+-------+
 | 初三一班  | brand       | 97.5  |
 | 初三一班  | helen       | 96.5  |
 | 初三一班  | lyn         | 96    |
 | 初三一班  | sol         | 97    |
 | 初三二班  | b1          | 81    |
 | 初三二班  | b2          | 82    |
 | 初三三班  | c1          | 71    |
 | 初三三班  | c2          | 72.5  |
 | NULL      | lala        | 51    |
 +-----------+-------------+-------+
 9 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

从上面结果中可以看出，lala同学无法找到班级，所以班级名称字段为null。

连接查询+聚合函数
使用连接查询的时候，经常会配合使用聚集函数来进行数据汇总。比如在上面的数据基础上查询出每个班级的人数和平均分数、班级总分数。

mysql> select a.classname as '班级名称',count(b.studentid) as '总人数',sum(b.score) as '总分',avg(b.score) as '平均分' 
 from classes a inner join students b on a.classid = b.classid 
 group by a.classid,a.classname;
 +----------+--------+--------+-----------+
 | 班级名称 | 总人数 | 总分   | 平均分    |
 +----------+--------+--------+-----------+
 | 初三一班 |      4 | 387.00 | 96.750000 |
 | 初三二班 |      2 | 163.00 | 81.500000 |
 | 初三三班 |      2 | 143.50 | 71.750000 |
 +----------+--------+--------+-----------+
 3 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

这边连表查询的同时对班级（classid，classname）做了分组，并输出每个班级的人数、平均分、班级总分。

连接查询附加过滤条件
使用连接查询之后，大概率会对数据进行在过滤筛选，所以我们可以在连接查询之后再加上where条件，比如我们根据上述的结果只取出一班的同学信息。

mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid where a.classid=1;
 +-----------+-------------+-------+
 | classname | studentname | score |
 +-----------+-------------+-------+
 | 初三一班  | brand       | 97.5  |
 | 初三一班  | helen       | 96.5  |
 | 初三一班  | lyn         | 96    |
 | 初三一班  | sol         | 97    |
 +-----------+-------------+-------+
 4 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

如上，只输出一班的同学，同理，可以附件 limit 限制，order by排序等操作。

总结
1、连接查询必然要带上连接条件，否则会变成笛卡尔乘积数据，使用不正确的联结条件，也将返回不正确的数据。

2、SQL规范推荐首选INNER JOIN语法。但是连接的几种方式本身并没有明显的性能差距，性能的差距主要是由数据的结构、连接的条件，索引的使用等多种条件综合决定的。

我们应该根据实际的业务场景来决定，比如上述数据场景：如果要求返回返回有学生的班级就使用 inner join；如果必须输出所有班级则使用left join；如果必须输出所有学生，则使用right join。

3、性能上的考虑，MySQL在运行时会根据关联条件处理连接的表，这种处理可能是非常耗费资源的，连接的表越多，性能下降越厉害。所以要分析去除那些不必要的连接和不需要显示的字段。

之前我的项目团队在优化旧的业务代码时，发现随着业务的变更，某些数据不需要显示，对应的某个连接也不需要了，去掉之后，性能较大提升。

文章转载自公众号：架构与思维

分类