SQL 关联子查询

目录

• 一、普通的子查询和关联子查询的区别
• 二、关联子查询也是用来对集合进行切分的
• 三、结合条件一定要写在子查询中
• 请参阅

学习重点

• 关联子查询会在细分的组内进行比较时使用。

• 关联子查询和 GROUP BY 子句一样，也可以对表中的数据进行切分。

• 关联子查询的结合条件如果未出现在子查询之中就会发生错误。

一、普通的子查询和关联子查询的区别

按此前所学，使用子查询就能选取出销售单价（sale_price）高于全部商品平均销售单价的商品。这次我们稍稍改变一下条件，选取出各商品种类中高于该商品种类的平均销售单价的商品。

• 按照商品种类与平均销售单价进行比较

  只通过语言描述可能难以理解，还是让我们来看看具体示例吧。我们以厨房用具中的商品为例，该分组中包含了表 1 所示的 4 种商品。

  表 1　厨房用具中的商品

  商品名称	销售单价
  菜刀	3000
  高压锅	6800
  叉子	500
  擦菜板	880

  因此，计算上述 4 种商品的平均价格的算术式如下所示。

  (3000 + 6800 + 500 + 880) / 4 = 2795 (日元)

  这样我们就能得知该分组内高于平均价格的商品是菜刀和高压锅了，这两种商品就是我们要选取的对象。

  我们可以对余下的分组继续使用同样的方法。衣服分组的平均销售单价是：

  (1000 + 4000) / 2 = 2500 (日元)

  因此运动T恤就是要选取的对象。办公用品分组的平均销售单价是：

  (500 + 100) / 2 = 300 (日元)

  因此打孔器就是我们要选取的对象。

  这样大家就能明白该进行什么样的操作了吧。我们并不是要以全部商品为基础，而是要以细分的组为基础，对组内商品的平均价格和各商品的销售单价进行比较。

  按照商品种类计算平均价格并不是什么难事，我们已经学习过了，只需按照代码清单 15 那样，使用 GROUP BY 子句就可以了。

  代码清单 15　按照商品种类计算平均价格

  SELECT AVG(sale_price)
  FROM Product
  GROUP BY product_type;
  

  但是，如果我们使用前一节（标量子查询）的方法，直接把上述 SELECT 语句使用到 WHERE 子句当中的话，就会发生错误。

  -- 发生错误的子查询
  SELECT product_id, product_name, sale_price
  FROM Product
  WHERE sale_price > (SELECT AVG(sale_price)
                          FROM Product
                          GROUP BY product_type);
  

  出错原因前一节已经讲过了，该子查询会返回 3 行结果（2795、2500、300），并不是标量子查询。在 WHERE 子句中使用子查询时，该子查询的结果必须是单一的。

  但是，如果以商品种类分组为单位，对销售单价和平均单价进行比较，除此之外似乎也没有其他什么办法了。到底应该怎么办才好呢？

• 使用关联子查询的解决方案

  这时就轮到我们的好帮手——关联子查询登场了。

  KEYWORD

  • 关联子查询

  只需要在刚才的 SELECT 语句中追加一行，就能得到我们想要的结果了 ^[1]。事实胜于雄辩，还是让我们先来看看修改之后的 SELECT 语句吧（代码清单 16）。

  代码清单 16　通过关联子查询按照商品种类对平均销售单价进行比较

  

  特定的 SQL

  Oracle 中不能使用 AS（会发生错误）。因此，在 Oracle 中执行代码清单 16 时，请大家把 ① 中的 FROM Product AS P1 变为 FROM Product P1，把 ② 中的 FROM Product AS P2 变为 FROM Product P2。

  执行结果

  product_type  | product_name  | sale_price
  ---------------+---------------+------------
  办公用品      | 打孔器         |        500
  衣服          | 运动T恤        |       4000
  厨房用具      | 菜刀           |       3000
  厨房用具      | 高压锅         |       6800
  

  这样我们就能选取出办公用品、衣服和厨房用具三类商品中高于该类商品的平均销售单价的商品了。

  这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。

  这次由于作为比较对象的都是同一张 Product 表，因此为了进行区别，分别使用了 P1 和 P2 两个别名。在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“<表名>.<列名>”的形式记述。

  在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。因此，使用关联子查询时，通常会使用“限定（绑定）”或者“限制”这样的语言，例如本次示例就是限定“商品种类”对平均单价进行比较。

  法则 8

  在细分的组内进行比较时，需要使用关联子查询。

二、关联子查询也是用来对集合进行切分的

换个角度来看，其实关联子查询也和 GROUP BY 子句一样，可以对集合进行切分。

大家还记得我们用来说明 GROUP BY 子句的图（图 6）吗？

上图显示了作为记录集合的表是如何按照商品种类被切分的。使用关联子查询进行切分的图示也基本相同（图 7）。

我们首先需要计算各个商品种类中商品的平均销售单价，由于该单价会用来和商品表中的各条记录进行比较，因此关联子查询实际只能返回 1 行结果。这也是关联子查询不出错的关键。关联子查询执行时，DBMS 内部的执行情况如图 8 所示。

如果商品种类发生了变化，那么用来进行比较的平均单价也会发生变化，这样就可以将各种商品的销售单价和平均单价进行比较了。关联子查询的内部执行结果对于初学者来说是比较难以理解的，但是像上图这样将其内部执行情况可视化之后，理解起来就变得非常容易了吧。

三、结合条件一定要写在子查询中

下面给大家介绍一下 SQL 初学者在使用关联子查询时经常犯的一个错误，那就是将关联条件写在子查询之外的外层查询之中。请大家看一下下面这条 SELECT 语句。

上述 SELECT 语句只是将子查询中的关联条件移到了外层查询之中，其他并没有任何更改。但是，该 SELECT 语句会发生错误，不能正确执行。允许存在这样的书写方法可能并不奇怪，但是 SQL 的规则禁止这样的书写方法。

该书写方法究竟违反了什么规则呢？那就是关联名称的作用域。虽然这一术语看起来有些晦涩难懂，但是一解释大家就明白了。关联名称就是像 P1、P2 这样作为表别名的名称，作用域（scope）就是生存范围（有效范围）。也就是说，关联名称存在一个有效范围的限制。

KEYWORD

• 关联名称

• 作用域

具体来讲，子查询内部设定的关联名称，只能在该子查询内部使用（图 9）。换句话说，就是“内部可以看到外部，而外部看不到内部”。

请大家一定不要忘记关联名称具有一定的有效范围。如前所述，SQL 是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时只会留下执行结果，作为抽出源的 P2 表其实已经不存在了 ^[2]。因此，在执行外层查询时，由于 P2 表已经不存在了，因此就会返回“不存在使用该名称的表”这样的错误。

请参阅

• 视图
• 子查询
• 关联子查询

（完）

---

1. 事实上，对于代码清单 16 中的 SELECT 语句，即使在子查询中不使用 GROUP BY 子句，也能得到正确的结果。这是因为在 WHERE 子句中追加了“P1.product_type=P2.product_type”这个条件，使得 AVG 函数按照商品种类进行了平均值计算。但是为了跟前面出错的查询进行对比，这里还是加上了 GROUP BY 子句。 ↩︎

2. 当然，消失的其实只是 P2 这个名称而已，Product 表以及其中的数据还是存在的。 ↩︎