联结-join

# 200.联结-join

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

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

‍

# 什么是联结

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

但是这些运算不会导致列数的改变。作为集合运算对象的表的前提就是列数要一致。因此，运算结果不会导致列的增减。

‍

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

00124

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

‍

# 内联结要点① 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';

1
2
3
4

像这样使用联结运算将满足相同规则的表联结起来时，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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

‍

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

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

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

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

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

‍

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

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

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

‍

# using

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

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

例如：

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';

1
2
3
4

‍

# 3张表及以上的联结

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


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

1
2
3
4
5

‍

# 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

‍

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

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

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

可以理解为，在内/外联结的时候，先通过笛卡尔积形成一个结果集，然后再根据on条件进行筛选。

‍

交叉联结没有应用到实际业务之中的原因有两个。一是其结果没有实用价值，二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。

‍

上次更新: 2024/5/30 15:25:05

← 集合运算 SQL入门小结→