12.1 子查询
- select语句是SQL的查询。迄今为止我们所看到的所有select语句都是简单查询，即从单个数据库表中检索数据的单条语句。
- 查询 任何SQL语句都是查询。但此术语一般指select语句。
- SQL还允许创建子查询，即嵌套在其他查询中的查询。
12.2 利用子查询进行过滤
- 本书所有章中使用的数据表都是关系表。订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储 客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。
- 现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？下面列出具体的步骤。
- （1）检索包含物品TNT2的所有订单的编号。
- （2）检索具有前一步骤列出的订单编号的所有客户的ID。
- （3）检索前一步骤返回的所有客户ID的客户信息。
- 上述每个步骤都可以单独作为一个查询来执行。可以把一条select语句返回的结果用于另一个select语句的where子句。
- 也可以使用子查询来把3个查询组合成一条语句。
- 第一条select语句的含义很明确，对于prod_id为TN2的所有订单物品，它检索其order_num列。输出列出两个包含此物品的订单：
select order_num
from orderitems
where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
- 下一步，查询具有订单20005和20007的客户ID。利用介绍的IN子句，编写如下的select语句：
select cust_id
from orders
where order_num IN(20005,20007);
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
- 现在，把第一个查询（返回订单号的那一个）变为子查询组合两个查询。请看下面的select语句：
select cust_id
from orders
where order_num IN (select order_num
from orderitems
where prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
- 在select语句中，子查询总是从内向外处理。在处理上面的select语句时，MySQL实际上执行了两个操作。
- 首先，它执行下面的查询：
select order_num from orderitems where prod_id = 'TNT2'
- 此查询返回两个订单号：20005和20007.然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的where子句。外部查询变成：
select cust_id from orders where order_num IN(20005,20007)
- 可以看到，输出是正确的并且与前面硬编码where子句所返回的值相同。
- 格式化SQL 包含子查询的select语句难以阅读和调式，特别是它们较为复杂时更是如此。如上所示把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。
- 现在得到了订购物品TNT2的所有客户的ID。下一步是检索这些客户ID的客户信息。检索两列的SQL语句为：
select cust_name, cust_contact
from customers
where cust_id IN (10001,10004);
- 可以把其中的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 = 'TNT2'
)
);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
- 为了执行上述select语句，MySQL实际上必须执行3条select语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的where子句。外面的子查询返回客户的ID列表，此客户ID列表 用于最外层的where子句。最外层查询确实返回所需的数据。
- 可见，在where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过实际使用时由于性能的限制，不能嵌套太多的子查询。
- 列必须匹配 在where子句中使用子查询，应该保证select语句具有与where子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。
- 虽然子查询一般与IN操作符结合使用，但也可以用于测试等于(=)、不等于(<>)等。
- 子查询和性能 这里给出的代码有效并获得所需的结果。但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。
12.3 作为计算字段使用子查询
- 使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
- 为了执行这个操作，遵循下面的步骤。
- （1）从customers表中检索客户列表
- （2）对于检索出的每个客户，统计其在orders表中的订单数目。
- 可使用select count(*)对表中的行进行计数，并且通过提供一条where子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。例如，下面的代码对客户10001的订单进行计数：
select count(*) as orders
from orders
where cust_id = 10001;
- 为了对每个客户执行count()计算，应该将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 |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
- 这条select语句对customers表中每个客户返回3列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。
- 子查询中的where子句与前面使用的where子句稍有不同，因为它使用了完全限定列名。下面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id:
where orders.cust_id = customers.cust_id
- 相关子查询 涉及外部查询的子查询。
- 这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法。为什么这样？我们来看看如果不使用完全限定的列名会发生什么情况：
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 |
+----------------+------------+--------+
- 显然，返回的结果不正确，那么，为什么会这样呢？有两个cust_id列，一个在customers中，另一个在orders中，需要比较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限 定列名，MySQL将假定你是对orders表中的cust_id进行自身比较。而select count(*) from orders where cust_id = cust_id;总是返回orders表中的订单总数(因为MySQL查看每个订单的cust_id是否与本身匹配，当然，它们总是匹配的)。
- 虽然子查询在构造这种select语句时有用，但必须注意限制有歧义性的列名。
- 不止一种解决方案 正如前面所述，虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最优效的方 法。在后面的章节中我们还要遇到这个例子。
- 逐渐增加子查询来建立查询 用子查询测试和调式查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后的大量时间，并且极大地提高了查询一开始就正常工作的可能性。