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（商品）表

表 2　ShopProduct（商店商品）表

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

表 3　两张表及其包含的列

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

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）的图示

三、外联结——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（库存商品）表

创建该表及插入数据的 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 张表进行内联结

特定的 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）。

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

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

请参阅

• 表的加减法
• SQL 联结

（完）

---

1. 根据表中数据的不同，也存在行数不发生变化的情况。 ↩︎

2. 在 FROM 子句中使用表的别名时，像 Product AS P 这样使用 AS 是标准 SQL 正式的语法。但是在 Oracle 中使用 AS 会发生错误。因此，在 Oracle 中使用时，需要注意不要在 FROM 子句中使用 AS。 ↩︎

3. 虽然不能恢复成完整的被除数，但是这里我们也不再追究了。 ↩︎