SQL回炉重造10_数据过滤_子查询

子查询

1. 子查询与过滤

• 子查询（subquery）：即嵌套在其他查询中的查询，MySQL4.1之后开始支持子查询

/*
列出订购物品TNT2的所有客户:
(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。
*/

# 不使用子查询
SELECT cust_name, cust_contact
FROM customers as c, orders as o, orderitems as oi
WHERE oi.prod_id = 'TNT2'
AND oi.order_num = o.order_num
AND o.cust_id = c.cust_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'));
                                    
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.03 sec)
2 rows in set (0.00 sec)使用子查询

• 思考：为什么使用嵌套子查询会比多个条件查询快呢？
  • 使用条件查询时，相当于对三个表做了笛卡尔积，然后再从中找出需要的数据，这样做的代价是十分大的；而嵌套子查询相当于先在一个小表内做查询，将查询结果与要查询的表做连结来进行查询，这样相当于把有限的所需要的几行提前查出来再做笛卡尔积，代价是要比把原表做笛卡尔积小很多很多的。所以使用嵌套子查询会相对快一些
• 子查询和性能：使用子查询并不总是检索数据的最有效方法，而且在实际使用时，由于性能限制，是不能嵌套太多子查询的。
  • 列数要匹配：在WHERE中使用子查询，应该保证子查询SELECT与WHERE有相同数目的列。通常子查询返回单个列并于单个列值匹配，但如果需要也可以使用多个列。

2. 作为计算字段使用子查询

• 相关子查询（correlated subquery）:涉及外部查询的子查询，指引用了外部查询中出现的表的列的子查询。这就意味着子查询要依赖于外部查询，不能独立地调用它。在逻辑上，相关子查询会为每个外部行单独计算一次。
  • 需要使用完全限定的列名，以免产生歧义

/*
需要显示customers表中每个客户的订单总数
(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在orders表中的订单数目。
*/
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 |
+----------------+------------+--------+
==> 该子查询会执行五次，因为检索出了五个用户

• 这种方法虽然运行的很nice，但它并不是解决这种数据检索的最有效的方法。
• 逐渐增加子查询来建立查询：由于子查询会使SQL语句变得很复杂，所以可以通过逐层增加的方式来建立子查询，先建立最内层的查询，然后根据结果测试外层查询，确认正常后再嵌入子查询，以此来尽量避免查询不正常工作。