SQL 如何使用内联结、外联结和交叉联结

本文介绍 SQL 如何使用内联结(INNER JOIN)、外联结(OUTER JOIN)和交叉联结(CROSS JOIN)。简单来说,就是将其他表中的列添加过来,进行“添加列”的运算。

一、什么是联结

SQL 如何进行并集、交集、差集等集合运算 中,我们学习了 UNIONINTERSECT 等集合运算,这些集合运算的特征就是以行方向为单位进行操作。

通俗地说,就是进行这些集合运算时,会导致记录行数的增减。使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数 1

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

本文将要学习的**联结(JOIN)**运算,简单来说,就是将其他表中的列添加过来,进行“添加列”的运算(图 5)。该操作通常用于无法从一张表中获取期望数据(列)的情况。

截至目前,我们介绍的示例基本上都是从一张表中选取数据,但实际上,期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表(3 张以上的表也没关系)中选取数据了。

联结的图示

图 5 联结的图示

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

二、内联结——INNER JOIN

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

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

表 1 Product(商品)表

product_id(商品编号)product_name(商品名称)product_type(商品种类)sale_price(销售单价)purchase_price(进货单价)regist_date(登记日期)
0001T 恤衫衣服10005002009-09-20
0002打孔器办公用品5003202009-09-11
0003运动 T 恤衣服40002800
0004菜刀厨房用具300028002009-09-20
0005高压锅厨房用具680050002009-01-15
0006叉子厨房用具5002009-09-20
0007擦菜板厨房用具8807902008-04-28
0008圆珠笔办公用品1002009-11-11

表 2 ShopProduct(商店商品)表

shop_id(商店编号)shop_name(商店名称)product_id(商品编号)quantity(数量)
000A东京000130
000A东京000250
000A东京000315
000B名古屋000230
000B名古屋0003120
000B名古屋000420
000B名古屋000610
000B名古屋000740
000C大阪000320
000C大阪000450
000C大阪000690
000C大阪000770
000D福冈0001100

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

表 3 两张表及其包含的列

ProductShopProduct
商品编号
商品名称
商品种类
销售单价
进货单价
登记日期
商店编号
商店名称
数量

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

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

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

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

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 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

1
2
3
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;

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

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

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

1
FROM ShopProduct AS SP INNER JOIN Product AS P

使用关键字 INNER JOIN 就可以将两张表联结在一起了。SPP 分别是这两张表的别名,但别名并不是必需的。

SELECT 子句中直接使用 ShopProductproduct_id 这样的表的原名也没有关系,但由于表名太长会影响 SQL 语句的可读性,因此还是希望大家能够习惯使用别名 2

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

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

1
ON SP.product_id = P.product_id

我们可以在 ON 之后指定两张表联结所使用的列(联结键),本例中使用的是商品编号(product_id)。

也就是说,ON 是专门用来指定联结条件的,它能起到与 WHERE 相同的作用。需要指定多个键时,同样可以使用 ANDOR

在进行内联结时 ON 子句是必不可少的(如果没有 ON 会发生错误),并且 ON 必须书写在 FROMWHERE 之间。

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

使用 ON 进行两表加法运算(和集)的图示

图 6 使用 ON 进行两表加法运算(和集)的图示

联结条件也可以使用“=”来记述。在语法上,还可以使用 <=BETWEEN 等谓词。

但因为实际应用中九成以上都可以用“=”进行联结,所以开始时大家只要记住使用“=”就可以了。

使用“=”将联结键关联起来,就能够将两张表中满足相同条件的记录进行“联结”了。

2.3 内联结要点 ③ ——SELECT 子句

第三点要注意的是,在 SELECT 子句中指定的列。

1
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

SELECT 子句中,像 SP.shop_idP.sale_price 这样使用“<表的别名>.<列名>”的形式来指定列。

和使用一张表时不同,由于多表联结时,某个列到底属于哪张表比较容易混乱,因此采用了这样的防范措施。

从语法上来说,只有那些同时存在于两张表中的列(这里是 product_id)必须使用这样的书写方式,其他的列像 shop_id 这样直接书写列名也不会发生错误。

但是就像前面说的那样,为了避免混乱,还是希望大家能够在使用联结时按照“<表的别名>.<列名>”的格式来书写 SELECT 子句中全部的列。

2.4 内联结和 WHERE 子句结合使用

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

代码清单 10 内联结和 WHERE 子句结合使用

SQL Server DB2 PostgreSQL MySQL

1
2
3
4
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
5
 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+-----------
 000A    | 东京      | 0001       | T恤衫         |      1000
 000A    | 东京      | 0002       | 打孔器        |       500
 000A    | 东京      | 0003       | 运动T恤       |      4000

像这样使用联结运算将满足相同规则的表联结起来时,WHEREGROUP BYHAVINGORDER BY 等工具都可以正常使用。

我们可以将联结之后的结果想象为新创建出来的一张表(表 4),对这张表使用 WHERE 子句等工具,这样理解起来就容易多了吧。

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

表 4 通过联结创建出的表(ProductJoinShopProduct)的图示

shop_id(编号)shop_name(商店名称)product_id(商品编号)product_name(商品名称)sale_price(销售单价)
000A东京0001T 恤衫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福冈0001T 恤衫1000

三、外联结——OUTER JOIN

内联结之外比较重要的就是 外联结(OUTER JOIN 了。我们再来回顾一下前面的例子。

在前例中,我们将 Product 表和 ShopProduct 表进行内联结,从两张表中取出各个商店销售的商品信息。其中,实现“从两张表中取出”的就是联结功能。

外联结也是通过 ON 子句的联结键将两张表进行联结,并从两张表中同时选取相应的列的。基本的使用方法并没有什么不同,只是结果却有所不同。

事实胜于雄辩,还是让我们先把之前内联结的 SELECT 语句(代码清单 9)转换为外联结试试看吧。转换的结果请参考代码清单 11。

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

SQL Server DB2 PostgreSQL MySQL

1
2
3
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;

执行结果:

执行结果

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

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

这正是外联结的关键点。多出的 2 条记录是高压锅和圆珠笔,这 2 条记录在 ShopProduct 表中并不存在,也就是说,这 2 种商品在任何商店中都没有销售。

由于内联结只能选取出同时存在于两张表中的数据,因此只在 Product 表中存在的 2 种商品并没有出现在结果之中。

相反,对于外联结来说,只要数据存在于某一张表当中,就能够读取出来。在实际的业务中,例如想要生成固定行数的单据时,就需要使用外联结。

如果使用内联结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外联结能够得到固定行数的结果。

虽说如此,那些表中不存在的信息我们还是无法得到,结果中高压锅圆珠笔的商店编号和商店名称都是 NULL(具体信息大家都不知道,真是无可奈何)。

外联结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在(在原表之外)的信息”。相反,只包含表内信息的联结也就被称为内联结了。

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

外联结还有一点非常重要,那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。

指定主表的关键字是 LEFTRIGHT。顾名思义,使用 LEFTFROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表。

代码清单 11 中使用了 RIGHT,因此,右侧的表,也就是 Product 表是主表。

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

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

SQL Server DB2 PostgreSQL MySQL

1
2
3
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;

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

通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题。

四、3 张以上的表的联结

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

首先我们创建一张用来管理库存商品的表(表 5)。假设商品都保存在 P001P002 这 2 个仓库之中。

表 5 InventoryProduct(库存商品)表

inventory_id(仓库编号)product_id(商品编号)inventory_quantity(库存数量)
P00100010
P0010002120
P0010003200
P00100043
P00100050
P001000699
P0010007999
P0010008200
P002000110
P002000225
P002000334
P002000419
P002000599
P00200060
P00200070
P002000818

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

代码清单 13 创建 InventoryProduct 表并向其中插入数据

1
2
3
4
5
6
-- 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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;

下面我们从上表中取出保存在 P001 仓库中的商品数量,并将该列添加到代码清单 11 所得到的结果中。

联结方式为内联结(外联结的使用方法完全相同),联结键为商品编号(product_id)(代码清单 14)。

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

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

执行结果:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 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 JOINInventoryProduct 表也添加了进来。

1
2
3
4
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。其实这种联结在实际业务中并不会使用(笔者使用这种联结的次数也屈指可数),那为什么还要在这里进行介绍呢?这是因为交叉联结是所有联结运算的基础。

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

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

SQL Server DB2 PostgreSQL MySQL

1
2
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
  FROM ShopProduct AS SP CROSS JOIN Product AS P; -----①

执行结果:

  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
 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 条记录。

可能这时会有读者想起前面我们在 SQL 如何进行并集、交集、差集等集合运算 中提到过集合运算中的乘法会在本文中进行详细学习,这就是上面介绍的交叉联结。

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

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

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

之前我们学习的内联结和外联结的语法都符合标准 SQL 的规定,可以在所有 DBMS 中执行,因此大家可以放心使用。

但是如果大家之后从事系统开发工作的话,一定会碰到需要阅读他人写的代码并进行维护的情况,而那些使用特定和过时语法的程序就会成为我们的麻烦。

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

例如,将本文前面介绍的内联结的 SELECT 语句(代码清单 9)替换为过时语法的结果如下所示(代码清单 16)。

代码清单 16 使用过时语法的内联结(结果与代码清单 9 相同)

1
2
3
4
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 的开发者都会考虑放弃过时的语法,转而支持新的语法。虽然并不是马上就不能使用了,但那一天总会到来的。

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

(完)


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

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