十一、使用子查询

工程师加油💪🏻.png

1.1子查询

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

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

1.2利用子查询进行过滤

• 举例：
  三张表：Orders、OrderItems、Customers
  ①：订单存储在两个表中。Orders表、OrderItems表
  ②：在 Orders 表中：每个订单的 编号、顾客 ID、订单日期。
  ③：在相关的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

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

解释： 在 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 Orders 
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));