MySQL-连表查询详

MySQL连表查询是一把双刃剑，一方面通过连表查询可以简化SQL的数量；但另一方面如果连接的表数据量较大，或者连接的表较多，其产生的笛卡尔积会成指数级增长，所以有些公司会禁用连表查询。

一、连表查询简介

连表查询即两张表或多张表联合查询，联合查询得到的结果称为笛卡尔积，假设A表中有n条记录，B表表中有m条记录，则它们联合查询得到的笛卡尔积为：n*m

大家可能听过很多种连接方式，比如内连接、外连接、自然连接、交叉连接、左连接以及右连接，但MySQL的连表查询其实只有3种：内连接、外连接、自然连接。 其中内连接等同于交叉连接，而外连接可以分为左外连接（左连接）和右外连接（右连接），仅此而已。

这是一张神图，可以辅助理解：

不管是哪种连接，本质上都是在总的笛卡尔积下进行筛选过滤而已。

二、内连接

内连接的关键字是 INNER JOIN，INNER JOIN等于CROSS JOIN 等于JOIN。

内连接分为两种情况，一种没有筛选条件；另一种是有ON或者WHERE筛选条件，假设我们有两张这样的表：

create table `table_a` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `a_name` varchar(255) NOT NULL,
  `age` smallint NOT NULL,
  PRIMARY KEY(`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表A';

create table `table_b` (
  `bid` int(11) NOT NULL AUTO_INCREMENT,
  `b_name` varchar(255) NOT NULL,
  `age` smallint NOT NULL,
  PRIMARY KEY(`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表B';

# 插入测试数据
INSERT INTO `table_a`(aid, a_name, age) VALUES(1, 'test1', 1),(2, 'test2', 2),(3, 'test3', 3);
INSERT INTO `table_b`(bid, b_name, age) VALUES(1, 'test2', 2),(2, 'test3', 3),(4, 'test4', 4);
复制代码

第一种情况：没有筛选条件，此时得到的结果是两张表的笛卡尔积，即3x3=9行

# 这三条SQL都是等价的
SELECT * FROM table_a JOIN table_b;
SELECT * FROM table_a INNER JOIN table_b;
SELECT * FROM table_a CROSS JOIN table_b;

# 结果如下：
mysql> SELECT * FROM table_a INNER JOIN table_b;
+-----+--------+-----+-----+--------+-----+
| aid | a_name | age | bid | b_name | age |
+-----+--------+-----+-----+--------+-----+
|   1 | test1  |   1 |   1 | test2  |   2 |
|   2 | test2  |   2 |   1 | test2  |   2 |
|   3 | test3  |   3 |   1 | test2  |   2 |
|   1 | test1  |   1 |   2 | test3  |   3 |
|   2 | test2  |   2 |   2 | test3  |   3 |
|   3 | test3  |   3 |   2 | test3  |   3 |
|   1 | test1  |   1 |   4 | test4  |   4 |
|   2 | test2  |   2 |   4 | test4  |   4 |
|   3 | test3  |   3 |   4 | test4  |   4 |
+-----+--------+-----+-----+--------+-----+
复制代码

第二种情况：有ON和WHERE筛选条件，此时得到的结果是两张表的交集（中间的图），对于内连接，ON和WHERE是等价的，但是对于外连接则不是，在下面会讲到。

# 这两条SQL是等价的，不过建议使用ON关键字，约定俗成
SELECT * FROM table_a a INNER JOIN table_b b ON a.a_name=b.b_name;
SELECT * FROM table_a a INNER JOIN table_b b WHERE a.a_name=b.b_name;

# 结果如下：
mysql> SELECT * FROM table_a a INNER JOIN table_b b ON a.a_name=b.b_name;
+-----+--------+-----+-----+--------+-----+
| aid | a_name | age | bid | b_name | age |
+-----+--------+-----+-----+--------+-----+
|   2 | test2  |   2 |   1 | test2  |   2 |
|   3 | test3  |   3 |   2 | test3  |   3 |
+-----+--------+-----+-----+--------+-----+
2 rows in set (0.00 sec)
复制代码

三、自然连接

自然连接的关键字是 NATURAL JOIN，自然连接与内连接的区别在于，自然连接是一种自动寻找连接条件的连接查询，即MySQL会自动寻找相同的字段名作为连接条件，当没有找到连接条件，就变成内连接（没有筛选条件的情况）。

内连接可以通过ON关键字指定不同的字段名

测试表和测试数据依旧如上，由于表A和表B有相同的字段名age，所以MySQL会自动将其作为连接条件：

# 可以看到第一列是age，与内连接还是有点不太一样的
mysql> SELECT * FROM table_a NATURAL JOIN table_b;
+-----+-----+--------+-----+--------+
| age | aid | a_name | bid | b_name |
+-----+-----+--------+-----+--------+
|   2 |   2 | test2  |   1 | test2  |
|   3 |   3 | test3  |   2 | test3  |
+-----+-----+--------+-----+--------+
复制代码

如果表A和表B没有相同的字段名，则会列出所有结果，这个大家可以自己尝试下。

四、外连接

外连接的关键字是 OUTER JOIN，从上图可以得到（下边的图），MySQL并不支持OUTER JOIN。

之所以有外连接，是因为内连接在一些场景下并不能满足我们的要求，内连接的原理是从每次A表取一条记录去B表中匹配，匹配成功则保留，匹配失败则放弃，直到A表的记录遍历完。

但有时候，我们需要保留匹配失败的记录，比如我们A表是学生表，B表是分数表，当我们拿学生去B表查分数时，如果没找到，我们是比较希望保留该学生的信息的，所以就出现了外连接。

前面提到，MySQL并不支持OUTER JOIN，但是左外连接和右外连接是支持的，他们的区别就在于要保留哪份表的数据。

4.1 左连接

左连接的关键字是 LEFT JOIN，从上图可以得到（左边的图），左连接其实就是两个表的交集+左表剩下的数据 ，当然这是在没其他过滤条件的情况下。

测试表和测试数据依旧和上面一致，测试如下：

# 没找到的被置为NULL
mysql> SELECT * FROM `table_a` a LEFT JOIN `table_b` b ON a.a_name=b.b_name;
+-----+--------+-----+------+--------+------+
| aid | a_name | age | bid  | b_name | age  |
+-----+--------+-----+------+--------+------+
|   2 | test2  |   2 |    1 | test2  |    2 |
|   3 | test3  |   3 |    2 | test3  |    3 |
|   1 | test1  |   1 | NULL | NULL   | NULL |
+-----+--------+-----+------+--------+------+
复制代码

4.2 右连接

右连接的关键字是 RIGHT JOIN，从上图可以得到（右边的图），右连接其实就是两个表的交集+右表剩下的数据 ，当然这是在没其他过滤条件的情况下。

mysql> SELECT * FROM `table_a` a RIGHT JOIN `table_b` b ON a.a_name=b.b_name;
+------+--------+------+-----+--------+-----+
| aid  | a_name | age  | bid | b_name | age |
+------+--------+------+-----+--------+-----+
|    2 | test2  |    2 |   1 | test2  |   2 |
|    3 | test3  |    3 |   2 | test3  |   3 |
| NULL | NULL   | NULL |   4 | test4  |   4 |
+------+--------+------+-----+--------+-----+
复制代码

4.3 ON 与 WHERE

WHERE子句中的过滤条件就是我们常见的那种，不管是内连接还是外连接，只要不符合WHERE子句的过滤条件，都会被过滤掉。

而ON子句中的过滤条件对于内连接和外连接是不同的，对于内连接，ON和WHERE的作用是一致的，因为匹配不到的都会过滤，所以你可以看到内连接并不强制需要 ON 关键字；但是对于外连接，ON决定匹配不到的是否要过滤，所以你可以看到外连接是强制需要 ON 关键字的。

4.4 表的先后顺序

我们知道，连接查询其实就是拿表A的记录去表B中匹配，查询的表称为驱动表，被查询的表称为被驱动表。

前面提到，连接查询是每次从表A拿一条记录去表B匹配，而表B匹配的效率是固定的（不管有没有做索引），所以你可以看到连接查询的效率取决于表A（驱动表）的记录数（行数），这也是为什么人们常说要用“小表驱动大表”的原因。

作者：言淦
链接：https://juejin.cn/post/7043811976270577672
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。