SQL零基础投喂（集合运算)

Task04： 集合运算
4.1 表的加减法
4.1.1 什么是集合运算
集合 在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,
表、视图和查询的执⾏结果都是记录的集合, 其中的元素为表或者查询结果中的每⼀⾏.
在标准 SQL 中, 分别对检索结果使⽤ UNION , INTERSECT, EXCEPT 来将检索结果进⾏
并,交和差运算, 像 UNION , INTERSECT , EXCEPT 这种⽤来进⾏集合运算的运算符称为集合
运算符.
4.1.2 表的加法–UNION
4.1.2.1 UNION
建表代码及数据导⼊请使⽤第⼀章提供的代码.
接下来我们演示 UNION 的具体⽤法及查询结果:
select product_id,product_name
from product_name union
select product_id,product_name
from product2

CREATE TABLE product2 (
product_id char(4) NOT NULL,
product_name varchar(100) NOT NULL,
product_type varchar(32) NOT NULL,
sale_price int DEFAULT NULL,
purchase_price int DEFAULT NULL,
regist_date date DEFAULT NULL,
PRIMARY KEY (product_id)
);

insert into product2
(product_id,product_name,product_type,sale_price,purchase_price,regist_date)
values
(‘0001’,‘T恤’,‘衣服’,1000,500,‘2009-09-20’),
(‘0002’,‘打孔器’,‘办公用品’,500,320,‘2009-09-11’),
(‘0003’,‘运动T恤’,‘衣服’,4000,2800,NULL),
(‘0009’,‘手套’,‘衣服’,800,500,NULL),
(‘0010’,‘水壶’,‘厨房用具’,2000,1700,‘2009-09-20’);
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 |
0009 | 手套 | 衣服 | 800 | 500 |
0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20
(5 行记录)
select product_id,product_name
from product
union
select product_id,product_name
from product2;

product_id | product_name
------------±-------------
0006 | 叉子
0001 | T恤
0002 | 打孔器
0007 | 擦菜板
0003 | 运动T恤
0004 | 菜刀
0005 | 高压锅
0008 | 圆珠笔
0010 | 水壶
0009 | 手套
0001 | T恤衫
(11 行记录)
可以发现,商品编号为“ 0001 ”~“ 0003 ”的 3 条记录在两个表中都存在,因此⼤家可
能会认为结果中会出现重复的记录,但是 UNION 等集合运算符通常都会除去重复的记录.
上述查询是对不同的两张表进⾏求并集运算. 对于同⼀张表, 实际上也是可以进⾏求并集的.

练习题: 假设连锁店想要增加⽑利率超过 50%或者售价低于 800 的货物的存货量, 请使⽤
UNION 对分别满⾜上述两个条件的商品的查询结果求并集.
结果应该类似于:
select * from product
where sale_price<800 or
(sale_price-purchase_price)/purchase_price>0.5
union
select * from product2
where sale_price<800 or
(sale_price-purchase_price)/purchase_price>0.5

product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------±-------------±-------------±-----------±---------------±------------
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
(5 行记录)

4.1.2.2 UNION 与 OR 谓词
对于上边的练习题, 如果你已经正确地写出来查询, 你会发现, 使⽤ UNION 对两个查询结果取
并集, 和在⼀个查询中使⽤ WHERE ⼦句, 然后使⽤ OR 谓词连接两个查询条件, 能够得到相同
的结果.
那么是不是就没必要引⼊ UNION 了呢? 当然不是这样的. 确实, 对于同⼀个表的两个不同的筛
选结果集, 使⽤ UNION 对两个结果集取并集, 和把两个⼦查询的筛选条件⽤ OR 谓词连接, 会
得到相同的结果, 但倘若要将两个不同的表中的结果合并在⼀起, 就不得不使⽤ UNION 了.
⽽且, 即便是对于同⼀张表, 有时也会出于查询效率⽅⾯的因素来使⽤ UNION.

4.1.2.3 包含重复⾏的集合运算 UNION ALL
在 1.1.1 中我们发现, SQL 语句的 UNION 会对两个查询的结果集进⾏合并和去重, 这种去重不
仅会去掉两个结果集相互重复的, 还会去掉⼀个结果集中的重复⾏. 但在实践中有时候需要需
要不去重的并集, 在 UNION 的结果中保留重复⾏的语法其实⾮常简单,只需要在 UNION 后⾯
添加 ALL 关键字就可以了.
例如, 想要知道 Product 和 Product2 中所包含的商品种类及每种商品的数量, 第⼀步,就需
要将两个表的商品种类字段选出来, 然后使⽤ UNION ALL 进⾏不去重地合并. 接下来再对两个
表的结果按 Product_type 字段分组计数.

4.1.2.4 [扩展阅读]bag 模型与 set 模型
在⾼中数学课上我们就学过, 集合的⼀个显著的特征就是集合中的元素都是互异的. 当我们把
数据库中的表看作是集合的时候, 实际上存在⼀些问题的: 不论是有意的设计或⽆意的过失, 很
多数据库中的表包含了重复的⾏.
Bag 是和 set 类似的⼀种数学结构, 不⼀样的地⽅在于: bag ⾥⾯允许存在重复元素, 如果同
⼀个元素被加⼊多次, 则袋⼦⾥就有多个该元素.
通过上述 bag 与 set 定义之间的差别我们就发现, 使⽤ bag 模型来描述数据库中的表在很多
时候更加合适.
是否允许元素重复导致了 set 和 bag 的并交差等运算都存在⼀些区别. 以 bag 的交为例, 由
于 bag 允许元素重复出现, 对于两个 bag, 他们的并运算会按照: 1.该元素是否⾄少在⼀个 bag
⾥出现过, 2.该元素在两个 bag 中的最⼤出现次数 这两个⽅⾯来进⾏计算. 因此对于 A =
{1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}.

4.1.2.5 隐式类型转换
通常来说, 我们会把类型完全⼀致, 并且代表相同属性的列使⽤ UNION 合并到⼀起显示, 但有
时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在⼀列⾥显
示, 例如字符串和数值类型:
select product_id,product_name,‘1’
from product
union
select product_id,product_name,sale_price
from product2;
product_id | product_name | ?column?
------------±-------------±---------
0009 | 手套 | 800
0002 | 打孔器 | 500
0008 | 圆珠笔 | 1
0001 | T恤衫 | 1
0003 | 运动T恤 | 4000
0007 | 擦菜板 | 1
0002 | 打孔器 | 1
0005 | 高压锅 | 1
0004 | 菜刀 | 1
0001 | T恤 | 1000
0003 | 运动T恤 | 1
0006 | 叉子 | 1
0010 | 水壶 | 2000
(13 行记录)

练习题:
使⽤ SYSDATE()函数可以返回当前⽇期时间, 是⼀个⽇期时间类型的数据, 试测试该数据类型
和数值,字符串等类型的兼容性.
例如, 以下代码可以正确执⾏, 说明时间⽇期类型和字符串,数值以及缺失值均能兼容.
select current_date,current_date,current_date
union
select ‘char’,123,null;
错误: 无效的类型 date 输入语法: “char”
第3行select ‘char’,123,null;–postgreSQL 无法兼容

4.1.3 MySQL 8.0 不⽀持交运算 INTERSECT
集合的交, 就是两个集合的公共部分, 由于集合元素的互异性, 集合的交只需通过⽂⽒图就可以
很直观地看到它的意义.
虽然集合的交运算在 SQL 标准中已经出现多年了, 然⽽很遗憾的是, 截⽌到 MySQL 8.0 版本,
MySQL 仍然不⽀持 INTERSECT 操作.
select product_id,product_name
from product
intersect --相交; 交叉; 横穿; 贯穿; 横断
select product_id,product_name
from Product2;

product_id | product_name
------------±-------------
0002 | 打孔器
0003 | 运动T恤
(2 行记录)

4.1.3.1 [扩展阅读]bag 的交运算
对于两个 bag, 他们的交运算会按照: 1.该元素是否同时属于两个 bag, 2.该元素在两个 bag 中
的最⼩出现次数这两个⽅⾯来进⾏计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两
个 bag, 它们的交运算结果就等于 {1,1,2}.

4.1.4 差集,补集与表的减法
求集合差集的减法运算和实数的减法运算有些不同, 当使⽤⼀个集合 A 减去另⼀个集合 B 的
时候,对于只存在于集合 B ⽽不存在于集合 A 的元素, 采取直接忽略的策略,因此集合 A 和 B
做减法只是将集合 A 中也同时属于集合 B 的元素减掉。
select * from product
EXCEPT
select * from product2;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------±-------------±-------------±-----------±---------------±------------
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
(6 行记录)

– 使用 IN 子句的实现方法
SELECT *
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM Product2);
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 |
0009 | 手套 | 衣服 | 800 | 500 |
0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20
(5 行记录)

4.1.4.2 EXCEPT 与 NOT 谓词
通过上述练习题的 MySQL 解法, 我们发现, 使⽤ NOT IN 谓词, 基本上可以实现和 SQL 标准
语法中的 EXCEPT 运算相同的效果.
练习题:
使⽤ NOT 谓词进⾏集合的减法运算, 求出 Product 表中, 售价⾼于 2000,但利润低于 30%的
商品:
select * from product
where sale_price>2000
EXCEPT
select * from product2
where sale_price<1.3*purchase_price;

SELECT *
FROM Product
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id
FROM Product
WHERE sale_price<1.3*purchase_price);
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------±-------------±-------------±-----------±---------------±------------
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
(2 行记录)

4.1.4.3 EXCEPT ALL 与 bag 的差
类似于 UNION ALL, EXCEPT ALL 也是按出现次数进⾏减法, 也是使⽤ bag 模型进⾏运算.
对于两个 bag, 他们的差运算会按照:
1.该元素是否属于作为被减数的 bag,
2.该元素在两个 bag 中的出现次数
这两个⽅⾯来进⾏计算. 只有属于被减数的 bag 的元素才参与 EXCEP ALL 运算, 并且差 bag
中的次数,等于该元素在两个 bag 的出现次数之差(差为零或负数则不出现). 因此对于 A =
{1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}.

4.1.4.4 INTERSECT 与 AND 谓词
对于同⼀个表的两个查询结果⽽⾔, 他们的交 INTERSECT 实际上可以等价地将两个查询的检
索条件⽤ AND 谓词连接来实现.
练习题:
使⽤ AND 谓词查找 product 表中利润率⾼于 50%,并且售价低于 1500 的商品,查询结果如
下所示.
SELECT *
FROM Product
WHERE sale_price > 1.5 * purchase_price
AND sale_price < 1500;
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
(2 行记录)

4.1.5 对称差
两个集合 A,B 的对称差是指那些仅属于 A 或仅属于 B 的元素构成的集合. 对称差也是个⾮常
基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.上述⽅法
在其他数据库⾥也可以⽤来简单地实现表或查询结果的对称差运算: ⾸先使⽤ UNION 求两个
表的并集, 然后使⽤ INTERSECT 求两个表的交集, 然后⽤并集减去交集, 就得到了对称差.
但由于在 MySQL 8.0 ⾥, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使⽤上述
思路来求对称差. 好在还有差集运算可以使⽤. 从直观上就能看出来, 两个集合的对称差等于
A-B 并上 B-A, 因此实践中可以⽤这个思路来求对称差.
练习题:
使⽤ Product 表和 Product2 表的对称差来查询哪些商品只在其中⼀张表, 结果类似于:
– 使用 NOT IN 实现两个表的差集
select * from product
where product_id not in (select product_id from product2)
union
select * from product2
where product_id not in (select product_id from product);
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------±-------------±-------------±-----------±---------------±------------
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0009 | 手套 | 衣服 | 800 | 500 |
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
(7 行记录)

4.1.5.1 借助并集和差集迂回实现交集运算 INTERSECT
通过观察集合运算的⽂⽒图, 我们发现, 两个集合的交可以看作是两个集合的并去掉两个集合
的对称差.

4.2 连结(JOIN)
前⼀节我们学习了 UNION 和 INTERSECT 等集合运算, 这些集合运算的特征就是以⾏⽅向为
单位进⾏操作. 通俗地说, 就是进⾏这些集合运算时, 会导致记录⾏数的增减. 使⽤ UNION 会增
加记录⾏数,⽽使⽤ INTERSECT 或者 EXCEPT 会减少记录⾏数.
但这些运算不能改变列的变化, 虽然使⽤函数或者 CASE 表达式等列运算, 可以增加列的数量,
但仍然只能从⼀张表中提供的基础信息列中获得⼀些"引申列", 本质上并不能提供更多的信息.
如果想要从多个表获取信息, 例如, 如果我们想要找出某个商店⾥的⾐服类商品的名称,数量及
价格等信息, 则必须分别从 ShopProduct 表和 Product 表获取信息.
连结( JOIN)就是使⽤某种关联条件(⼀般是使⽤相等判断谓词"="), 将其他表中的列添加过来,
进⾏“添加列”的集合运算. 可以说, 连结是 SQL 查询的核⼼操作, 掌握了连结, 能够从两张甚⾄
多张表中获取列, 能够将过去使⽤关联⼦查询等过于复杂的查询简化为更加易读的形式, 以及
进⾏⼀些更加复杂的查询.

4.2.1 内连结(INNER JOIN)
内连结的语法格式是:
– 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
4.2.1.1 使⽤内连结从两个表获取信息
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id;
shop_id | shop_name | product_id | product_name | product_type | sale_price | quantity
---------±----------±-----------±-------------±-------------±-----------±---------
000A | 东京 | 0001 | T恤衫 | 衣服 | 1000 | 30
000A | 东京 | 0002 | 打孔器 | 办公用品 | 500 | 50
000A | 东京 | 0003 | 运动T恤 | 衣服 | 4000 | 15
000B | 名古屋 | 0002 | 打孔器 | 办公用品 | 500 | 30
000B | 名古屋 | 0003 | 运动T恤 | 衣服 | 4000 | 120
000B | 名古屋 | 0004 | 菜刀 | 厨房用具 | 3000 | 20
000B | 名古屋 | 0006 | 叉子 | 厨房用具 | 500 | 10
000B | 名古屋 | 0007 | 擦菜板 | 厨房用具 | 880 | 40
000C | 大阪 | 0003 | 运动T恤 | 衣服 | 4000 | 20
000C | 大阪 | 0004 | 菜刀 | 厨房用具 | 3000 | 50
000C | 大阪 | 0006 | 叉子 | 厨房用具 | 500 | 90
000C | 大阪 | 0007 | 擦菜板 | 厨房用具 | 880 | 70
000D | 福冈 | 0001 | T恤衫 | 衣服 | 1000 | 100
(13 行记录)

要点⼀: 进⾏连结时需要在 FROM ⼦句中使⽤多张表.
之前的 FROM ⼦句中只有⼀张表, ⽽这次我们同时使⽤了 ShopProduct 和 Product 两张表,
使⽤关键字 INNER JOIN 就可以将两张表连结在⼀起了:
FROM ShopProduct AS SP INNER JOIN Product AS P

要点⼆:必须使⽤ ON ⼦句来指定连结条件.
在进⾏内连结时 ON ⼦句是必不可少的(⼤家可以试试去掉上述查询的 ON ⼦句后会有什么结
果).
ON ⼦句是专⻔⽤来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使⽤的列
以及⽐较条件,

要点三: SELECT ⼦句中的列最好按照 表名.列名 的格式来使⽤.
当两张表的列除了⽤于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得
我们能够在今后的任何时间阅读查询代码的时候, 都能⻢上看出每⼀列来⾃于哪张表, 能够节
省我们很多时间.
但是, 如果两张表有其他名称相同的列, 则必须使⽤上述格式来选择列名, 否则查询语句会报错.
我们回到上述查询所回答的问题. 通过观察上述查询的结果, 我们发现, 这个结果离我们的⽬标:
找出东京商店的⾐服类商品的基础信息已经很接近了. 接下来,我们只需要把这个查询结果作为
⼀张表, 给它增加⼀个 WHERE ⼦句来指定筛选条件.

4.2.1.2 结合 WHERE ⼦句使⽤内连结
如果需要在使⽤内连结的时候同时使⽤ WHERE ⼦句对检索结果进⾏筛选, 则需要把 WHERE
⼦句写在 ON ⼦句的后边.
例如, 对于上述查询问题, 我们可以在前⼀步查询的基础上, 增加 WHERE 条件.
增加 WHERE ⼦句的⽅式有好⼏种,我们先从最简单的说起.
第⼀种增加 WEHRE ⼦句的⽅式, 就是把上述查询作为⼦查询, ⽤括号封装起来, 然后在外层查
询增加筛选条件.

SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
WHERE SP.shop_name = ‘东京’
AND P.product_type = ‘衣服’ ;

我们⾸先给出上述查询的执⾏顺序:
FROM ⼦句->WHERE ⼦句->SELECT ⼦句
也就是说, 两张表是先按照连结列进⾏了连结, 得到了⼀张新表, 然后 WHERE ⼦句对这张新表
的⾏按照两个条件进⾏了筛选, 最后, SELECT ⼦句选出了那些我们需要的列.

4.2.1.3 结合 GROUP BY ⼦句使⽤内连结
结合 GROUP BY ⼦句使⽤内连结, 需要根据分组列位于哪个表区别对待.
最简单的情形, 是在内连结之前就使⽤ GROUP BY ⼦句.
但是如果分组列和被聚合的列不在同⼀张表, 且⼆者都未被⽤于连结两张表, 则只能先连结, 再
聚合.

练习题:
每个商店中, 售价最⾼的商品的售价分别是多少?
select ps.shop_id,ps.shop_name,max(p.sale_price)
from product p inner JOIN shopproduct ps
on p.product_id=ps.product_id
GROUP by ps.shop_id,ps.shop_name;

shop_id | shop_name | max
---------±----------±-----
000A | 东京 | 4000
000D | 福冈 | 1000
000C | 大阪 | 4000
000B | 名古屋 | 4000
(4 行记录)

4.2.1.4 ⾃连结(SELF JOIN)
之前的内连结, 连结的都是不⼀样的两个表. 但实际上⼀张表也可以与⾃身作连结, 这种连接称
之为⾃连结. 需要注意, ⾃连结并不是区分于内连结和外连结的第三种连结, ⾃连结可以是外连
结也可以是内连结, 它是不同于内连结外连结的另⼀个连结的分类⽅法.

4.2.1.6 ⾃然连结(NATURAL JOIN)
⾃然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的⼀种特例–当两个表
进⾏⾃然连结时, 会按照两个表中都包含的列名来进⾏等值内连结, 此时⽆需使⽤ ON 来指定
连接条件.
SELECT * FROM shopproduct NATURAL JOIN Product
product_id | shop_id | shop_name | quantity | product_name | product_type | sale_price | purchase_price | regist_date
------------±--------±----------±---------±-------------±-------------±-----------±---------------±------------
0001 | 000A | 东京 | 30 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20
0002 | 000A | 东京 | 50 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 000A | 东京 | 15 | 运动T恤 | 衣服 | 4000 | 2800 |
0002 | 000B | 名古屋 | 30 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 000B | 名古屋 | 120 | 运动T恤 | 衣服 | 4000 | 2800 |
0004 | 000B | 名古屋 | 20 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0006 | 000B | 名古屋 | 10 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 000B | 名古屋 | 40 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0003 | 000C | 大阪 | 20 | 运动T恤 | 衣服 | 4000 | 2800 |
0004 | 000C | 大阪 | 50 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0006 | 000C | 大阪 | 90 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 000C | 大阪 | 70 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0001 | 000D | 福冈 | 100 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20
(13 行记录)
select * from product natural join product2;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------±-------------±-------------±-----------±---------------±------------
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
(1 行记录)

4.2.2 外连结(OUTER JOIN)
内连结会丢弃两张表中不满⾜ ON 条件的⾏, 和内连结相对的就是外连结. 外连结会根据外连
结的种类有选择地保留⽆法匹配到的⾏.
按照保留的⾏位于哪张表, 外连结有三种形式: 左连结, 右连结和全外连结.
左连结会保存左表中⽆法按照 ON ⼦句匹配到的⾏, 此时对应右表的⾏均为缺失值; 右连结则
会保存右表中⽆法按照 ON ⼦句匹配到的⾏, 此时对应左表的⾏均为缺失值; ⽽全外连结则会
同时保存两个表中⽆法按照 ON ⼦句匹配到的⾏, 相应的另⼀张表中的⾏⽤缺失值填充.
– 左连结
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
– 右连结
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
– 全外连结
FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

4.2.2.1 左连结与右链接
由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别. 接下来我们先
以左连结为例进⾏学习. 所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都
能得到相同的结果. 稍后再介绍全外连结的概念.
4.2.2.2 使⽤左连结从两个表获取信息
如果你仔细观察过将 ShopProduct 和 Product 进⾏内连结前后的结果的话, 你就会发现,
Product 表中有两种商品并未在内连结的结果⾥, 就是说, 这两种商品并未在任何商店有售(这
通常意味着⽐较重要的业务信息, 例如, 这两种商品在所有商店都处于缺货状态, 需要及时补
货). 现在, 让我们先把之前内连结的 SELECT 语句转换为左连结试试看吧.
练习题: 统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品.
使⽤左连结的代码如下(注意区别于书上的右连结):

SELECT SP.shop_id
,SP.shop_name
,p.product_id
,P.product_name
,P.sale_price
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
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
| | 0008 | 圆珠笔 | 100
| | 0005 | 高压锅 | 6800
(15 行记录)
我们观察上述结果可以发现, 有两种商品: ⾼压锅和圆珠笔, 在所有商店都没有销售. 由于我们
在 SELECT ⼦句选择列的显示顺序以及未对结果进⾏排序的原因, 这个事实需要你仔细地进
⾏观察.

外连结要点 1: 选取出单张表中全部的信息
与内连结的结果相⽐,不同点显⽽易⻅,那就是结果的⾏数不⼀样.内连结的结果中有 13 条记录,
⽽外连结的结果中有 15 条记录,增加的 2 条记录到底是什么呢?这正是外连结的关键点. 多出
的 2 条记录是⾼压锅和圆珠笔,这 2 条记录在 ShopProduct 表中并不存在,也就是说,这 2 种
商品在任何商店中都没有销售.由于内连结只能选取出同时存在于两张表中的数据,因此只在
Product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某
⼀张表当中,就能够读取出来.在实际的业务中,例如想要⽣成固定⾏数的单据时,就需要使⽤外连
结.如果使⽤内连结的话,根据 SELECT 语句执⾏时商店库存状况的不同,结果的⾏数也会发⽣
改变,⽣成的单据的版式也会受到影响,⽽使⽤外连结能够得到固定⾏数的结果.虽说如此,那些表
中不存在的信息我们还是⽆法得到,结果中⾼压锅和圆珠笔的商店编号和商店名称都是 NULL
（具体信息⼤家都不知道,真是⽆可奈何）.外连结名称的由来也跟 NULL 有关,即“结果中包含
原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了.

外连结要点 2:使⽤ LEFT、RIGHT 来指定主表.
外连结还有⼀点⾮常重要,那就是要把哪张表作为主表.最终的结果中会包含主表内所有的数据.
指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使⽤ LEFT 时 FROM ⼦句中写在左侧的表是
主表,使⽤ RIGHT 时右侧的表是主表.代码清单 7-11 中使⽤了 RIGHT ,因此,右侧的表,也就是
Product 表是主表.我们还可以像代码清单 7-12 这样进⾏改写,意思完全相同.这样你可能会困
惑，到底应该使⽤ LEFT 还是 RIGHT？其实它们的功能没有任何区别,使⽤哪⼀个都可以.通常
使⽤ LEFT 的情况会多⼀些,但也并没有⾮使⽤这个不可的理由,使⽤ RIGHT 也没有问题.
通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两
种⽅式会到完全相同的结果.

4.2.2.3 结合 WHERE ⼦句使⽤左连结
上⼀⼩节我们学到了外连结的基础⽤法, 并且在上⼀节也学习了结合WHERE⼦句使⽤内连结的
⽅法, 但在结合WHERE⼦句使⽤外连结时, 由于外连结的结果很可能与内连结的结果不⼀样,
会包含那些主表中⽆法匹配到的⾏, 并⽤缺失值填写另⼀表中的列, 由于这些⾏的存在, 因此在
外连结时使⽤WHERE⼦句, 情况会有些不⼀样. 我们来看⼀个例⼦:

SELECT P.product_id
,P.product_name
,P.sale_price
,sp.shop_id
,SP.shop_name
,SP.quantity
FROM Product AS P
LEFT JOIN ShopProduct AS SP
ON SP.product_id = P.product_id
WHERE quantity< 50;

SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,SP.quantity
FROM Product AS P
LEFT OUTER JOIN – 先筛选quantity<50的商品
(SELECT *
FROM ShopProduct
WHERE quantity < 50 ) AS SP
ON SP.product_id = P.product_id;

select * from product p
left join shopproduct sp
on p.product_id=sp.product_id
where quantity<50 or quantity is null;
product_id | product_name | product_type | sale_price | purchase_price | regist_date | shop_id | shop_name | product_id | quantity
------------±-------------±-------------±-----------±---------------±------------±--------±----------±-----------±---------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 | 000A | 东京 | 0001 | 30
0003 | 运动T恤 | 衣服 | 4000 | 2800 | | 000A | 东京 | 0003 | 15
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | 000B | 名古屋 | 0002 | 30
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | 000B | 名古屋 | 0004 | 20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20 | 000B | 名古屋 | 0006 | 10
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 | 000B | 名古屋 | 0007 | 40
0003 | 运动T恤 | 衣服 | 4000 | 2800 | | 000C | 大阪 | 0003 | 20
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11 | | | |
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 | | | |
(9 行记录)

4.2.2.4 在 MySQL 中实现全外连结
有了对左连结和右连结的了解, 就不难理解全外连结的含义了. 全外连结本质上就是对左表和
右表的所有⾏都予以保留, 能⽤ ON 关联到的就把左表和右表的内容在⼀⾏内显示, 不能被关
联到的就分别显示, 然后把多余的列⽤缺失值填充.

4.2.3 多表连结
通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的
数量并没有限制.

4.2.3.1 多表进⾏内连结
⾸先创建⼀个⽤于三表连结的表 InventoryProduct.⾸先我们创建⼀张⽤来管理库存商品的表,
假设商品都保存在 P001 和 P002 这 2 个仓库之中.
CREATE TABLE InventoryProduct
(inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));

select * from inventoryproduct;
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity) VALUES (‘P001’, ‘0001’, 0);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0002’, 120);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0003’, 200);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0004’, 3);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0005’, 0);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0006’, 99);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0007’, 999);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P001’, ‘0008’, 200);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0001’, 10);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0002’, 25);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0003’, 34);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0004’, 19);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0005’, 99);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0006’, 0);
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0007’, 0 );
INSERT INTO InventoryProduct (inventory_id, product_id,inventory_quantity)VALUES (‘P002’, ‘0008’, 18);

inventory_id | product_id | inventory_quantity
--------------±-----------±-------------------
P001 | 0001 | 0
P001 | 0002 | 120
P001 | 0003 | 200
P001 | 0004 | 3
P001 | 0005 | 0
P001 | 0006 | 99
P001 | 0007 | 999
P001 | 0008 | 200
P002 | 0001 | 10
P002 | 0002 | 25
P002 | 0003 | 34
P002 | 0004 | 19
P002 | 0005 | 99
P002 | 0006 | 0
P002 | 0007 | 0
P002 | 0008 | 18
(16 行记录)

select SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
,IP.inventory_quantity
from shopproduct sp
inner join product p
on sp.product_id=p.product_id
inner join inventoryproduct ip
on ip.product_id=p.product_id
where ip.inventory_id=‘P001’;

shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity
---------±----------±-----------±-------------±-----------±-------------------
000A | 东京 | 0001 | T恤衫 | 1000 | 0
000A | 东京 | 0002 | 打孔器 | 500 | 120
000A | 东京 | 0003 | 运动T恤 | 4000 | 200
000B | 名古屋 | 0002 | 打孔器 | 500 | 120
000B | 名古屋 | 0003 | 运动T恤 | 4000 | 200
000B | 名古屋 | 0004 | 菜刀 | 3000 | 3
000B | 名古屋 | 0006 | 叉子 | 500 | 99
000B | 名古屋 | 0007 | 擦菜板 | 880 | 999
000C | 大阪 | 0003 | 运动T恤 | 4000 | 200
000C | 大阪 | 0004 | 菜刀 | 3000 | 3
000C | 大阪 | 0006 | 叉子 | 500 | 99
000C | 大阪 | 0007 | 擦菜板 | 880 | 999
000D | 福冈 | 0001 | T恤衫 | 1000 | 0
(13 行记录)

我们可以看到, 连结第三张表的时候, 也是通过 ON ⼦句指定连结条件(这⾥使⽤最基础的等号
将作为连结条件的 Product 表和 ShopProduct 表中的商品编号 product _id 连结了起来), 由
于 Product 表和 ShopProduct 表已经进⾏了连结,因此就⽆需再对 Product 表和
InventoryProduct 表进⾏连结了(虽然也可以进⾏连结,但结果并不会发⽣改变, 因为本质上并
没有增加新的限制条件).
即使想要把连结的表增加到 4 张、5 张……使⽤ INNER JOIN 进⾏添加的⽅式也是完全相同的.

4.2.3.2 多表进⾏外连结
正如之前所学发现的, 外连结⼀般能⽐内连结有更多的⾏, 从⽽能够⽐内连结给出更多关于主
表的信息, 多表连结的时候使⽤外连结也有同样的作⽤.

4.2.4 ON ⼦句进阶–⾮等值连结
在刚开始介绍连结的时候, 书上提到过, 除了使⽤相等判断的等值连结, 也可以使⽤⽐较运算符
来进⾏连接. 实际上, 包括⽐较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)
在内的所有的逻辑运算都可以放在 ON ⼦句内作为连结条件

4.2.4.1 ⾮等值⾃左连结(SELF JOIN)
使⽤⾮等值⾃左连结实现排名。

练习题:
希望对 Product 表中的商品按照售价赋予排名. ⼀个从集合论出发,使⽤⾃左连结的思路是, 对
每⼀种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使⽤ COUNT 函数计
数. 例如, 对于价格最⾼的商品,
select product_id
,product_name
,sale_price
,count(p2_id) as rank
from(
select P1.product_id
,P1.product_name
,P1.sale_price
,P2.product_id AS P2_id
,P2.product_name AS P2_name
,P2.sale_price AS P2_price
from product p1 left join product p2
on p1.sale_price <= p2.sale_price) as foo
group BY product_id, product_name, sale_price
ORDER BY rank;

product_id | product_name | sale_price | rank
------------±-------------±-----------±-----
0005 | 高压锅 | 6800 | 1
0003 | 运动T恤 | 4000 | 2
0004 | 菜刀 | 3000 | 3
0001 | T恤衫 | 1000 | 4
0007 | 擦菜板 | 880 | 5
0006 | 叉子 | 500 | 7
0002 | 打孔器 | 500 | 7
0008 | 圆珠笔 | 100 | 8
(8 行记录)

4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)
之前的⽆论是外连结内连结, ⼀个共同的必备条件就是连结条件–ON ⼦句, ⽤来指定连结的
条件. 如果你试过不使⽤这个连结条件的连结查询, 你可能已经发现, 结果会有很多⾏. 在连结
去掉 ON ⼦句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结⼜叫笛卡尔积, 后者是⼀个数学
术语. 两个集合做笛卡尔积, 就是使⽤集合 A 中的每⼀个元素与集合 B 中的每⼀个元素组成⼀
个有序的组合. 数据库表(或者⼦查询)的并,交和差都是在纵向上对表进⾏扩张或筛选限制等运
算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, ⽽
交叉连接(笛卡尔积)则是在横向上对表进⾏扩张, 即增加新的列, 这⼀点和连结的功能是⼀致的.
但因为没有了ON⼦句的限制, 会对左表和右表的每⼀⾏进⾏组合, 这经常会导致很多⽆意义的
⾏出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有⼀些⽤处.