200.联结查询-JOIN

联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION 是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。

联结大体上分为内联结和外联结两种。首先请大家牢牢掌握这两种联结的使用方法。

‍ ‍

什么是联结

前一节我们学习了 UNION 和 INTERSECT 等集合运算，这些集合运算的特征就是以行为单位进行操作。通俗地说，就是进行这些集合运算时，会导致记录行数的增减。使用 UNION 会增加记录行数，而使用 INTERSECT 或者 EXCEPT 会减少记录行数 。

但是这些运算不会导致列数的改变。作为集合运算对象的表的前提就是列数要一致。因此，运算结果不会导致列的增减。 ‍ 而联结（JOIN ） 运算，简单来说，就是将其他表中的列添加过来，进行“添加列”的运算。该操作通常用于无法从一张表中获取期望数据（列）的情况。截至目前，本书中出现的示例基本上都是从一张表中选取数据，但实际上，期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表（3 张以上的表也没关系）中选取数据了。

​​

SQL 的联结根据其用途可以分为很多种类，这里希望大家掌握的有两种，内联结和外联结。接下来，我们就以这两种联结为中心进行学习。

INNER JOIN：内联结

内联结（INNER JOIN ） ，它是应用最广泛的联结运算。大家现在可以暂时忽略“内”这个字，之后会给大家详细说明。 ‍

准备

本例中我们会使用 Product 表和 ShopProduct 表，我们再来回顾一下这两张表的内容：

Product （商品）表

​product_id​ （商品编号）	​product_name​ （商品名称）	​product_type​ （商品种类）	​sale_price​ （销售单价）	​purchase_price​ （进货单价）	​regist_date​ （登记日期）
​0001​	T 恤衫	衣服	​1000​	​500​	​2009-09-20​
​0002​	打孔器	办公用品	​500​	​320​	​2009-09-11​
​0003​	运动 T 恤	衣服	​4000​	​2800​
​0004​	菜刀	厨房用具	​3000​	​2800​	​2009-09-20​
​0005​	高压锅	厨房用具	​6800​	​5000​	​2009-01-15​
​0006​	叉子	厨房用具	​500​		​2009-09-20​
​0007​	擦菜板	厨房用具	​880​	​790​	​2008-04-28​
​0008​	圆珠笔	办公用品	​100​		​2009-11-11​

‍ ShopProduct （商店商品）表

​shop_id​ （商店编号）	​shop_name​ （商店名称）	​product_id​ （商品编号）	​quantity​ （数量）
​000A​	东京	​0001​	​30​
​000A​	东京	​0002​	​50​
​000A​	东京	​0003​	​15​
​000B​	名古屋	​0002​	​30​
​000B​	名古屋	​0003​	​120​
​000B​	名古屋	​0004​	​20​
​000B​	名古屋	​0006​	​10​
​000B​	名古屋	​0007​	​40​
​000C​	大阪	​0003​	​20​
​000C​	大阪	​0004​	​50​
​000C​	大阪	​0006​	​90​
​000C​	大阪	​0007​	​70​
​000D​	福冈	​0001​	​100​

两张表及其包含的列：

	​Product​	​ShopProduct​
商品编号	○	○
商品名称	○
商品种类	○
销售单价	○
进货单价	○
登记日期	○
商店编号		○
商店名称		○
数量		○

‍

开始使用

如上表所示，两张表中的列可以分为如下两类。

• A：两张表中都包含的列 → 商品编号
• B：只存在于一张表内的列 → 商品编号之外的列

所谓联结运算，一言以蔽之，就是“以 A 中的列作为桥梁，将 B 中满足同样条件的列汇集到同一结果之中”。

从 ShopProduct 表中的数据我们能够知道，东京店（000A ）销售商品编号为 0001 、0002 和 0003 的商品，但这些商品的信息都保存在 Product 表中，ShopProduct 表没有存储。

如果我们将两个表结合起来查询，就能查询出每个店铺销售的商品信息了：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM ShopProduct AS SP INNER JOIN Product AS P 
    ON SP.product_id = P.product_id;


--结果：
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000A    | 东京      | 0001       | T恤衫        |       1000 |
| 000A    | 东京      | 0002       | 打孔器       |        500 |
| 000A    | 东京      | 0003       | 运动T恤      |       4000 |
| 000B    | 名古屋    | 0002       | 打孔器       |        500 |
| 000B    | 名古屋    | 0003       | 运动T恤      |       4000 |
| 000B    | 名古屋    | 0004       | 菜刀         |       3000 |
| 000B    | 名古屋    | 0006       | 叉子         |        500 |
| 000B    | 名古屋    | 0007       | 擦菜板       |        880 |
| 000C    | 大阪      | 0003       | 运动T恤      |       4000 |
| 000C    | 大阪      | 0004       | 菜刀         |       3000 |
| 000C    | 大阪      | 0006       | 叉子         |        500 |
| 000C    | 大阪      | 0007       | 擦菜板       |        880 |
| 000D    | 福冈      | 0001       | T恤衫        |       1000 |
+---------+-----------+------------+--------------+------------+
13 rows in set (0.00 sec)

内联结要点 ① FROM 子句

FROM 子句：之前的 FROM 子句中只有一张表，而这次我们同时使用了 ShopProduct 和 Product 两张表。

FROM ShopProduct AS SP INNER JOIN Product AS P

使用关键字 INNER JOIN 就可以将两张表联结在一起了。SP 和 P 分别是这两张表的别名，但别名并不是必需的。在 SELECT 子句中直接使用 ShopProduct 和 product_id 这样的表的原名也没有关系，但由于表名太长会影响 SQL 语句的可读性，因此还是希望大家能够习惯使用别名 。

内联结要点 ② ON 子句

第二点要注意的是 ON 后面的联结条件。

ON SP.product_id = P.product_id

我们可以在 ON 之后指定两张表联结所使用的列（联结键 ），本例中使用的是商品编号（product_id ）。也就是说，ON 是专门用来指定联结条件的，它能起到与 WHERE 相同的作用。需要指定多个键时，同样可以使用 AND 、OR 。

在进行内联结时 ON 子句是必不可少的（如果没有 ON 会发生错误），并且 ON 必须书写在 FROM 和 WHERE 之间。

内联结要点 ③ SELECT 子句

第三点要注意的是，在 SELECT 子句中指定的列。

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

在 SELECT 子句中，像 SP.shop_id 和 P.sale_price 这样使用“< 表的别名 >.< 列名 >”的形式来指定列。和使用一张表时不同，由于多表联结时，某个列到底属于哪张表比较容易混乱，因此采用了这样的防范措施。从语法上来说，只有那些同时存在于两张表中的列（这里是 product_id ）必须使用这样的书写方式，其他的列像 shop_id 这样直接书写列名也不会发生错误。

但是就像前面说的那样，为了避免混乱，还是希望大家能够在使用联结时按照“< 表的别名 >.< 列名 >”的格式来书写 SELECT 子句中全部的列。 ‍

内联结和 WHERE 子句结合使用

如果并不想了解所有商店的情况，例如只想知道东京店（000A ）的信息时，可以像之前学习的那样在 WHERE 子句中添加条件：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM ShopProduct AS SP INNER JOIN Product AS P 
    ON SP.product_id = P.product_id
 WHERE SP.shop_id = '000A';

像这样使用联结运算将满足相同规则的表联结起来时，WHERE 、GROUP BY 、HAVING 、ORDER BY 等工具都可以正常使用。我们可以将联结之后的结果想象为新创建出来的一张表。当然，这张“表”只在 SELECT 语句执行期间存在，SELECT 语句执行之后就会消失。 ‍

OUTER JOIN：外联结

外联结也是通过 ON 子句的联结键将两张表进行联结，并从两张表中同时选取相应的列的。基本的使用方法并没有什么不同，只是结果却有所不同。

直接来看看结果吧：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P 
    ON SP.product_id = P.product_id;

-- 结果：
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000D    | 福冈      | 0001       | T恤衫        |       1000 |
| 000A    | 东京      | 0001       | T恤衫        |       1000 |
| 000B    | 名古屋    | 0002       | 打孔器       |        500 |
| 000A    | 东京      | 0002       | 打孔器       |        500 |
| 000C    | 大阪      | 0003       | 运动T恤      |       4000 |
| 000B    | 名古屋    | 0003       | 运动T恤      |       4000 |
| 000A    | 东京      | 0003       | 运动T恤      |       4000 |
| 000C    | 大阪      | 0004       | 菜刀         |       3000 |
| 000B    | 名古屋    | 0004       | 菜刀         |       3000 |
| 000C    | 大阪      | 0006       | 叉子         |        500 |
| 000B    | 名古屋    | 0006       | 叉子         |        500 |
| 000C    | 大阪      | 0007       | 擦菜板       |        880 |
| 000B    | 名古屋    | 0007       | 擦菜板       |        880 |
| NULL    | NULL      | NULL       | 高压锅       |       6800 |
| NULL    | NULL      | NULL       | 圆珠笔       |        100 |
+---------+-----------+------------+--------------+------------+
15 rows in set (0.00 sec)

‍

外联结要点 ① 选取出单张表中全部的信息

与内联结的结果相比，不同点显而易见，那就是结果的行数不一样。内联结的结果中有 13 条记录，而外联结的结果中有 15 条记录，增加的 2 条记录到底是什么呢？

这正是外联结的关键点。多出的 2 条记录是高压锅和圆珠笔，这 2 条记录在 ShopProduct 表中并不存在，也就是说，这 2 种商品在任何商店中都没有销售。由于内联结只能选取出同时存在于两张表中的数据，因此只在 Product 表中存在的 2 种商品并没有出现在结果之中。

相反，对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。在实际的业务中，例如想要生成固定行数的单据时，就需要使用外联结。如果使用内联结的话，根据 SELECT 语句执行时商店库存状况的不同，结果的行数也会发生改变，生成的单据的版式也会受到影响，而使用外联结能够得到固定行数的结果。

虽说如此，那些表中不存在的信息我们还是无法得到，结果中高压锅和圆珠笔的商店编号和商店名称都是 NULL （具体信息大家都不知道，真是无可奈何）。外联结名称的由来也跟 NULL 有关，即“结果中包含原表中不存在（在原表之外）的信息”。相反，只包含表内信息的联结也就被称为内联结了。 ‍

外联结要点 ② 每张表都是主表吗？

外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT 。顾名思义，使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT 时右侧的表是主表。

大家可能会犹豫到底应该使用 LEFT 还是 RIGHT ，其实它们的功能没有任何区别，使用哪一个都可以。 ‍

using

sql/92 标准可以使用 using 关键字来简化连接查询，但是只是在查询满足下面两个条件时，才能使用 using 关键字进行简化。

1. 查询必须是等值连接（用=号）。
2. 等值连接中的列必须具有相同的名称和数据类型。

例如：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM ShopProduct AS SP INNER JOIN Product AS P
  USING (product_id)
  WHERE SP.shop_id = '000A';

‍

3 张表及以上的联结

通常联结只涉及 2 张表，但有时也会出现必须同时联结 3 张以上的表的情况。原则上联结表的数量并没有限制，语法上也没什么不同，只需添加 join 和 on 即可，例如：

select *
from a 
inner join b on a.id = b.id
inner join c on a.id = c.id

CROSS JOIN：交叉联结

交叉联结（CROSS JOIN），在实际业务中并不会使用，为什么还要在这里进行介绍呢？这是因为交叉联结是所有联结运算的基础。

交叉联结本身非常简单，但是其结果有点麻烦。下面我们就试着将 Product 表和 ShopProduct 表进行交叉联结：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P; 


-- 结果：
+---------+-----------+------------+--------------+
| shop_id | shop_name | product_id | product_name |
+---------+-----------+------------+--------------+
| 000A    | 东京      | 0001       | 圆珠笔       |
| 000A    | 东京      | 0001       | 擦菜板       |
| 000A    | 东京      | 0001       | 叉子         |
| 000A    | 东京      | 0001       | 高压锅       |
| 000A    | 东京      | 0001       | 菜刀         |
| 000A    | 东京      | 0001       | 运动T恤      |
| 000A    | 东京      | 0001       | 打孔器       |
| 000A    | 东京      | 0001       | T恤衫        |
| 000A    | 东京      | 0002       | 圆珠笔       |
| 000A    | 东京      | 0002       | 擦菜板       |
| 000A    | 东京      | 0002       | 叉子         |
| 000A    | 东京      | 0002       | 高压锅       |
| 000A    | 东京      | 0002       | 菜刀         |
| 000A    | 东京      | 0002       | 运动T恤      |
| 000A    | 东京      | 0002       | 打孔器       |
| 000A    | 东京      | 0002       | T恤衫        |
| 000A    | 东京      | 0003       | 圆珠笔       |
| 000A    | 东京      | 0003       | 擦菜板       |
| 000A    | 东京      | 0003       | 叉子         |
| 000A    | 东京      | 0003       | 高压锅       |
| 000A    | 东京      | 0003       | 菜刀         |
| 000A    | 东京      | 0003       | 运动T恤      |
| 000A    | 东京      | 0003       | 打孔器       |
| 000A    | 东京      | 0003       | T恤衫        |
| 000B    | 名古屋    | 0002       | 圆珠笔       |
| 000B    | 名古屋    | 0002       | 擦菜板       |
| 000B    | 名古屋    | 0002       | 叉子         |
| 000B    | 名古屋    | 0002       | 高压锅       |
| 000B    | 名古屋    | 0002       | 菜刀         |
| 000B    | 名古屋    | 0002       | 运动T恤      |
| 000B    | 名古屋    | 0002       | 打孔器       |
| 000B    | 名古屋    | 0002       | T恤衫        |
| 000B    | 名古屋    | 0003       | 圆珠笔       |
| 000B    | 名古屋    | 0003       | 擦菜板       |
| 000B    | 名古屋    | 0003       | 叉子         |
| 000B    | 名古屋    | 0003       | 高压锅       |
| 000B    | 名古屋    | 0003       | 菜刀         |
| 000B    | 名古屋    | 0003       | 运动T恤      |
| 000B    | 名古屋    | 0003       | 打孔器       |
| 000B    | 名古屋    | 0003       | T恤衫        |
| 000B    | 名古屋    | 0004       | 圆珠笔       |
| 000B    | 名古屋    | 0004       | 擦菜板       |
| 000B    | 名古屋    | 0004       | 叉子         |
| 000B    | 名古屋    | 0004       | 高压锅       |
| 000B    | 名古屋    | 0004       | 菜刀         |
| 000B    | 名古屋    | 0004       | 运动T恤      |
| 000B    | 名古屋    | 0004       | 打孔器       |
| 000B    | 名古屋    | 0004       | T恤衫        |
| 000B    | 名古屋    | 0006       | 圆珠笔       |
| 000B    | 名古屋    | 0006       | 擦菜板       |
| 000B    | 名古屋    | 0006       | 叉子         |
| 000B    | 名古屋    | 0006       | 高压锅       |
| 000B    | 名古屋    | 0006       | 菜刀         |
| 000B    | 名古屋    | 0006       | 运动T恤      |
| 000B    | 名古屋    | 0006       | 打孔器       |
| 000B    | 名古屋    | 0006       | T恤衫        |
| 000B    | 名古屋    | 0007       | 圆珠笔       |
| 000B    | 名古屋    | 0007       | 擦菜板       |
| 000B    | 名古屋    | 0007       | 叉子         |
| 000B    | 名古屋    | 0007       | 高压锅       |
| 000B    | 名古屋    | 0007       | 菜刀         |
| 000B    | 名古屋    | 0007       | 运动T恤      |
| 000B    | 名古屋    | 0007       | 打孔器       |
| 000B    | 名古屋    | 0007       | T恤衫        |
| 000C    | 大阪      | 0003       | 圆珠笔       |
| 000C    | 大阪      | 0003       | 擦菜板       |
| 000C    | 大阪      | 0003       | 叉子         |
| 000C    | 大阪      | 0003       | 高压锅       |
| 000C    | 大阪      | 0003       | 菜刀         |
| 000C    | 大阪      | 0003       | 运动T恤      |
| 000C    | 大阪      | 0003       | 打孔器       |
| 000C    | 大阪      | 0003       | T恤衫        |
| 000C    | 大阪      | 0004       | 圆珠笔       |
| 000C    | 大阪      | 0004       | 擦菜板       |
| 000C    | 大阪      | 0004       | 叉子         |
| 000C    | 大阪      | 0004       | 高压锅       |
| 000C    | 大阪      | 0004       | 菜刀         |
| 000C    | 大阪      | 0004       | 运动T恤      |
| 000C    | 大阪      | 0004       | 打孔器       |
| 000C    | 大阪      | 0004       | T恤衫        |
| 000C    | 大阪      | 0006       | 圆珠笔       |
| 000C    | 大阪      | 0006       | 擦菜板       |
| 000C    | 大阪      | 0006       | 叉子         |
| 000C    | 大阪      | 0006       | 高压锅       |
| 000C    | 大阪      | 0006       | 菜刀         |
| 000C    | 大阪      | 0006       | 运动T恤      |
| 000C    | 大阪      | 0006       | 打孔器       |
| 000C    | 大阪      | 0006       | T恤衫        |
| 000C    | 大阪      | 0007       | 圆珠笔       |
| 000C    | 大阪      | 0007       | 擦菜板       |
| 000C    | 大阪      | 0007       | 叉子         |
| 000C    | 大阪      | 0007       | 高压锅       |
| 000C    | 大阪      | 0007       | 菜刀         |
| 000C    | 大阪      | 0007       | 运动T恤      |
| 000C    | 大阪      | 0007       | 打孔器       |
| 000C    | 大阪      | 0007       | T恤衫        |
| 000D    | 福冈      | 0001       | 圆珠笔       |
| 000D    | 福冈      | 0001       | 擦菜板       |
| 000D    | 福冈      | 0001       | 叉子         |
| 000D    | 福冈      | 0001       | 高压锅       |
| 000D    | 福冈      | 0001       | 菜刀         |
| 000D    | 福冈      | 0001       | 运动T恤      |
| 000D    | 福冈      | 0001       | 打孔器       |
| 000D    | 福冈      | 0001       | T恤衫        |
+---------+-----------+------------+--------------+
104 rows in set (0.00 sec)

‍ 为什么有 100 多行结果呢？这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。

本例中，因为 ShopProduct 表存在 13 条记录，Product 表存在 8 条记录，所以结果中就包含了 13 × 8 = 104 条记录（其实就是笛卡尔积），这也就是为什么该联结无需使用 on 进行条件指定键

内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。

可以理解为，在内/外联结的时候，先通过笛卡尔积形成一个结果集，然后再根据 on 条件进行筛选。 ‍ 交叉联结没有应用到实际业务之中的原因有两个：一是其结果没有实用价值，二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。 ‍ （完）