SQL JOIN 以列为单位对表进行联结

2022-02-28 15:19

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

SQL JOIN 以列为单位对表进行联结

一、什么是联结
二、内联结——INNER JOIN
- 2.1 内联结要点 ① ——FROM 子句
- 2.2 内联结要点 ②—— ON 子句
- 2.3 内联结要点 ③ ——SELECT 子句
  - 2.3.1 内联结和 WHERE 子句结合使用
三、外联结——OUTER JOIN
- 3.1 外联结要点 ① ——选取出单张表中全部的信息
- 3.2 外联结要点 ② ——每张表都是主表吗？
四、3 张以上的表的联结
五、交叉联结——CROSS JOIN
六、联结的特定语法和过时语法
请参阅

学习重点

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

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

请大家一定要使用标准 SQL 的语法格式来写联结运算，对于那些过时的或者特定 SQL 中的写法，了解一下即可，不建议使用。

一、什么是联结

在表的加减法中我们学习了 UNION 和 INTERSECT 等集合运算，这些集合运算的特征就是以行方向为单位进行操作。通俗地说，就是进行这些集合运算时，会导致记录行数的增减。使用 UNION 会增加记录行数，而使用 INTERSECT 或者 EXCEPT 会减少记录行数 ^[1]。

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

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

KEYWORD

联结（JOIN）

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

二、内联结——`INNER JOIN`

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

KEYWORD

内联结（INNER JOIN）

本例中我们会继续使用 Product 表和谓词创建的 ShopProduct 表。下面我们再来回顾一下这两张表的内容。

表 7-1　Product（商品）表

商品编号	商品名称	商品种类	销售单价	进货单价	登记日期
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

表 2　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

对这两张表包含的列进行整理后的结果如表 3 所示。

表 3　两张表及其包含的列

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

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

A：两张表中都包含的列 → 商品编号

B：只存在于一张表内的列 → 商品编号之外的列

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

从 ShopProduct 表中的数据我们能够知道，东京店（000A）销售商品编号为 0001、0002 和 0003 的商品，但这些商品的商品名称（product_name）和销售单价（sale_price）在 ShopProduct 表中并不存在，这些信息都保存在 Product 表中。大阪店和名古屋店的情况也是如此。

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

执行结果

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

能够得到上述结果的 SELECT 语句如代码清单 9 所示。

代码清单 9　将两张表进行内联结

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;

特定的 SQL

在 Oracle 的 FROM 子句中不能使用 AS（会发生错误）。因此，在 Oracle 中执行代码清单 9 时，请将 ① 的部分变为“FROM ShopProduct SP INNER JOIN Product P”。

关于内联结，请大家注意以下三点。

2.1 内联结要点 ① ——`FROM` 子句

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

FROM ShopProduct AS SP INNER JOIN Product AS P

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

法则 3

进行联结时需要在 FROM 子句中使用多张表。

2.2 内联结要点 ②—— `ON` 子句

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

KEYWORD

ON 子句

ON SP.product_id = P.product_id

我们可以在 ON 之后指定两张表联结所使用的列（联结键），本例中使用的是商品编号（product_id）。也就是说，ON 是专门用来指定联结条件的，它能起到与 WHERE 相同的作用。需要指定多个键时，同样可以使用 AND、OR。在进行内联结时 ON 子句是必不可少的（如果没有 ON 会发生错误），并且 ON 必须书写在 FROM 和 WHERE 之间。

KEYWORD

联结键

法则 4

进行内联结时必须使用 ON 子句，并且要书写在 FROM 和 WHERE 之间。

举个比较直观的例子，ON 就像是连接河流两岸城镇的桥梁一样（图 6）。

联结条件也可以使用“=”来记述。在语法上，还可以使用 <= 和 BETWEEN 等谓词。但因为实际应用中九成以上都可以用“=”进行联结，所以开始时大家只要记住使用“=”就可以了。使用“=”将联结键关联起来，就能够将两张表中满足相同条件的记录进行“联结”了。

2.3 内联结要点 ③ ——`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 子句中全部的列。

法则 5

使用联结时 SELECT 子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。

2.3.1 内联结和 `WHERE` 子句结合使用

如果并不想了解所有商店的情况，例如只想知道东京店（000A）的信息时，可以像之前学习的那样在 WHERE 子句中添加条件，这样我们就可以从代码清单 9 中得到的全部商店的信息中选取出东京店的记录了。

代码清单 10　内联结和 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";

特定的 SQL

在 Oracle 中执行代码清单 10 时，请将 ① 的部分变为“FROM ShopProduct SP INNER JOIN Product P”（删掉 FROM 子句中的 AS）。

执行结果

 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+-----------
 000A    | 东京      | 0001       | T恤衫         |      1000
 000A    | 东京      | 0002       | 打孔器        |       500
 000A    | 东京      | 0003       | 运动T恤       |      4000

像这样使用联结运算将满足相同规则的表联结起来时，WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用。我们可以将联结之后的结果想象为新创建出来的一张表（表 4），对这张表使用 WHERE 子句等工具，这样理解起来就容易多了吧。

当然，这张“表”只在 SELECT 语句执行期间存在，SELECT 语句执行之后就会消失。如果希望继续使用这张“表”，还是将它创建成视图吧。

表 4　通过联结创建出的表（ProductJoinShopProduct）的图示

`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

三、外联结——`OUTER JOIN`

内联结之外比较重要的就是 外联结（OUTER JOIN） 了。我们再来回顾一下前面的例子。在前例中，我们将 Product 表和 ShopProduct 表进行内联结，从两张表中取出各个商店销售的商品信息。其中，实现“从两张表中取出”的就是联结功能。

KEYWORD

外联结（OUTER JOIN）

外联结也是通过 ON 子句的联结键将两张表进行联结，并从两张表中同时选取相应的列的。基本的使用方法并没有什么不同，只是结果却有所不同。事实胜于雄辩，还是让我们先把之前内联结的 SELECT 语句（代码清单 9）转换为外联结试试看吧。转换的结果请参考代码清单 11。

代码清单 11　将两张表进行外联结

SQL Server DB2 PostgreSQL MySQL

SELECT SP.shop_id, SP.shop_name, P.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;

特定的 SQL

在 Oracle 中执行代码清单 11 时，请将 ① 的部分变为“FROM ShopProduct SP RIGHT OUTER JOIN Product P”（删除掉 FROM 子句中的 AS）。

执行结果

执行结果

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

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

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

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

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

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

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

KEYWORD

LEFT 关键字

RIGHT 关键字

我们还可以像代码清单 12 这样进行改写，意思完全相同。

代码清单 12　改写后外联结的结果完全相同

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;

特定的 SQL

在 Oracle 中执行代码清单 12 时，请将 ① 的部分变为“FROM ShopProduct SP LEFT OUTER JOIN Product P”（删除掉 FROM 子句中的 AS）。

大家可能会犹豫到底应该使用 LEFT 还是 RIGHT，其实它们的功能没有任何区别，使用哪一个都可以。通常使用 LEFT 的情况会多一些，但也并没有非使用这个不可的理由，使用 RIGHT 也没有问题。

法则 6

外联结中使用 LEFT、RIGHT来指定主表。使用二者所得到的结果完全相同。

四、3 张以上的表的联结

通常联结只涉及 2 张表，但有时也会出现必须同时联结 3 张以上的表的情况。原则上联结表的数量并没有限制，下面就让我们来看一下 3 张表的联结吧。

首先我们创建一张用来管理库存商品的表（表 5）。假设商品都保存在 P001 和 P002 这 2 个仓库之中。

表 5　InventoryProduct（库存商品）表

`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

创建该表及插入数据的 SQL 语句请参考代码清单 13。

代码清单 13　创建 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;

特定的 SQL

不同的 DBMS 的事务处理的语法也不尽相同。代码清单 13 中的 DML 语句在 MySQL 中执行时，需要将 ① 部分更改为“START TRANSACTION;”，在 Oracle 和 DB2 中执行时，无需用到 ① 的部分（请删除）。

详细内容请大家参考事务中的“创建事务”。

下面我们从上表中取出保存在 P001 仓库中的商品数量，并将该列添加到代码清单 11 所得到的结果中。联结方式为内联结（外联结的使用方法完全相同），联结键为商品编号（product_id）（代码清单 14）。

代码清单 14　对 3 张表进行内联结

对 3 张表进行内联结

特定的 SQL

在 Oracle 中执行代码清单 14 时，请将 ① 的部分变为“FROM ShopProduct SP INNER JOIN Product P”，将 ② 的部分变为“INNER JOIN InventoryProduct IP”（删除掉 FROM 子句中的 AS）

执行结果

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

在代码清单 11 内联结的 FROM 子句中，再次使用 INNER JOIN 将 InventoryProduct 表也添加了进来。

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

通过 ON 子句指定联结条件的方式也没有发生改变，使用等号将作为联结条件的 Product 表和 ShopProduct 表中的商品编号（product_id）联结起来。由于 Product 表和 ShopProduct 表已经进行了联结，因此这里无需再对 Product 表和 InventoryProduct 表进行联结了（虽然也可以进行联结，但结果并不会发生改变）。

即使想要把联结的表增加到 4 张、5 张……使用 INNER JOIN 进行添加的方式也是完全相同的。

五、交叉联结——`CROSS JOIN`

接下来和大家一起学习第 3 种联结方式——交叉联结（CROSS JOIN）。其实这种联结在实际业务中并不会使用（笔者使用这种联结的次数也屈指可数），那为什么还要在这里进行介绍呢？这是因为交叉联结是所有联结运算的基础。

KEYWORD

交叉联结（CROSS JOIN）

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

代码清单 15　将两张表进行交叉联结

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

特定的 SQL

在 Oracle 中执行代码清单 15 时，请将 ① 的部分变为“FROM ShopProduct SP CROSS JOIN Product P;”（删除掉 FROM 子句中的 AS）。

执行结果

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

可能大家会惊讶于结果的行数，但我们还是先来介绍一下语法结构吧。对满足相同规则的表进行交叉联结的集合运算符是 CROSS JOIN（笛卡儿积）。进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。本例中，因为 ShopProduct 表存在 13 条记录，Product 表存在 8 条记录，所以结果中就包含了 13 × 8 = 104 条记录。

KEYWORD

CROSS JOIN（笛卡儿积）

可能这时会有读者想起在表的加减法中我们提到过集合运算中的乘法会在本节中进行详细学习，这就是上面介绍的交叉联结。

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

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

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

之前我们学习的内联结和外联结的语法都符合标准 SQL 的规定，可以在所有 DBMS 中执行，因此大家可以放心使用。但是如果大家之后从事系统开发工作的话，一定会碰到需要阅读他人写的代码并进行维护的情况，而那些使用特定和过时语法的程序就会成为我们的麻烦。

SQL 是一门特定语法及过时语法非常多的语言，虽然之前本教程中也多次提及，但联结是其中特定语法的部分，现在还有不少年长的程序员和系统工程师仍在使用这些特定的语法。

例如，将本文最初介绍的内联结的 SELECT 语句（代码清单 9）替换为过时语法的结果如下所示（代码清单 16）。

代码清单 16　使用过时语法的内联结（结果与代码清单 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 的开发者都会考虑放弃过时的语法，转而支持新的语法。虽然并不是马上就不能使用了，但那一天总会到来的。

虽然这么说，但是现在使用这些过时语法编写的程序还有很多，到目前为止还都能正常执行。我想大家很可能会碰到这样的代码，因此还是希望大家能够了解这些知识。

法则 7

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

专栏

关系除法

这个部分的教程中我们学习了以下 4 个集合运算符。

UNION（并集）

EXCEPT（差集）

INTERSECT（交集）

CROSS JOIN（笛卡儿积）

虽然交集是一种独立的集合运算，但实际上它也是“只包含公共部分的特殊 UNION”。剩下的 3 个在四则运算中也有对应的运算。但是，除法运算还没有介绍。

难道集合运算中没有除法吗？当然不是，除法运算是存在的。集合运算中的除法通常称为关系除法。关系是数学领域中对表或者视图的称谓，但是并没有定义像 UNION 或者 EXCEPT 这样专用的运算符。如果要定义，估计应该是 DIVIDE（除）吧。但截至目前并没有 DBMS 使用这样的运算符。

KEYWORD

关系除法

为什么只有除法运算不使用运算符（只有除法）对被除数进行运算呢？其中的理由有点复杂，还是让我们先来介绍一下“表的除法”具体是一种什么样的运算吧。

我们使用表 A 和表 B 两张表作为示例用表。

表 A　Skills（技术）表：关系除法中的除数

skill --

Oracle

UNIX

Java

表 B　EmpSkills（员工技术）表：关系除法中的被除数

emp skill

相田 Oracle

相田 UNIX

相田 Java

相田 C#

神崎 Oracle

神崎 UNIX

神崎 Java

平井 UNIX

平井 Oracle

平井 PHP

平井 Perl

平井 C++

若田部 Perl

渡来 Oracle

创建上述两张表并向其中插入数据的 SQL 语句请参考代码清单 A。

代码清单 A　创建 Skills/EmpSkills表并插入数据
-- DDL ：创建表
CREATE TABLE Skills
(skill VARCHAR(32),
PRIMARY KEY(skill));

CREATE TABLE EmpSkills
(emp   VARCHAR(32),
skill VARCHAR(32),
PRIMARY KEY(emp, skill));
SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; -------------①

INSERT INTO Skills VALUES("Oracle");
INSERT INTO Skills VALUES("UNIX");
INSERT INTO Skills VALUES("Java");

INSERT INTO EmpSkills VALUES("相田", "Oracle");
INSERT INTO EmpSkills VALUES("相田", "UNIX");
INSERT INTO EmpSkills VALUES("相田", "Java");
INSERT INTO EmpSkills VALUES("相田", "C#");
INSERT INTO EmpSkills VALUES("神崎", "Oracle");
INSERT INTO EmpSkills VALUES("神崎", "UNIX");
INSERT INTO EmpSkills VALUES("神崎", "Java");
INSERT INTO EmpSkills VALUES("平井", "UNIX");
INSERT INTO EmpSkills VALUES("平井", "Oracle");
INSERT INTO EmpSkills VALUES("平井", "PHP");
INSERT INTO EmpSkills VALUES("平井", "Perl");
INSERT INTO EmpSkills VALUES("平井", "C++");
INSERT INTO EmpSkills VALUES("若田部", "Perl");
INSERT INTO EmpSkills VALUES("渡来", "Oracle");

COMMIT;
特定的 SQL

不同的 DBMS 的事务处理的语法也不尽相同。代码清单 A 中的 DML 语句在 MySQL 中执行时，需要将 ① 部分更改为“START TRANSACTION;”，在 Oracle 和 DB2 中执行时，无需用到 ① 的部分（请删除）。

详细内容请大家参考事务中的“创建事务”。

EmpSkills 表中保存了某个系统公司员工所掌握的技术信息。例如，从该表中我们可以了解到相田掌握了 Oracle、UNIX、Java、C# 这 4 种技术。

下面我们来思考一下如何从该表中选取出掌握了 Skills 表中所有 3 个领域的技术的员工吧（代码清单 B）。

代码清单 B　选取出掌握所有 3 个领域的技术的员工
SELECT DISTINCT emp
 FROM EmpSkills ES1
WHERE NOT EXISTS
       (SELECT skill
          FROM Skills
        EXCEPT
        SELECT skill
          FROM EmpSkills ES2
         WHERE ES1.emp = ES2.emp);
这样我们就得到了包含相田和神崎 2 人的结果。虽然平井也掌握了 Orcale 和 UNIX，但很可惜他不会使用 Java，因此没有选取出来。

执行结果（关系除法中的商）
emp
------
神崎
相田
这样的结果满足了除法运算的基本规则。肯定有读者会产生这样的疑问：“到底上述运算中什么地方是除法运算呢？”实际上这和数值的除法既相似又有所不同，大家从与除法相对的乘法运算的角度去思考就能得到答案了。

除法和乘法是相辅相成的关系，除法运算的结果（商）乘以除数就能得到除法运算前的被除数了。例如对于 20÷4 = 5 来说，就是 5(商)×4(除数) = 20( 被除数)（图 A）。

图 A　除法运算和乘法运算相辅相成的关系图

关系除法中这样的规则也是成立的。通过商和除数相乘，也就是交叉联结，就能够得到作为被除数的集合了 ^[3]。

如上所述，除法运算是集合运算中最复杂的运算，但是其在实际业务中的应用十分广泛，因此希望大家能在达到中级以上水平时掌握其使用方法。此外，想要详细了解 SQL 中除法运算实现方法的读者，可以参考《SQL进阶教程》中的 1-4 节和 1-7 节。

`skill`	--
Oracle
UNIX
Java

emp	skill
相田	Oracle
相田	UNIX
相田	Java
相田	C#
神崎	Oracle
神崎	UNIX
神崎	Java
平井	UNIX
平井	Oracle
平井	PHP
平井	Perl
平井	C++
若田部	Perl
渡来	Oracle

请参阅

表的加减法
SQL 联结

（完）

根据表中数据的不同，也存在行数不发生变化的情况。 ↩︎
在 FROM 子句中使用表的别名时，像 Product AS P 这样使用 AS 是标准 SQL 正式的语法。但是在 Oracle 中使用 AS 会发生错误。因此，在 Oracle 中使用时，需要注意不要在 FROM 子句中使用 AS。 ↩︎
虽然不能恢复成完整的被除数，但是这里我们也不再追究了。 ↩︎