SQL必知必会中的联合子查询详细讲解

使用子查询

这一课介绍什么是子查询，如何使用它们。

11.1 子查询

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

查询（query）

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

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

说明：MySQL支持

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

MySQL的早期版本不支持子查询。

11.2利用子查询进行过滤

本书所有课中使用的数据库表都是关系表（关于每个表及关系的描述， 请参阅附录A ）。订单存储在两个表中。每个订单包含订单编号、客户ID、

订单日期，在Orders表中存储为一行。各订单的物品存储在相关的 Orderitems表中。Orders表不存储顾客信息，只存储顾客ID。顾客的 实际信息存储在Customers表中。

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

⑴检索包含物品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 o「dejnumlN (20007,20008);

输出▼ cust_id 1000000004

1000000005

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

输入▼

SELECT cust_id

FROM Orders

WHERE。图dejnum IN (SELECT order_num

FROM Orderltems

WHERE p「od_id = 'RGAN01');

输出▼ cust_id 1000000004

1000000005

分析▼

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

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

SELECT order_num FROM orderi terns WHERE prod_^_id=^, RGAN01'

此查询返回两个订单号：20007和20008_o然后，这两个值以IN操作 符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询 变成：

SELECTcust_idFROM o「de「sWHEREo,dejnum 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

(SELECT cust_id

FROM Orders

WHERE order_num IN

WHERE prod_id = ^TRGANO1^T));

输出▼

cust_name                                                             cust_contact

Fun4A】】                           DeniseL. Stephens

TheToyStone                                                         Kim Howard

分析▼

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

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

注意：只能是单列

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

注意：子查询和性能

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

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

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

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

⑴从Customers表中检索顾客列表；

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

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

输入▼

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

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

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

WHERE Orders.cust_id = Customers.cust_id

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

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;

输出▼

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

注意：完全限定列名

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

提示：不止一种解决方案

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

11.4小结

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