【Mysql】初探表连接的原理

在我们的工作学习中肯定都用到过表连接的操作，不同连接写法在执行效率上会有不小的区别，要想写出高效的表连接语句，还是需要我们知晓表连接的原理。

什么是连接

连接的本质就是要连接在一起的表中符合条件的结果集组合在一起，然后返回给用户的过程。

准备

# 创建两个表t1,t2
CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
# 向t1,t2中插入几条数据
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

#表结构
mysql> select * from t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

连接t1,t2

mysql> select * from t1,t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    2 | b    |
|    2 | b    |    2 | b    |
|    1 | a    |    2 | b    |
|    3 | c    |    3 | c    |
|    2 | b    |    3 | c    |
|    1 | a    |    3 | c    |
|    3 | c    |    4 | d    |
|    2 | b    |    4 | d    |
|    1 | a    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)

上面这中连接其实就是取得t1和t2的笛卡尔积，t1有三条数据，t2也有三条数据，那么两个表的笛卡尔积就是3x3=9条数据，所以不推荐不加条件的对两张表进行连接操作。
那么加上条件是什么效果呢？

mysql> select * from t1,t2 where t1.m1>1 and t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

可以看到，我们在加了查询条件之后，缩小了查询范围，也查询到了想要的数据。

驱动表与被驱动表

连接表的过程需要确定一个驱动表，也就是第一次被查询的表，确定驱动表的过程，只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询）
我们这里把t1当作驱动表，t2作为被驱动表，上面sql的查询过程大致这样：

• 先去t1表中查询符合条件的数据，即m1>1的数据，符合条件有两条数据
• 然后在查询被驱动表 t2，当m1=2时，在t2表中查找符合 t1.m1=t2.m2，即t2.m2=2的记录，查询到一条
• 然后再次查询被驱动表 t2,当m1=3时，在t2表中查找符合 t1.m1=t2.m2，即t2.m2=3的记录，查询到一条
  所以最终符合条件的结果为两条记录；

内连接和外连接

内连接：对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集
外连接：对于外连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录仍然会加入到最后的结果集

举个例子：

mysql> select * from t1,t2 where t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

在t1 驱动表中不存在的m2=4，以及在t2表中不存在m1=1，都没有加入结果中

左（外）连接

所谓的左（外）连接，就是指定要连接的表中，最左侧的为驱动表，其他的作为被驱动表，然后使用on指定连接条件，从而将表进行连接;

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

mysql> select * from t1 left join t2 on t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
3 rows in set (0.00 sec)

on 与 where的区别

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

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

小节：内连接是根据where中的条件先从驱动表中查询然后再分别去被驱动表中查询，外连接通过关键字on指定了查询条件，在驱动表中根据on指定的条件去被驱动表中查询符合条件的记录，如果没有在驱动表中找到符合条件的记录，那么也会保留驱动表中的结果。

右（外）连接

所谓的右（外）连接，就是指定要连接的表中，最右侧的为驱动表，其他的作为被驱动表，然后使用on指定连接条件，从而将表进行连接;

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

mysql> select * from t1 right join t2 on t1.m1=t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)

内连接

学习了外连接，我们再来看内连接，可以发现，内连接和外连接的区别就是外连接通过关键字on指定了连接条件，当从被驱动表中没有找到符合条件的数据时，驱动表中的数据仍然保留，而内连接则是通过where条件进行筛选，不会保留驱动表中的数据。

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
SELECT * FROM t1,t2 [WHERE 普通过滤条件] ;# 不推荐这种写法

当使用内连接时，on 后的条件等价于where条件，所以我们通常省略掉on关键字。

预告

下一篇，我们深入连接的原理！感谢阅读！