使用子查询

SQL
什么是子查询，如何使用它们。
作者

Davon

发布于

2020年7月30日

修改于

2026年4月12日

11.1 子查询

SELECT 语句是 SQL的查询。我们迄今为止所看到的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。

查询（query） :任何 SQL 语句都是查询。但此术语一般指 SELECT 语句。

SQL 还允许创建子查询（subquery），即嵌套在其他查询中的查询。为什么要这样做呢？理解这个概念的最好方法是考察几个例子。

11.2 利用子查询进行过滤

本书所有课中使用的数据库表都是关系表（关于每个表及关系的描述，请参阅附录 A）。

订单存储在两个表中。每个订单包含订单编号、客户ID、订单日期，在 Orders 表中存储为一行。各订单的物品存储在相关的OrderItems 表中。Orders 表不存储顾客信息，只存储顾客 ID。顾客的实际信息存储在 Customers 表中。

现在，假如需要列出订购物品 RGAN01 的所有顾客，应该怎样检索？下面列出具体的步骤。

(1)检索包含物品 RGAN01 的所有订单的编号。

(2)检索具有前一步骤列出的订单编号的所有顾客的 ID。

(3)检索前一步骤返回的所有顾客 ID的顾客信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。

也可以使用子查询来把 3 个查询组合成一条语句。

第一条 SELECT 语句的含义很明确，它对 prod_id 为 RGAN01 的所有订单物品，检索其 order_num 列。输出列出了两个包含此物品的订单：

SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单 20007和20008相关的顾客ID。利用第 5课介绍的IN子句，编写如下的SELECT语句：

SELECT cust_id 
FROM Orders WHERE order_num IN (20007,20008);

现在，结合这两个查询，把第一个查询（返回订单号的那一个）变为子查询。请看下面的 SELECT 语句：

SELECT cust_id  
FROM Orders  
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'); 
cust_id   
1000000004   
1000000005

在 SELECT 语句中，子查询总是从内向外处理。在处理上面的 SELECT 语句时，DBMS实际上执行了两个操作。

首先，它执行下面的查询：

SELECT order_num FROM orderitems WHERE prod_id='RGAN01'

此查询返回两个订单号：20007 和20008。然后，这两个值以IN 操作符

要求的逗号分隔的格式传递给外部查询的 WHERE 子句。外部查询变成：

SELECT cust_id FROM orders WHERE order_num IN (20007,20008)

可以看到，输出是正确的，与前面硬编码 WHERE 子句所返回的值相同。

提示：格式化 SQL

包含子查询的 SELECT 语句难以阅读和调试，它们在较为复杂时更是如此。如上所示，把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用。

顺便一提，这就是颜色编码起作用的地方，好的 DBMS客户端正是出于这个原因使用了颜色代码 SQL。

现在得到了订购物品 RGAN01 的所有顾客的 ID。下一步是检索这些顾客ID 的顾客信息。检索两列的 SQL 语句为：

SELECT cust_name, cust_contact  
FROM Customers  
WHERE cust_id IN (100000004, 100000005);

可以把其中的 WHERE 子句转换为子查询，而不是硬编码这些顾客 ID：

SELECT cust_name, cust_contact  
FROM Customers  
WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));

cust_name

cust_contact

Fun4All

Denise L. Stephens

The Toy Store

Kim Howard

为了执行上述 SELECT 语句，DBMS实际上必须执行三条 SELECT 语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的 WHERE子句。外面的子查询返回顾客 ID列表，此顾客 ID列表用于最外层查询的 WHERE 子句。最外层查询返回所需的数据。

可见，在 WHERE 子句中使用子查询能够编写出功能很强且很灵活的 SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

注意：只能是单列

作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

注意：子查询和性能

这里给出的代码有效，并且获得了所需的结果。但是，使用子查询并不总是执行这类数据检索的最有效方法。更多的论述，请参阅第 12课，其中将再次给出这个例子。

11.3 作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID存储在Orders 表中。

执行这个操作，要遵循下面的步骤：

  1. 从 Customers 表中检索顾客列表；

  2. 对于检索出的每个顾客，统计其在 Orders 表中的订单数目。

正如前两课所述，可以使用 SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE 子句来过滤某个特定的顾客ID，仅对该顾客的订单进行计数。例如，下面的代码对顾客1000000001 的订单进行计数：

SELECT COUNT(*) AS orders FROM Orders WHERE cust_id = 1000000001;

要对每个顾客执行 COUNT(*)，应该将它作为一个子查询。请看下面的代码：

SELECT cust_name,  
cust_state,  
(SELECT COUNT(*)  
FROM Orders  
WHERE Orders.cust_id = Customers.cust_id) AS orders  
FROM Customers  
ORDER BY cust_name; 
cust_name cust_state orders  
Fun4A11 IN 1  
Fun4A11 AZ 1  
Kids Place OH 0  
The Toy Store IL 1  
Village Toys MI 2

这条 SELECT 语句对 Customers 表中每个顾客返回三列：cust_name、cust_state 和 orders。orders 是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。在此例中，该子查询执行了 5次，因为检索出了 5个顾客。

子查询中的 WHERE子句与前面使用的 WHERE子句稍有不同，因为它使用了完全限定列名，而不只是列名（cust_id）。它指定表名和列名（Orders.cust_id和 Customers.cust_id）。下面的 WHERE 子句告诉 SQL，比较 Orders 表中的 cust_id 和当前正从 Customers 表中检索的 cust_id：

WHERE Orders.cust_id = Customers.cust_id

用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法。在这个例子中，有两个 cust_id 列：一个在 Customers 中，另一个在Orders 中。如果不采用完全限定列名，DBMS会认为要对 Orders 表中的 cust_id 自身进行比较。因为

SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id

总是返回 Orders 表中订单的总数，而这个结果不是我们想要的：

SELECT cust_name,  
cust_state,  
(SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders  
FROM Customers  
ORDER BY cust_name;
cust_name cust_state orders
Fun4A11 IN 5
Fun4A11 AZ 5
Kids Place OH 5
The Toy Store IL 5
Village Toys MI 5

虽然子查询在构造这种 SELECT 语句时极有用，但必须注意限制有歧义的列。

注意：完全限定列名

你已经看到了为什么要使用完全限定列名，没有具体指定就会返回错误结果，因为 DBMS会误解你的意思。有时候，由于出现冲突列名而导致的歧义性，会引起 DBMS抛出错误信息。例如，WHERE 或 ORDERBY 子句指定的某个列名可能会出现在多个表中。好的做法是，如果在SELECT 语句中操作多个表，就应使用完全限定列名来避免歧义。

提示：不止一种解决方案

正如这一课前面所述，虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效方法。在后面两课学习 JOIN 时，我们还会遇到这个例子。

11.4 小结

这一课学习了什么是子查询，如何使用它们。子查询常用于 WHERE 子句的 IN 操作符中，以及用来填充计算列。我们举了这两种操作类型的例子。

11.5 挑战题

  1. 使用子查询，返回购买价格为 10 美元或以上产品的顾客列表。你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

  2. 你想知道订购 BR01 产品的日期。编写 SQL语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 BR01 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date）。按订购日期对结果进行排序。

  3. 现在我们让它更具挑战性。在上一个挑战题，返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email）。提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

  4. 我们需要一个顾客ID列表，其中包含他们已订购的总金额。编写SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数。将结果按金额从大到小排序。提示：你之前已经使用 SUM()计算订单总数。

  5. 再来。编写SQL语句，从Products表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）。