MySQL必知之SQL执行原理（二）—— 连接查询

连接查询

搞数据库一个避不开的概念就是 join，中文就是连接的意思。使用的时候常常陷入下边两个误区。
误区一：业务之上，管他三七二十一，再复杂的查询也在一个连接语句中搞定。
误区二：敬而远之，上次慢查询就是因为使用了连接导致的，以后再也不敢用了。

了解关联查询，对数据查询、性能优化至关重要，理解关联查询，在工作中用好SQL连接，才能更好地让程序服务于业务。

连接的本质

为了方便描述，我们建立两个简单的演示表并给他们写入数据

create table e1(m1 int, n1 char(1));
create table e2(m2 int, n2 char(1));

insert into e1 values(1,'a'),(2,'b'),(3,'c');
insert into e2 values(2,'b'),(3,'c'),(4,'d');

执行结果的数据如下

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

所以我们把 e1 和 e2 两个表连接起来的过程如下图所示

这个过程看起来就是把 e1 表的记录和 e2 的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积。

因为表 e1 中有3条记录，表 e2 中也有3条记录，所以这个表连接之后的笛卡尔积就有 3 x 3 = 9行记录。

在MySQL中，连接查询的语法很随意，只要在 FROM 关键字后跟多个表名就好了，比如我们把 e1 和 e2 表连接起来的查询语句可以写成这样：

select * from e1,e2;

连接过程简介

我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接产生的笛卡尔积就有100x100x100=1000000行数据！所以在连接的时候过滤掉特定记录组合是必要的，在连接查询中大的过滤条件可以分为两种，比方说下边这个查询语句

select * from e1,e2 where e1.m1 > 1 and e1.m2 = e2.m2 and e2.n2 < 'd';

涉及单表的条件

• e1.m1 > 1 是只针对e1表的过滤条件
• e2.n2 < ‘d’ 是只针对e2表的过滤条件

涉及两表的条件
比如类似e1.m1 = e2.m2，这些条件中涉及到了两个表。
看下携带的过滤条件的连接查询的大致执行过程在这个查询中我们指明了这三个过滤条件：

• e1.m1 > 1
• e1.m1 = e2.m2
• e2.n2 < ‘d’

那么这个连接查询的大致执行过程如下
确定驱动表（t1）
首先确定第一个需要查询的表，这个表称之为驱动表。单表中执行查询语句只需要选择代价最小的那种访问方法去执行单表查询语句就好了（就是说之前从执行计划中找const、ref、ref_or_null、range、all等这些执行方法中取代价最小的执行查询）。
此处假设使用e1作为驱动表，那么就需要到e1表中满足 e1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询e1表的访问方法就设定为all，也就是采用全表扫描的方式执行单表查询。
遍历驱动表结果，到被驱动表（t2）中查找匹配记录
针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到e2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。
因为根据e1表中的记录去找e2表中的记录，所以e2表也可以称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次e2表。
此时涉及两个表的列的过滤条件 e1.m1=e2.m2 就派上用场了。
当 e1.m1 = 2 时，过滤条件 e1.m1 = e2.m2 就相当于 e2.m2 = 2，所以此时e2表相当于有了e2.m2 = 2、e2.n2 < ‘d’,这两个过滤条件，然后到e2表执行单表查询。
当 e1.m1 = 3 时，过虑条件 e1.m1 = e2.m2 就相当于 e2.m2 = 3，所以此时e2表相当于有了 e2.m2 = 3、e2.n2 < ‘d’，这两个过滤条件，然后到e2表中执行单表查询。
所以整个连接查询的执行过程就如下图所示

也就是说整个连接查询最后的结果只有两条符合过滤条件的记录：
从上边两个步骤可看出，这两个表连接查询共需要查询1次e1表，2次e2表。
当然这是在特定的顾虑条件下的结果，如果我们把 e1.m1 > 1 这个条件去掉，那么从e1表中查出的记录就有3条，就需要查询3次e2表了。也就是说，在两表连接查询中，驱动表只需要访问1次，被驱动表可能被访问多次。

内连接和外连接

为了大家更好地理解后边内容，我们创建两个有现实意义的表，并插入一些数据：

CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '客户信息表';

CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, subject)
) Engine=InnoDB CHARSET=utf8 COMMENT '客户成绩表';

两张表插入以下数据


现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了（因为score中没有姓名信息，所以不能单纯只查询score表）。连接过程就是从student表中取出记录，在score表中查找number相同的成绩记录，所以过滤条件就是student.number = socre.number，整个查询语句就是这样：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1,score AS s2 WHERE s1.number = s2.number;

从上述查询结果中我们可以看到，各个同学对应的各科成绩就都被查出来了。
可是有个问题，yan同学，也就是学号为20200904的同学因为某些原因没有参加考试，所以在score表中没有对应的成绩记录。

如果老师想查看所有同学的考试成绩，即使是缺考的同学也应该展示出来，但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一下这个需求，其本质是想： 驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集 。为了解决这类问题，就有了内连接和外连接的概念：
对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：
左外连接 ，选取左侧的表为驱动表。
右外连接 ，选取右侧的表为驱动表。
可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。
这就犯难了，怎么办？把过滤条件分为两种就可以就解决这个问题了，所以放在不同地方的过滤条件是有不同语义的：

WHERE子句中的过滤条件
WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

ON子句中的过滤条件
对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

左（外）连接的语法

左（外）连接的语法还是挺简单的，比如我们要把e1表和e2表进行左外连接查询可以这么写：

SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];

其中中括号里的OUTER单词是可以省略的。
对于 LEFT JOIN 类型的连接来说，我们把放在左边的表称之为外表或者驱动表；右边的表称之为内表或者被驱动表。
所以上述例子中e1就是外表或者驱动表，e2就是内表或者被驱动表。需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。了解了左（外）连接的基本语法之后，再次回到我们上边那个现实问题中来，看看怎样写查询语句才能把所有的客户的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;

从结果集中可以看出来，虽然yan并没有对应的成绩记录，但是由于采用的是连接类型为左（外）连接，所以仍然把她放到了结果集中，只不过在对应的成绩记录的各列使用NULL值填充而已。

右（外）连接的语法

右（外）连接和左（外）连接的原理是一样的，语法也只是把LEFT换成RIGHT而已：

SELECT * FROM e1
RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];

只不过驱动表是右边的表e2，被驱动表是左边的表e1。

内连接的语法

内连接和外连接的根本区别就是在驱动表中的记录不符合 ON 子句中的连接条件时不会把该记录加入到最后的结果集，一个中最简单的内连接语法，就是直接把需要连接的多个表都放到from子句后面。其实针对内连接，MySQL提供了好多不同的语法：

SELECT * FROM e1 [INNER | CROSS] JOIN e2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下边这几种内连接的写法都是等价的

select * from e1 join e2;
select * from e1 inner join e2;
select * from e1 cross join e2;

上边的这些写法和直接把需要连接的表明放到 FROM 语句之后，用逗号，分隔开的写法是等价的：

SELECT * FROM e1, e2;

再说一次，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。
我们前边说过，连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉，其实也就相当于两表连接的笛卡尔积中把不符合过滤条件的记录踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。
但对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。