4.1 相关子查询基本概念

学习目标：

1.理解相关子查询的执行过程

2.掌握相关子查询思考问题的方法（自顶向下，逐步细化）

3.会用相关子查询解决业务需求

4.exists子查询用于where子句

一、认知相关子查询的概念

1.what 子查询依赖于外部主查询提供的条件，不可以单独执行的子查询。

2.why 有些需求用相关子查询解决思路更清晰，但一般情况优先选择效率更高的非相关子查询。

3.where 几乎与非相关子查询相同。

二、相关子查询的执行过程

三、相关子查询应用实例

会用相关子查询解决业务需求

1.查询团购价大于同类商品的商品信息，显示商品标题和团购价。

-- 查询团购价大于同类商品的商品信息，显示商品标题和团购价。
SELECT p.title , p.currentPrice
FROM product p
WHERE p.currentPrice >
# 同类商品的平均团购价
(
       SELECT AVG(p1.currentPrice)
       FROM product p1 
       WHERE p.categoryID = p1.categoryID
     );

2.查询“江汉路”地区的商品信息，要求输出商品标题和商品团购价。

-- 查询“江汉路”地区的商品信息，要求输出商品标题和商品团购价。
SELECT p.title , p.currentPrice
FROM product p
WHERE '江汉路' =  # 子查询 ：主查询提供areaID，获取地区名
        (
            SELECT a.areaName from area a WHERE p.areaID = areaID 
        );

3.查询‘刘亚蒙’订购的商品信息，显示商品标题和团购价

SELECT *
FROM product p
WHERE p.productID in 
            (
                SELECT od.productID FROM ordersdetail od 
                WHERE od.ordersID in
                                (SELECT ordersID FROM orders o
                                 WHERE '刘亚蒙' = 
                   (SELECT customerName FROM customer 
                                        WHERE o.customerID =customerID )
                                )
                );

实例：— 3. 查询‘刘亚蒙’订购的商品信息，显示商品标题和团购价

SELECT p.title , p.currentPrice
FROM product p
WHERE EXISTS ( SELECT * FROM ordersdetail od  -- 相关子查询 
                             WHERE p.productID = productID AND 
                                     EXISTS( SELECT * FROM orders o
                                                         WHERE od.ordersID = ordersID AND
                                                            EXISTS(SELECT * FROM customer c
                                                                        WHERE o.customerID = customerID AND 
                                                                                    c.customerName = '刘亚蒙'
                                                                        )
                                                     )
                            );

小结：

1.相关子查询执行步骤4.执行次数由外部主查询的行数决定。

2.相关子查询不能独立执行，由外及里。

3.exists（子查询）是相关子查询，返回布尔值，用于where子句，如果有多层子查询，每一个子查询都会承上启下，上接父查询提供关键要素，下启exists（子查询），最后一个子查询提供原始条件。