第11课 SQL入门之使用子查询

本专栏目录

第1课 SQL入门之了解SQL
第2课 SQL入门之检索数据
第3课 SQL入门之排序检索数据
第4课 SQL入门之过滤数据
第5课 SQL入门之高级数据过滤
第6课 SQL入门之用通配符进行过滤
第7课 SQL入门之创建计算字段
第8课 SQL入门之使用数据处理函数
第9课 SQL入门之汇总数据
第10课 SQL入门之分组数据
第11课 SQL入门之使用子查询
第12课 SQL入门之联结表
第13课 SQL入门之创建高级联结
第14课 SQL入门之组合查询
第15课 SQL入门之插入数据
第16课 SQL入门之更新和删除数据
第17课 SQL入门之创建和操纵表
第18课 SQL入门之使用视图
第19课 SQL入门之使用存储过程
第20课 SQL入门之管理事务处理
第21课 SQL入门之使用游标
第22课 SQL入门之高级SQL特性
附录A SQL入门之SQL样例表脚本
附录B SQL入门之SQL流行的应用程序
附录C SQL入门之SQL语句的语法
附录D SQL入门之SQL数据类型
附录E SQL入门之SQL保留字

---

11.1 子查询

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

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

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

说明：MySQL支持

如果使用MySQL，应该知道对子查询的支持是从4.1版本引入的。MySQL的早期版本不支持子查询。

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'; 

输出▼

order_num
20007
20008

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

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

输出▼

cust_id
1000000004
1000000005

现在，结合这两个查询，把第一个查询（返回订单号的那一个）变为子查询。请看下面的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 ('1000000004','1000000005'); 

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

SELECT cust_name, cust_contact 
FROM Customers 
WHERE cust_id IN (SELECT cust_id
                   FROM Order
                   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
Fun4All	IN	1
Fun4All	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
Fun4All	IN	5
Fun4All	AZ	5
Kids Place	OH	5
The Toy Store	IL	5
Village Toys	MI	5

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

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

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

---

上一篇：第10课 SQL入门之分组数据
下一篇：第12课 SQL入门之联结表