11.1 连接简介

11.1 连接的本质

create table t1 (m1 int ,n2 char(1));
create table t2 (m2 int, n2 char(1));
insert into t1 values (1,'a'),(2,'b'),(3,'c');
insert into t2 values (2,'b'),(3,'c'),(4,'d');

本质上来说，连接就是吧各个表中的记录都取出来进行一次匹配，病吧匹配后的组合发给客户端。

笛卡尔积

mysql> select * from t1,t2;

9 rows in set (0.00 sec)

11.1.2 连接过程简介

连接时，过滤掉 特定的记录组合是有必要的。过滤条件分为下面两种

• 涉及单表的条件
• 涉及两表的条件

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

在这个查询中有三个过滤条件

执行过程大概如下
步骤1 首先确定第一个需要查询的表，这个表成为驱动表
单表查询已经说过了: 只需要选取代价最小的那种访问方式即可，假设使用t1作为驱动表，那么就需要到t1表中查找满足t1.m1>1的记录，这里访问方法为all，全表查询

可以看到符合条件记录有两条

步骤2 从驱动表中没获取到一条记录，都需要到t2表中查找匹配的记录
也就是指符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，t2称为被驱动表。
从驱动表中获取到了两条记录，意味着需要查询2次t2表。

在两表的连接查询中，驱动表只需要被访问一次，被驱动表可能需要访问多次

并不是将所有满足条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表中查询的。而是每获得一条驱动表记录，就立刻到被驱动表中寻找匹配的记录

11.1.3 内连接和外连接

CREATE TABLE `student` (
  `number` int NOT NULL,
  `name` varchar(5) DEFAULT NULL COMMENT '姓名',
  `major` varchar(30) DEFAULT NULL COMMENT '专业',
  PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `score` (
  `number` int NOT NULL COMMENT '学号',
  `subject` varchar(30) NOT NULL COMMENT '科目',
  `score` tinyint DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`number`,`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入一些数据
要查询学生的考试成绩需要两表连接

select * from student,score where student.number = score.number;

select s1.number,s1.name,s2.subject,s2.score from student as s1,score as s2 where s1.number = s2.number;

王五同学因为某种原因没来考试，所以没有分数，但是缺考的学生成绩也应该展示出来。
针对驱动表中的某条记录，即使在被驱动表中没有找到阈值匹配的记录，也仍然需要把该驱动表记录加到结果集
有了内连接和外连接的概念

• 对于内连接的两个表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加到最后的结果集。
• 对于外连接的两个表，即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

在MySQL中，根据选取的驱动表的不同 外连接细分为两种

• 左外连接： 选取左侧的表为驱动表
• 右外连接：选取右侧的表为驱动表

有时候也不想吧驱动表的全部记录都加入到最后的结果集

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

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

  需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到皮胚记录时是否应该吧该驱动表记录加入结果集中这个场景提出的，所以如果吧ON子句放到内连接中，mysql会把他像where子句一样对待。也就是说，内连接中的where子句和ON子句是等价的。

1. 左(外)连接的语法

   selece * from table1 left [OUTER] join table2 ON 连接条件 [where 普通过滤条件]

   其中OUTER单词可以省略，对于LEFT JOIN类型的链接来说，左边的表称为外表或者驱动表，右边的表称为内表或者被驱动表，对于左(外)右(外)链接来说，必须使用ON子句来指出连接条件(内连接不必要包含ON子句)。

   select s1.number,s1.name,s2.subject,s2.score from student as s1 LEFT JOIN score as s2 ON s1.number = s2.number;

2. 右(外)连接语法
   和左外连接原理一样，只是把LEFT换成RIGHT而已
   驱动表是右边的表，被驱动表是左边的表
3. 内连接的语法
   内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时，内连接不会吧记录加入到最后的结果集中
   select * from t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [where]
select * from t1,t2等价于select * from t1 JOIN t2
4. 小结
   select * from t1 INNER JOIN t2 ON t1.m1 = t2.m2

select * from t1 LEFT JOIN t2 ON t1.m1 = t2.m2

select * from t1 RIGHT JOIN t2 ON t1.m1 = t2.m2
   

11.2连接的原理

11.2.1 循环嵌套链接

驱动表只会被访问一遍，单被驱动表却要被访问好多遍，具体取决于对驱动表执行单表查询后的结果集中有多少条记录。对于内连接来说，驱动表无所谓。外连接的驱动表是固定的。

如果有3个表进行连接，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表成为了被驱动表，然后重复上面过程。称为嵌套循环连接，这是最简单也是最笨的一种查询算法。

11.2.2 使用索引加快连接速度

select * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd'

连接查询的查询列表和过滤条件中有时可能只涉及被驱动表的部分列，而这些列都是某个二级索引的一部分，在这种情况下即时不能使用eq_ref、ref、ref_or_null或者range等访问方法来查询被驱动表，可以通过index访问方法。