SQL 子查询简介[亲测有效]

学习重点

• 一言以蔽之，子查询就是一次性视图（SELECT 语句）。与视图不同，子查询在 SELECT 语句执行完毕之后就会消失。

• 由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。

• 标量子查询就是只能返回一行一列的子查询。

一、子查询和视图

前一节我们学习了视图这个非常方便的工具，本节将学习以视图为基础的子查询。子查询的特点概括起来就是一张一次性视图。

KEYWORD

• 子查询

我们先来复习一下视图的概念，视图并不是用来保存数据的，而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利。反之，子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。接下来，就让我们拿前一节使用的视图 ProductSum（商品合计）来与子查询进行一番比较吧。

首先，我们再来看一下视图 ProductSum 的定义和视图所对应的 SELECT 语句（代码清单 8）。

代码清单 8　视图 ProductSum 和确认用的 SELECT 语句

-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

-- 确认创建好的视图
SELECT product_type, cnt_product
  FROM ProductSum;

能够实现同样功能的子查询如代码清单 9 所示。

代码清单 9　子查询

特定的 SQL

在 Oracle 的 FROM 子句中，不能使用 AS（会发生错误），因此，在 Oracle 中执行代码清单 9 时，需要将 ① 中的“) AS ProductSum;”变为“) ProductSum;”

两种方法得到的结果完全相同。

执行结果

 product_type | cnt_product
--------------+------------
 衣服         |           2
 办公用品     |           2
 厨房用具     |           4

如上所示，子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。虽然“AS ProductSum”就是子查询的名称，但由于该名称是一次性的，因此不会像视图那样保存在存储介质（硬盘）之中，而是在 SELECT 语句执行之后就消失了。

实际上，该 SELECT 语句包含嵌套的结构，首先会执行 FROM 子句中的 SELECT 语句，然后才会执行外层的 SELECT 语句（图 4）。

① 首先执行 FROM 子句中的 SELECT 语句（子查询）

SELECT product_type, COUNT(*) AS cnt_product
  FROM Product
 GROUP BY product_type;

② 根据 ① 的结果执行外层的 SELECT 语句

SELECT product_type, cnt_product
  FROM ProductSum;

法则 6

子查询作为内层查询会首先执行。

• 增加子查询的层数

  由于子查询的层数原则上没有限制，因此可以像“子查询的 FROM 子句中还可以继续使用子查询，该子查询的 FROM 子句中还可以再使用子查询……”这样无限嵌套下去（代码清单 10）。

  代码清单 10　尝试增加子查询的嵌套层数

  SQL Server DB2 PostgreSQL MySQL

  SELECT product_type, cnt_product
  FROM (SELECT *
          FROM (SELECT product_type, COUNT(*) AS cnt_product
                  FROM Product
                  GROUP BY product_type) AS ProductSum -----①
          WHERE cnt_product = 4) AS ProductSum2; -----------②
  

  特定的 SQL

  在 Oracle 的 FROM 子句中不能使用 AS（会发生错误），因此，在 Oracle 中执行代码清单 10 时，需要将 ① 中的“) AS ProductSum”变为“) ProductSum”，将 ② 中的“) AS ProductSum2;”变为“) ProductSum2;”。

  执行结果

  product_type | cnt_product
  --------------+------------
  厨房用具     |           4
  

  最内层的子查询（ProductSum）与之前一样，根据商品种类（product_type）对数据进行汇总，其外层的子查询将商品数量（cnt_product）限定为 4，结果就得到了 1 行厨房用具的数据。

  但是，随着子查询嵌套层数的增加，SQL 语句会变得越来越难读懂，性能也会越来越差。因此，请大家尽量避免使用多层嵌套的子查询。

二、子查询的名称

之前的例子中我们给子查询设定了 ProductSum 等名称。原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。在上述例子中，子查询用来对 Product 表的数据进行汇总，因此我们使用了后缀 Sum 作为其名称。

为子查询设定名称时需要使用 AS 关键字，该关键字有时也可以省略 ^[1]。

三、标量子查询

接下来我们学习子查询中的标量子查询（scalar subquery）。

KEYWORD

• 标量子查询

• 什么是标量

  标量就是单一的意思，在数据库之外的领域也经常使用。

  KEYWORD

  • 标量

  上一节我们学习的子查询基本上都会返回多行结果（虽然偶尔也会只返回 1 行数据）。由于结构和表相同，因此也会有查询不到结果的情况。

  而标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1 列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“东京都”这样的值。

  KEYWORD

  • 返回值

    返回值就是函数或者 SQL 语句等处理执行之后作为结果返回的值。

  法则 7

  标量子查询就是返回单一值的子查询。

  细心的读者可能会发现，由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。这也正是标量子查询的优势所在。下面就让我们赶快来试试看吧。

• 在 WHERE 子句中使用标量子查询

  在 数据的删除 中，我们练习了通过各种各样的条件从 Product（商品）表中读取数据。大家有没有想过通过下面这样的条件查询数据呢？

  “查询出销售单价高于平均销售单价的商品。”

  或者说想知道价格处于上游的商品时，也可以通过上述条件进行查询。

  然而这并不是用普通方法就能解决的。如果我们像下面这样使用 AVG 函数的话，就会发生错误。

  

  虽然这样的 SELECT 语句看上去能够满足我们的要求，但是由于在 WHERE 子句中不能使用聚合函数，因此这样的 SELECT 语句是错误的。

  那么究竟什么样的 SELECT 语句才能满足上述条件呢？

  这时标量子查询就可以发挥它的功效了。首先，如果想要求出 Product 表中商品的平均销售单价（sale_price），可以使用代码清单 11 中的 SELECT 语句。

  代码清单 11　计算平均销售单价的标量子查询

  SELECT AVG(sale_price)
  FROM Product;
  

  执行结果

          avg
  ----------------------
  2097.5000000000000000
  

  AVG 函数的使用方法和 COUNT 函数相同，其计算式如下所示。

  (1000＋500＋4000＋3000＋6800＋500＋880＋100) / 8＝2097.5

  这样计算出的平均单价大约就是 2100 日元。不难发现，代码清单 11 中的 SELECT 语句的查询结果是单一的值（2097.5）。因此，我们可以直接将这个结果用到之前失败的查询之中。正确的 SQL 如代码清单 12 所示。

  代码清单 12　选取出销售单价（sale_price）高于全部商品的平均单价的商品

  

  执行结果

  product_id | product_name | sale_price
  ------------+--------------+-----------
  0003       | 运动T恤      |       4000
  0004       | 菜刀         |       3000
  0005       | 高压锅       |       6800
  

  前一节我们已经介绍过，使用子查询的 SQL 会从子查询开始执行。因此，这种情况下也会先执行下述计算平均单价的子查询（图 5）。

  

  -- ① 内层的子查询
  SELECT AVG(sale_price)
  FROM Product;
  

  子查询的结果是 2097.5，因此会用该值替换子查询的部分，生成如下 SELECT 语句。

  -- ② 外层的查询
  SELECT product_id, product_name, sale_price
  FROM Product
  WHERE sale_price > 2097.5
  

  大家都能看出该 SQL 没有任何问题可以正常执行，结果如上所述。

四、标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地 方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

例如，在 SELECT 子句当中使用之前计算平均值的标量子查询的 SQL 语句，如代码清单 13 所示。

代码清单 13　在 SELECT 子句中使用标量子查询

执行结果

 product_id | product_name  | sale_price |       avg_price
------------+---------------+------------+----------------------
 0001       | T恤衫         |       1000 | 2097.5000000000000000
 0002       | 打孔器        |        500 | 2097.5000000000000000
 0003       | 运动T恤       |       4000 | 2097.5000000000000000
 0004       | 菜刀          |       3000 | 2097.5000000000000000
 0005       | 高压锅        |       6800 | 2097.5000000000000000
 0006       | 叉子          |        500 | 2097.5000000000000000
 0007       | 擦菜板        |        880 | 2097.5000000000000000
 0008       | 圆珠笔        |        100 | 2097.5000000000000000

从上述结果可以看出，在商品一览表中加入了全部商品的平均单价。有时我们会需要这样的单据。

此外，我们还可以像代码清单 14 中的 SELECT 语句那样，在 HAVING 子句中使用标量子查询。

代码清单 14　在 HAVING 子句中使用标量子查询

执行结果

 product_type |         avg
--------------+----------------------
 衣服         | 2500.0000000000000000
 厨房用具     | 2795.0000000000000000

该查询的含义是想要选取出按照商品种类计算出的销售单价高于全部商品的平均销售单价的商品种类。如果在 SELECT 语句中不使用 HAVING 子句的话，那么平均销售单价为 300 日元的办公用品也会被选取出来。但是，由于全部商品的平均销售单价是 2097.5 日元，因此低于该平均值的办公用品会被 HAVING 子句中的条件排除在外。

五、使用标量子查询时的注意事项

最后我们来介绍一下使用标量子查询时的注意事项，那就是该子查询绝对不能返回多行结果。也就是说，如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中。

例如，如下的 SELECT 子查询会发生错误。

发生错误的原因很简单，就是因为会返回如下多行结果。

         avg
----------------------
2500.0000000000000000
300.0000000000000000
2795.0000000000000000

在 1 行 SELECT 子句之中当然不可能使用 3 行数据。因此，上述 SELECT 语句会返回“因为子查询返回了多行数据所以不能执行”这样的错误信息 ^[2]。

请参阅

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

（完）