《数据库基础语法》10. 使用 JOIN 实现多表查询

楔子

到目前为止，我们的查询都是从单个表中获取数据。下面我们开始探讨一下如何从多个表中获取相关的数据。因为在关系数据库中，通常将不同的信息和它们之间的联系存储到多个表中。比如产品表、用户表、用户订单表、以及关联的订单明细表等。当我们想要查看某个订单时，需要同时从这几个表中查找关于订单的全部信息。

作在 SQL 中，我们可以使用多表连接（JOIN）查询获取多个表中的关联数据。

join 连接

 

连接语法：

在 SQL 的发展过程中，出现了两种连接查询的语法：

• ANSI SQL/86 标准，使用 FROM 和 WHERE 关键字指定表的连接；
• ANSI SQL/92 标准，使用 JOIN 和 ON 关键字指定表的连接；

当前有两张表，一张叫girl_info、存储了id、name、age；另一张叫girl_score、存储了id、score。

如果我想知道某个女孩考了多少分，需要同时查询 girl_info 和 girl_score 两张表，这个问题就可以使用 FROM WHERE 解决。

SELECT a.name, b.score FROM girl_info AS a, girl_score AS b
WHERE a.id = b.id;

其中，FROM 子句中的逗号用于连接两个表；同时在 WHERE 子句中指定了连接的条件是 girl_info 中的 id 等于 girl_score 表中的 id。另外，该查询中还通过别名（a 和 b）指定了查询的字段来自哪个表，当然不使用别名、而是使用整个表名也是可以的，只不过比较长。

当然我们这里的id字段是不重复的，如果一方的id字段重复了怎么办？这个问题，我们先放在这里，后面再说。

对于同样的问题，我们看看如何使用 JOIN 和 ON 实现连接查询：

SELECT a.name, b.score FROM girl_info AS a JOIN girl_score AS b
ON a.id = b.id;

我们看到整体是差不多的，除了把两张表改成用 JOIN 连接，WHERE 改成 ON。

JOIN 表示连接 girl_info 和 girl_score 两张表，ON 则是用于指定连接条件，返回结果和上面是一样的。那么我们使用哪一种呢？

推荐使用 JOIN 和 ON 进行连接查询，它们的语义更清晰，更符合 SQL 的声明性；另外，我们知道where还可以指定整张表的过滤条件，那么当 WHERE 中包含多个查询条件，又用于指定表的连接关系时，会显得比较混乱。

所以推荐使用 JOIN 和 ON，至于 WHERE，它就用来对整张表进行过滤。

SELECT a.name, b.score
FROM girl_info AS a
         JOIN girl_score AS b
              ON a.id = b.id
WHERE a.id > 1003;
/*
芙兰朵露	81
雾雨魔理沙	100
坂上智代	86
 */
-- 表示只对girl_info中id大于1003的进行join

-- 当然，from where也是可以的
-- 只是我们把表的过滤、以及连接关系都写在了where里面
SELECT a.name, b.score
FROM girl_info AS a,
     girl_score AS b
WHERE a.id = b.id
  AND a.id > 1003
/*
芙兰朵露	81
雾雨魔理沙	100
坂上智代	86
 */

另外我们这里 ON 指定的是两张表的 id 字段相等，但是不一定是 id，两张表的其它字段也可以。并且指定的两张表的字段也可以不一样，比如让一张表的 id 和另一张表的 nid 相等之类的，on后面也可以指定多个条件，使用 AND 或者 OR 连接。

 

连接类型：

接下来我们详细介绍一下 SQL 中的各种连接类型。为了介绍连接类型，我们将表的数据改一下，当然结果不变，只是两张表都增加一条数据。

1001	古明地觉	16
1002	古明地恋	15
1003	椎名真白	17
1004	芙兰朵露	400
1005	雾雨魔理沙	
1006	坂上智代	19
1007	古明地觉	16

1001	85
1002	89
1003	95
1004	81
1005	100
1006	86
1002	89

girl_info 增加一个 id 为 1001 的数据，girl_score 增加一条 id 为 1002 的数据，显然这两条数据是重复的。

至于SQL 支持的连接查询，包括 内连接、外连接、交叉连接、自然连接 以及 自连接 等。其中，外连接又可以分为 左外连接、右外连接 以及 全外连接。

另外，连接查询中的 ON 子句与 WHERE 子句类似，可以支持各种条件运算符（=、>=、!=、BETWEEN 等）。但最常用的是等值连接（=），我们主要介绍这种条件的连接查询。

 

内连接：

内连接（Inner Join）返回两个表中满足连接条件的数据；使用关键字 INNER JOIN 表示，也可以简写成 JOIN。内连接的原理如下图所示（基于两个表的 id 进行等值连接）：

其中，id = 1 和 id = 3 是两个表中匹配的数据，因此内连接返回了这 2 行记录。

 

左外连接：

左外连接（Left Outer Join）首先返回左表中所有的数据；对于右表，返回满足连接条件的数据；如果没有相应的数据就返回空值。左外连接使用关键字 LEFT OUTER JOIN 表示，也可以简写成 LEFT JOIN。左外连接的原理如下图所示（基于两个表的 id 进行连接）：

其中，id = 2 的数据在 table1 中存在，在 table2 中不存在；左外连接仍然会返回左表中的该记录，而对于 table2 中的价格（price），返回的是空值。

 

左外连接：

右外连接（Right Outer Join）首先返回右表中所有的数据；对于左表，返回满足连接条件的数据，如果没有相应的数据就返回空值。右外连接使用关键字 RIGHT OUTER JOIN 表示，也可以简写成 RIGHT JOIN。右外连接的原理如下图所示（基于两个表的 id 进行连接）：

其中，id = 5 的数据在 table2 中存在，在 table1 中不存在；右外连接仍然会返回右表中的该记录，而对于 table1 中的名称（name），返回的是空值。简而言之：

table1 RIGHT JOIN table2 等价于 table2 LEFT JOIN table1

因此右外连接和左外连接可以相互转换，就我个人而言习惯左连接。如果需要右连接的逻辑，那么我会把两张表的顺序颠倒，而不会把左连接改成右连接，当然这只是我个人习惯。具体怎么做由你自己决定。

 

全外连接：

全外连接（Full Outer Join）等价于左外连接加上右外连接，同时返回左表和右表中所有的数据；对于两个表中不满足连接条件的数据返回空值。全外连接使用关键字 FULL OUTER JOIN 表示，也可以简写成 FULL JOIN 。全外连接的原理如下图所示（基于两个表的 id 进行连接）：

MySQL不支持全外连接。

 

交叉连接：

交叉连接也称为 笛卡尔积（Cartesian Product），使用关键字 CROSS JOIN 表示。两个表的交叉连接相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。如果第一个表有 1000 行，第二个表有 2000 行，它们的交叉连接将会产生 2000000 行数据。

交叉连接可能会导致查询结果的数量急剧增长，从而引起性能问题；通常应该使用连接条件进行过滤，避免产生交叉连接。

交叉连接一般使用较少。

除了上面介绍的几种连接类型，SQL 中还存在一些特殊形式的连接查询。

 

自然连接：

对于连接查询，如果满足以下条件，可以使用 USING 替代 ON 简化连接条件的输入：

• 连接条件是等值连接
• 两个表中的连接字段必须名称相同，类型也相同

比如我们之前的例子，根据两张表的id字段、并且判断是否相等，所以可以改写如下：

select a.name, b.score
from girl_info as a
         join girl_score as b
              using(id)
where a.id > 1003;

得到的结果也是和之前一样。

其中，USING 表示使用两个表中的公共字段（id）进行等值连接。查询语句中的公共字段不需要添加表名限定。该语句的结果与上文中的内连接查询示例相同。

SQL Server 不支持 USING 语法。

另外一张表也可以和其自身进行连接。

 

自然连接：

我们目前以 id 进行连接，但是我们看到了，我们将记录给改了，id有重复的。那么结果会怎么样呢？

select a.id, a.name, b.score
from girl_info as a
         join girl_score as b
              using (id);
/*
1001    古明地觉    85
1001    古明地觉    85
1002    古明地恋    89
1003    椎名真白    95
1004    芙兰朵露    81
1005    雾雨魔理沙  100
1006    坂上智代    86
1002    古明地恋    89
 */

我们看到 "古明地觉" 和 "古明地恋" 都出现了两次，因为 id=1001 的记录在 girl_info 中出现了两次，id=1002 的记录在 girl_score 中出现了两次，那么在进行 join 的时候，id=1001 和 id=1002 都会出现两次，因为能匹配上。同理，如果 girl_score 中出现了 3 个 id=1001 的记录，那么结果 id=1001 的记录总数就是2 * 3 = 6。因为也进行了笛卡尔积。

所以有时候在做 LEFT JOIN 之后，会发现结果数据的总数和左表不一致，明明是按照左表进行 JOIN 的呀，为啥结果和左表的总数对不上呢？出现这种情况就是 ON 后面的连接字段中出现了重复。假设按照 id 进行连接，左表有一个 id=3 的记录，这时候右表有三个 id=3 的记录，那么在连接的时候，左表的 id=3 会和右表的三个 id=3 的记录进行连接，因此会变成 3 条记录。因此如果 JOIN 之后发现结果不对，并且语法也没有写错，那么数量对不上的原因十有八九就是我们目前说的数据重复的问题。

小结

连接查询使得 SQL 能够方便地通过一个查询获取多个表中的关联数据。这节我们讨论了内连接、左/右/全外连接、交叉连接、自然连接以及自连接的概念和作用。推荐使用语义更加清晰、更加通用的 JOIN 和 ON 语法实现连接查询。