MySQL子查询的用法及原理

1.4. 子查询

子查询：

任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。子查询的层级用括号实现。

子查询案例：

在 products 中，找到所有比生菜（id = 3）价格高的

SELECT *
FROM products
WHERE unit_price > (
  -- 先查询出生菜的价格
    SELECT unit_price
    FROM products
    WHERE product_id = 3
)

MySQL执行时会先执行括号内的子查询（内查询），将获得的生菜价格作为结果返回给外查询，子查询不仅可用在 WHERE …… 中，也可用在 SELECT …… 或 FROM …… 等子句中。

在 sql_hr 库 employees 表里，选择所有工资超过平均工资的雇员

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

1.4.1.1. IN在子查询中的应用

IN在子查询中的应用案例：

在 sql_store 库 products 表中找出从未被订购过的产品

思路：
1. 先查询order_items 表里有所有产品被订购的记录，用 DISTINCT 去重，得到所有被订购过的产品列表
2. 不在这列表里（NOT IN 的使用）的产品即为从未被订购过的产品

SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
)

上一个案例是子查询返回一个值（平均工资），这个案例是返回一列数据（被订购过的产品id列表），也可以用子查询返回一个多列的表。

1.4.1.2. 子查询和连接的关系

子查询（Subquery）是将一张表的查询结果作为另一张表的查询依据并层层嵌套，也可以先将这些表连接（Join）合并成一个包含所需全部信息的详情表再直接从详情表里筛选查询。两种方法一般是可互换的，具体用哪一种取决于 效率/性能（Performance） 和 可读性（readability）。

子查询VS连接：

子查询：

先用子查询查出有过发票记录的顾客名单，作为筛选依据

SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
)

连接：

用顾客表 LEFT JOIN 发票记录表，再直接从这个合并详情表中筛选出没有发票记录的顾客

SELECT DISTINCT client_id, name …… 
-- 不能SELECT DISTINCT *
FROM clients
LEFT JOIN invoices USING (client_id)
-- 注意不能用内链接，否则没有发票记录的顾客，直接就被筛掉了
WHERE invoice_id IS NULL

可以看出来子查询可读性更好，但有时子查询会过于复杂（嵌套层数过多），用连接表更好。在选择方法时，可读性是很重要的考虑因素。

分别用子查询方法和连接表方法实现并比较可读性：

在 sql_store 中，选出买过生菜（id = 3）的顾客的id、姓氏和名字

1. 完全子查询

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询2：从订单表中买过生菜的顾客
    SELECT customer_id 
    FROM orders
    WHERE order_id IN (  
        -- 子查询1：从订单项目表中找出包含生菜的订单
        SELECT DISTINCT order_id
        FROM order_items
        WHERE product_id = 3
    )
)

2. 混合：子查询 + 表连接

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询：哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    JOIN order_items USING (order_id)  
    -- 表连接：合并订单和订单项目表得到 订单详情表
    WHERE product_id = 3
)

法3. 完全表连接

连接合并3张表（顾客表、订单表和订单项目表）得到 带顾客信息的订单详情表，该合并表包含所需的所有信息，可直接在合并表中用WHERE筛选买过生菜的顾客（注意 DISTINCT 关键字的运用）。

SELECT DISTINCT customer_id, first_name, last_name
FROM customers
JOIN orders USING (customer_id)
JOIN order_items USING (order_id)
WHERE product_id = 3

这个案例中，先将所需信息所在的几张表全部连接合并成一张大表再来查询筛选明显比层层嵌套的多重子查询更加清晰明了。

1.4.1.3. ALL关键字

> (MAX (……)) 和 > ALL(……) 等效可互换

“比这里面最大的还大” = “比这里面的所有的都大”

ALL和MAX：

sql_invoicing 库中，选出金额大于3号顾客所有发票金额（或3号顾客最大发票金额） 的发票

1.用MAX关键字

SELECT *
FROM invoices
WHERE invoice_total > ( -- 返回来一个最大值 需要比这个值大
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

2.用ALL关键字

SELECT *
FROM invoices
WHERE invoice_total > ALL ( -- 返回来一组数据 需要比这组数据的值都大
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

1.4.1.4. ANY关键字

> ANY/SOME (……) 与 > (MIN (……)) 等效

“比这组数据中任意一个数大，就大于最小值”=“比这组数据最小值大”

= ANY/SOME (……) 与 IN (……) 等效

ANY关键字实例：

1. > ANY (……) 与 > (MIN (……)) 等效的例子：
sql_invoicing 库中，选出金额大于3号顾客任何发票金额（或最小发票金额） 的发票

SELECT *
FROM invoices
WHERE invoice_total > ANY ( -- 返回一组数据，大于其中一个就成立
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

-- 或

WHERE invoice_total > ( -- 返回一个最小值
    SELECT MIN(invoice_total)
    FROM invoices
    WHERE client_id = 3

2. = ANY (……) 与 IN (……) 等效的例子:
选出至少有两次发票记录的顾客

SELECT *
FROM clients
WHERE client_id IN (  -- 或 = ANY ( 
    -- 子查询：有2次以上发票记录的顾客
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

1.4.1.5. 相关子查询

之前都是非关联主/子（外/内）查询，比如子查询先查出整体的某平均值或满足某些条件的一列id，作为主查询的筛选依据，这种子查询与主查询无关，会先一次性得出查询结果再返回给主查询供其使用。

相关联的子查询例子里，子查询要查询的是某员工所在办公室的平均值，子查询是依赖主查询的，注意这种关联查询是在主查询的每一行/每一条记录层面上依次进行的，这一点可以为我们写关联子查询提供线索（注意表别名的使用），另外也正因为这一点，相关子查询会比非关联查询执行起来慢一些。

相关子查询案例：

选出 sql_hr.employees 里那些工资超过他所在办公室平均工资（而不是整体平均工资）的员工。
关键：如何查询目前主查询员工的所在办公室的平均工资而不是整体的平均工资？
思路：给主查询 employees表 设置别名 e，这样在子查询查询平均工资时加上 WHERE office_id = e.office_id 筛选条件即可相关联地查询到目前员工所在地办公室的平均工资

SELECT *
FROM employees e  -- 关键 1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id  -- 关键 2
    -- 【子查询表字段不用加前缀，主查询表的字段要加前缀，以此区分】
)

执行流程：

1. 外部查询选择了所有的员工（employees 表中的所有行）。

2. 对于每一行，执行内部子查询。子查询首先根据当前员工的 office_id 找到与之相同办公室的所有员工，并计算他们的薪水平均值。这个平均值是对办公室内所有员工薪水的聚合。

3. 外部查询中的 WHERE 条件比较了每个员工的薪水与对应办公室的平均薪水。如果员工的薪水高于平均薪水，则该员工的行将包含在结果集中。

在 sql_invoicing 库 invoices 表中，找出高于每位顾客平均发票金额的发票

SELECT *
FROM invoices i
WHERE invoice_total >(
   -- 子查询：目前客户的平均发票额
  SELECT AVG(invoice_total)
  FROM invoices
  WHERE client_id = i.client_id
)

如果，当前的client_id=1，就会计算所有client_id=1的平均发票金额，相当于是一个循环，外层当前第一行数据，获取的就是第一行对应的id，然后子查询会计算与第一行id相等的平均值。

1.4.1.6. SELECT子句的子查询

不仅 WHERE 筛选条件里可以用子查询，SELECT 选择子句和 FROM 来源表子句也能用子查询，简单讲就是，SELECT选择语句是用来确定查询结果包含哪些字段，每个字段都可以是一个表达式，而每个字段表达式里的元素除了可以是原始的列，具体的数值，也同样可以是其它各种子查询的结果。任何子查询都是简单查询的嵌套，没什么新东西，只是多了一个层级而已，由内向外地一层层梳理就很清楚。要特别注意记住以子查询方式实现在SELECT中使用同级字段别名。

SELECT子句的子查询案例：

得到一个有如下列的表格：invoice_id, invoice_total（总发票额）, avarege（总平均发票额）, difference（前两个值的差）

SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    /*不能直接用聚合函数，因为“比较强势”，会压缩聚合结果为一条
    用括号+子查询(SELECT AVG(invoice_total) FROM invoices) 
    将其作为一个数值结果 152.388235 加入主查询语句*/
    invoice_total - (SELECT invoice_average) AS difference
    /*SELECT表达式里要用原列名，不能直接用别名invoice_average
    要用列别名的话用子查询（SELECT 同级的列别名）*/
FROM invoices

得到一个有如下列的表格：client_id, name, total_sales（各个客户的发票总额）, average（总平均发票额）, difference（前两个值的差）

SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
    -- 要得到【相关】客户的发票总额，要用相关子查询 WHERE client_id = c.client_id
    (SELECT AVG(invoice_total) FROM invoices) AS average,
    (SELECT total_sales - average) AS difference   
    /* 如前所述，引用同级的字段别名，要加括号和 SELECT，
    和前两行子查询的区别是，引用同级的字段别名不需要说明来源，
    所以没有 FROM …… */
FROM clients c

1.4.1.7. FROM子句的子查询

子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表，即将筛选查询结果作为来源再进行进一步的筛选查询。但注意只有在子查询不太复杂时进行这样的嵌套，否则最好用视图先把子查询结果储存起来再使用。

FROM子句的子查询案例：

将上一节练习里的查询结果当作来源表，查询其中 total_sales 非空的记录

SELECT * 
FROM (
    SELECT 
        client_id,
        name,
        (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
        (SELECT AVG(invoice_total) FROM invoices) AS average,
        (SELECT total_sales - average) AS difference   
    FROM clients c
) AS sales_summury
/* 在FROM中使用子查询，即使用 “派生表” 时，
必须给派生表取个别名（不管用不用），这是硬性要求，不写会报错：
Error Code: 1248. Every derived table（派生表、导出表）
must have its own alias */
WHERE total_sales IS NOT NULL

复杂的子查询再嵌套进 FROM 里会让整个查询看起来过于复杂，上面这个最好是将子查询结果储存为叫 sales_summury 的视图，然后再直接使用该视图作为来源表。

派生表：

当一个子查询位于 FORM 子句中时，这个子查询被称为派生表。

派生表案例：

SELECT *
FROM (
    SELECT last_name,
      COUNT(*) count
    FROM actor
    GROUP BY last_name
  ) t -- 作为数据源：统计名字出现的次数并且名字首字母为A开头的
WHERE t.last_name LIKE 'A%';

//结果
+-----------+-------+
| last_name | count |
+-----------+-------+
| AKROYD    |     3 |
| ALLEN     |     3 |
| ASTAIRE   |     1 |
+-----------+-------+
3 rows in set (0.00 sec)

这是一个派生表，并且它有一个别名 t。派生表必须使用别名，因为 MySQL 规定，任何 FORM 子句中的表必须具有一个名字。派生表不是临时表，必须具有别名。