SQL基础教程笔记：第七章 集合运算

目录

• 表的加减法
  • 学习重点
  • 什么是集合运算
  • 表的加法——UNION
  • 集合运算的注意事项
  • 包含重复行的集合运算——ALL选项
  • 选取表中公共部分——INTERSECT
  • 记录的减法——EXCEPT
• 联结（以列为单位对表进行联结）
  • 学习重点
  • 什么是联结
  • 内联结——INNER JOIN
  • 外联结——OUTER JOIN
  • 3张以上的表的联结
  • 交叉联结——CROSS JOIN
  • 联结的特定语法和过时语法
• 练习题

表的加减法

学习重点

• 集合运算就是对满足同一规则的记录进行的加减等四则运算。
• 使用UNION（并集）、 INTERSECT（交集）、 EXCEPT（差集）等集合运算符来进行集合运算。
• 集合运算符可以去除重复行。
• 如果希望集合运算符保留重复行，就需要使用ALL选项。

什么是集合运算

集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

表的加法——UNION

首先为大家介绍的集合运算符是进行记录加法运算的 UNION（并集）。在学习具体的使用方法之前，我们首先添加一张表，该表的结构与之前我们使用的 Product（商品）表相同，只是表名变为 Product2。

-- 使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;

商品编号为“0001”~“0003”的 3 条记录在两个表中都存在，因此大家可能会认为结果中会出现重复的记录，但是UNION等集合运算符通常都会除去重复的记录。

集合运算的注意事项

• 作为运算对象的记录的列数必须相同,

-- 列数不一致时会发生错误
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name, sale_price
FROM Product2;

• 作为运算对象的记录中列的类型必须一致,

-- 数据类型不一致时会发生错误
SELECT product_id, sale_price
FROM Product
UNION
SELECT product_id, regist_date
FROM Product2;

实际上，在有些DBMS中，即使数据类型不同，也可以通过隐式类型转换来完成操作。但由于并非所有的DBMS都支持这样的用法，因此还是希望大家能够使用恰当的数据类型来进行运算.

• 通过UNION进行并集运算时可以使用任何形式的SELECT语句，之前学过的WHERE、 GROUP BY、 HAVING等子句都可以使用。但是ORDER BY只能在最后使用一次.

-- ORDER BY子句只在最后使用一次
SELECT product_id, product_name
FROM Product
WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
FROM Product2
WHERE product_type = '厨房用具'
ORDER BY product_id;

包含重复行的集合运算——ALL选项

在UNION的结果中保留重复行的语法。其实非常简单，只需要在UNION后面添加ALL关键字就可以了。这里的ALL选项，在 UNION 之外的集合运算符中同样可以使用.

-- 保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

在集合运算符中使用ALL选项，可以保留重复行。

选取表中公共部分——INTERSECT

-- 使用INTERSECT选取出表中公共部分
-- Oracle SQL Server DB2 PostgreSQL
-- 因为MySQL尚不支持INTERSECT，所以无法使用
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

与使用AND可以选取出一张表中满足多个条件的公共部分不同，INTERSECT 应用于两张表，选取出它们当中的公共记录。
其注意事项与 UNION 相同，我们在“集合运算的注意事项”和“保留重复行的集合运算”中已经介绍过了。希望保留重复行时同样需要使用INTERSECT ALL。

记录的减法——EXCEPT

最后要给大家介绍的集合运算符就是进行减法运算的EXCEPT(差集)，其语法也与 UNION 相同.

-- 使用EXCEPT对记录进行减法运算
-- SQL Server DB2 PostgreSQL
-- MySQL还不支持EXCEPT，因此也无法使用
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

大家可以看到，结果中只包含 Product 表中记录除去 Product2表中记录之后的剩余部分。

-- 被减数和减数位置不同，得到的结果也不同
-- SQL Server DB2 PostgreSQL
-- 从Product2的记录中除去Product中的记录
SELECT product_id, product_name
FROM Product2
EXCEPT
SELECT product_id, product_name
FROM Product
ORDER BY product_id;

联结（以列为单位对表进行联结）

学习重点

• 联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。
• 联结大体上分为内联结和外联结两种。首先请大家牢牢掌握这两种联结的使用方法。
• 请大家一定要使用标准SQL的语法格式来写联结运算，对于那些过时的或者特定SQL中的写法，了解一下即可，不建议使用。

什么是联结

UNION和INTERSECT等集合运算，

• 这些集合运算的特征就是以行方向为单位进行操作。通俗地说,就是进行这些集合运算时，会导致记录行数的增减。使用UNION会增加记录行数，而使用INTERSECT或者EXCEPT会减少记录行数(根据表中数据的不同,也存在行数不发生变化的情况)。
• 但是这些运算不会导致列数的改变。作为集合运算对象的表的前提就是列数要一致。因此，运算结果不会导致列的增减。

本节将要学习的联结(JOIN)运算，简单来说，就是将其他表中的列添加过来，进行“添加列”的运算（图 7-5）。该操作通常用于无法从一张表中获取期望数据(列)的情况。实际上，期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表(3 张以上的表也没关系)中选取数据了。

内联结——INNER JOIN

首先我们来学习内联结（INNER JOIN），它是应用最广泛的联结运算。
本例中我们会继续使用Product 表和第 6 章创建的ShopProduct表。下面我们再来回顾一下这两张表的内容。



下面我们就试着从 Product 表中取出商品名称（product_name）和销售单价（sale_price），并与ShopProduct 表中的内容进行结合，

-- 将两张表进行内联结
-- SQL Server DB2 PostgreSQL MySQL
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;

SP和P分别是这两张表的别名，但别名并不是必需的。在SELECT子句中直接使用ShopProduct和 product_id 这样的表的原名也没有关系，但由于表名太长会影响 SQL 语句的可读性，因此还是希望大家能够习惯使用别名。
我们可以在ON之后指定两张表联结所使用的列（联结键），进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。
在SELECT子句中，像SP.shop_id和P.sale_price这样使用“<表的别名>.<列名>”的形式来指定列。和使用一张表时不同，由于多表联结时，某个列到底属于哪张表比较容易混乱，因此采用了这样的防范措施。从语法上来说，只有那些同时存在于两张表中的列（这里是product_id）必须使用这样的书写方式，其他的列像 shop_id 这样直接书写列名也不会发生错误。但是就像前面说的那样，为了避免混乱，还是希望大家能够在使用联结时按照“< 表的别名 >.< 列名 >”的格式来书写 SELECT 子句中全部的列。

-- 内联结和WHERE子句结合使用
-- SQL Server DB2 PostgreSQL MySQL
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 等工具都可以正常使用。我们可以将联结之后的结果想象为新创建出来的一张表（表 7-4），对这张表使用WHERE子句等工具，这样理解起来就容易多了吧。当然，这张“表”只在 SELECT 语句执行期间存在， SELECT 语句执行之后就会消失。如果希望继续使用这张“表”，还是将它创建成视图吧。

外联结——OUTER JOIN

内联结之外比较重要的就是外联结（OUTER JOIN）了。

-- 将两张表进行外联结
-- SQL Server DB2 PostgreSQL MySQL
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;

由于内联结只能选取出同时存在于两张表中的数据，因此只在 Product 表中存在的2种商品并没有出现在结果之中。相反，对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。外联结名称的由来也跟 NULL 有关，即“结果中包含原表中不存在（在原表之外）的信息”。相反，只包含表内信息的联结也就被称为内联结了。
外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是LEFT和RIGHT.。顾名思义， 使用LEFT时FROM子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。

-- 改写后外联结的结果完全相同
-- SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.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;

3张以上的表的联结

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

-- 创建InventoryProduct表并向其中插入数据
-- DDL ：创建表
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));
-- SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; 
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);
COMMIT;

-- 对3张表进行内联结
-- SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price, IP.inventory_quantity
FROM ShopProduct AS SP INNER JOIN Product AS P
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';

交叉联结——CROSS JOIN

交叉联结（CROSS JOIN,笛卡儿积）,其实这种联结在实际业务中并不会使用（笔者使用这种联结的次数也屈指可数），交叉联结是所有联结运算的基础。

-- 将两张表进行交叉联结
-- SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;

进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。本例中，因为 ShopProduct表存在 13 条记录， Product 表存在 8 条记录，所以结果中就包含了13 × 8 = 104 条记录。

需要注意的是，inner join如果不指定 on 条件进行过滤的话，取得的结果就是两张表的笛卡尔积.

什么是笛卡尔积 ？
举个例子，两个集合 A{1, 2, 3} 和 B{a, b, c}，则两个集合的笛卡尔积为

举个例子：如果 A 表示某学校学生的集合，B 表示该学校所有课程的集合，则 A 与 B 的笛卡尔积就表示这个学校所有可能的选课情况.


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

联结的特定语法和过时语法

之前我们学习的内联结和外联结的语法都符合标准 SQL 的规定，可以在所有 DBMS 中执行，因此大家可以放心使用。但是如果大家之后从事系统开发工作的话，一定会碰到需要阅读他人写的代码并进行维护的情况，而那些使用特定和过时语法的程序就会成为我们的麻烦。SQL 是一门特定语法及过时语法非常多的语言，虽然之前本书中也多次提及，但联结是其中特定语法的部分，现在还有不少年长的程序员和系统工程师仍在使用这些特定的语法。

-- 使用过时语法的内联结（结果与代码清单7-9相同）
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, 
P.sale_price
FROM ShopProduct SP, Product P
WHERE SP.product_id = P.product_id
AND SP.shop_id = '000A';

这样的书写方式所得到的结果与标准语法完全相同，并且这样的语法可以在所有的 DBMS 中执行，并不能算是特定的语法，只是过时了而已。但是，由于这样的语法不仅过时，而且还存在很多其他的问题，因此不推荐大家使用，理由主要有以下三点。

• 第一，使用这样的语法无法马上判断出到底是内联结还是外联结（又或者是其他种类的联结）。
• 第二，由于联结条件都写在 WHERE 子句之中，因此无法在短时间内分辨出哪部分是联结条件，哪部分是用来选取记录的限制条件。
• 第三，我们不知道这样的语法到底还能使用多久。每个 DBMS 的开发者都会考虑放弃过时的语法，转而支持新的语法。虽然并不是马上就不能使用了，但那一天总会到来的。

对于联结的过时语法和特定语法，虽然不建议使用，但还是希望大家能够读懂。

练习题

会将Product表中的8行记录原封不动地选取出来。同时使用UNION和INTERSECT时，INTERSECT会优先执行,由于UNION或者INTERSECT未使用ALL，会排除掉重复的记录，因此对同一张表来说，无论执行多少次操作，原表也不会发生改变。

SELECT COALESCE(SP.shop_id, '不确定') AS shop_id,
 COALESCE(SP.shop_name, '不确定') AS shop_name,
 P.product_id,
 P.product_name,
 P.sale_price
 FROM ShopProduct SP RIGHT OUTER JOIN Product P
 ON SP.product_id = P.product_id
ORDER BY shop_id;

解答
大家想起这个名字有点奇怪的 COALESCE 函数了吗？该函数可以将 NULL 变换为其他的值。虽然名字有些古怪，但使用却很频繁。特别是在希望改变外部连接结果中的 NULL 时，该函数是唯一的选择，因此希望大家能够牢记。