使用子查询

本章介绍什么是子查询以及如何使用它们。

子查询

SELECT语句是SQL的查询，目前我们用的所有SELECT语句都是从单个数据库表中检索数据的单条简单语句。

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

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤

检索出订购物品TNT2的所有客户的步骤：

检索包含物品TNT2的所有订单编号。检索具有前一步列出的订单编号的所有客户的ID。检索前一步列出的客户ID的客户信息。

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

步骤1：检索prod_id为TNT2的order_num值

mysql> SELECT order_num-> FROM orderitems-> WHERE prod_id = 'TNT2';+-----------+| order_num |+-----------+|20005 ||20007 |+-----------+2 rows in set (0.02 sec)

步骤2：查询具有订单20005和20007的客户ID

mysql> SELECT cust_id-> FROM orders-> WHERE order_num IN (20005,20007);+---------+| cust_id |+---------+| 10001 || 10004 |+---------+2 rows in set (0.01 sec)

利用子查询合并步骤1和2：

mysql> SELECT cust_id-> FROM orders-> WHERE order_num IN (SELECT order_num-> FROM orderitems-> WHERE prod_id = 'TNT2');+---------+| cust_id |+---------+| 10001 || 10004 |+---------+2 rows in set (0.01 sec)

在SELECT语句中，子查询总是从内向外处理。

步骤3：检索这些客户ID的客户信息：

mysql> SELECT cust_name, cust_contact-> FROM customers-> WHERE cust_id IN (10001,10004);+----------------+--------------+| cust_name| cust_contact |+----------------+--------------+| Coyote Inc. | Y Lee || Yosemite Place | Y Sam |+----------------+--------------+2 rows in set (0.01 sec)

利用子查询合并步骤1、2和3：

mysql> 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 = 'TNT2'));+----------------+--------------+| cust_name| cust_contact |+----------------+--------------+| Coyote Inc. | Y Lee || Yosemite Place | Y Sam |+----------------+--------------+2 rows in set (0.01 sec)

为了执行此SELECT语句，MySQL实际上必须执行3条SELECT语句。最外层查询确实返回所需数据。

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

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于(=)，不等于（<>）等。

格式化SQL包含子查询的SELECT语句难以阅读和调试。我们把子查询分解为多行并且适当的缩进，能极大的简化子查询的使用。列必须匹配使用子查询时，应保证SELECT语句具有与WHERE子句中相同数目的列。子查询和性能由于需要多次查询，使用子查询并不总是执行这种类型的数据检索的最有效的方法。

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。

检索customers表中每个客户的订单总数步骤：

从customers表中检索客户列表。对于检索出的每个客户，统计其在orders表中的订单数目。

可使用SELECT COUNT(*)对表中的行进行计数，可通过WHERE来过滤客户。

对客户10001的订单进行计数：

mysql> SELECT COUNT(*) AS orders-> FROM orders-> WHERE cust_id = 10001;+--------+| orders |+--------+|2 |+--------+1 row in set (0.00 sec)

为对每个客户进行COUNT(*)计算，应将COUNT(*)作为子查询：

mysql> 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 |+----------------+------------+--------+| Coyote Inc. | MI |2 || E Fudd | IL |1 || Mouse House | OH |0 || Wascals | IN |1 || Yosemite Place | AZ |1 |+----------------+------------+--------+5 rows in set (0.01 sec)

此SELECT语句对customers表中每个客户返回3列：cust_name、cust_state和orders。orders是一个计算字段，它由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。

子查询中的WHERE子句使用了完全限定列名（MySQL必知必会——第四章检索数据），这告诉SQL比较orders表中的cust_id和当前的customers表中检索的cust_id。

相关子查询（correlated subquery）涉及外部查询的子查询。

这种子查询称为相关子查询。任何时候只有列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

不使用完全限定列名：

mysql> 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 |+----------------+------------+--------+| Coyote Inc. | MI |5 || E Fudd | IL |5 || Mouse House | OH |5 || Wascals | IN |5 || Yosemite Place | AZ |5 |+----------------+------------+--------+5 rows in set (0.01 sec)

很显然，不返回预期结果。在子查询中，存在两个cust_id列，一个在customers中，一个在orders中。如果不完全限定列名，MySQL将假定cust_id是orders表中的cust_id，这形成了自身比较。

虽然子查询有时很有用，但必须注意限制有歧义性的列名。

不止一种解决方案虽然样例代码运行良好，但它并非解决这种数据检索的最有效的方法。逐渐增加子查询来建立查询用子查询测试和调试查询很有技巧性。用子查询建立查询最可靠的方法是逐渐进行，由内到外，逐步测试。这样节省了以后找错误的大量时间，并且提高了查询一开始就正常工作的可能。