【MySQL—SQL编程】子查询

子查询是指在一个SELECT语句中嵌套另一个SELECT语句。

通常来讲，使用子查询的好处如下：

• 子查询允许结构化的查询，这样就可以把一个查询语句的每个部分隔开。
• 子查询提供了另一种方法来执行有些需要复杂的JOIN和UNION来实现的操作。
• 在许多人看来，子查询可读性较高。

一个子查询会返回一个标量（单一值）、一个行、一个列或一个表（一行或多行及一列或多列），这些子查询被称为标量、列、行和表子查询。可返回一个特定种类结果的子查询经常只用于特定的语境中。子查询可以包括普通SELECT可以包括的任何关键词或子句，如DISTINCT、GROUP BY、ORDERBY、LIMIT、JOIN、UNION等。

子查询的限制是其外部语句必须是以下语句之一：SELECT、INSERT、UPDATE、DELETE、SET或DO。还有一个限制是，目前用户不能既在一个子查询中修改一个表，又在同一个表中进行选择，虽然这样的操作可用于普通的DELETE、INSERT、REPLACE和UPDATE语句中，但是对子查询不可以同时进行这样的操作。

常用关键词（ANY/IN/SOME/ALL）

使用ANY、IN、SOME和ALL进行子查询的语法如下：

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)

ANY关键词必须与一个比较操作符一起使用。ANY关键词的意思是“对于子查询返回的列中的任一数值，如果比较结果为TRUE，则返回TRUE”。例如：

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

关键词IN是“= ANY”的别名，关键词SOME是ANY的别名。

ALL关键词也必须与比较操作符一起使用。ALL的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。

独立子查询

独立子查询是不依赖外部查询而运行的子查询。

举个例子，现在有一张订单表，我们需要查询出每月最后实际订单日期发生的订单。在这里，每月最后实际订单的日期可能并不是每月的最后一天。因此，每月最后订单日期需要通过子查询来判断。查询语句如下：

SELECT orderid,customerid,employeeid,orderdate
    FROM orders
    WHERE orderdate IN
        ( SELECT MAX(orderdate)
                FROM orders
                GROUP BY (DATE_FORMAT(orderdate,'%Y%m'))
        )

子查询通过把订单按月分组并返回每个分组中最大的日期结果，这就是每个月最后订单生成的日期。而后外部查询通过子查询获得的日期来取得所有的订单信息。

这个问题看似得到了解决，实际上并没有这么简单。因为在这个数据量并不大的数据库中执行上述SQL语句竟然需要6.08秒，如果我们用EXPLAIN来对语句进行分析，就可以找到问题的所在——独立子查询被转化成了相关子查询。

这个是MySQL优化器对IN子查询优化时存在的一个问题，MySQL优化器对于IN语句的优化是“LAZY”的。对于IN子句，如果不是显式的列表定义，如IN('a','b','c')，那么IN子句都会被转换为EXISTS的相关子查询。

如果子查询和外部查询分别返回M和N行，那么该子查询被扫描为O（N+M*N）而不是O（M+N）。

因此，对于上述问题的SQL语句，MySQL数据库的优化器将其对应地转换为如下的相关子查询：

SELECT orderid,customerid,employeeid,orderdate
    FROM orders AS A
    WHERE EXISTS
        ( SELECT ＊
                FROM orders
                GROUP BY(DATE_FORMAT(orderdate,'%Y%M'))
                HAVING MAX(orderdate)= A.OrderDate
        );

需要注意的是，在大多数情况下，MySQL数据库都将独立子查询转换为相关子查询。

不过对于上述语句，还是有一定的方法可以优化的。注意到慢的原因是独立子查询被转换成相关子查询，而这个相关子查询需要进行多次的分组操作。可以采取另一个方法，再嵌套一层子查询，避免多次的分组操作，语句如下：

SELECT orderid,customerid,employeeid,orderdate
    FROM orders A
    WHERE EXISTS
        ( SELECT ＊ FROM (SELECT MAX(orderdate) AS orderdate
                FROM orders
                GROUP BY (DATE_FORMAT(orderdate,'%Y%M')) ) B
                WHERE A.orderdate = B.orderdate
        );

相关子查询

相关子查询是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算。

举个例子，要查询每个员工最大订单日期的订单，因为一个员工可能有多个订单具有相同的订单日期，所以可能会为每个员工返回多行数据。

在这个例子中，子查询必须关联外部查询，将内部查询的employeeid与外部的employeeid进行匹配：

SELECT orderid,customerid,employeeid,orderdate,requireddate
    FROM orders AS A
    WHERE orderdate =
        ( SELECT MAX(orderdate) FROM orders AS B
                WHERE A.employeeid=B.employeeid);

不过因为执行了大量逻辑IO，该语句运行速度过慢，有什么方式可以优化呢？

首先，我们可以考虑添加一个唯一索引来加快处理速度：

CREATE UNIQUE INDEX idx_empid_od_rd_oid
    ON orders(employeeid,orderdate,requireddate,orderid)

速度得到了一定的提升，然而问题仍然是相关子查询需要与外部子查询的列进行多次比较。

为了避免子查询与外部子查询的多次比较操作，对于相关子查询，有时可以通过派生表来进行重写，对于以上SQL我们可以重写为：

SELECT
        A.orderid,A.customerid,A.employeeid,
        B.orderdate,requireddate
    FROM orders AS A,
    (SELECT employeeid,MAX(orderdate) AS orderdate FROM orders
    GROUP BY employeeid) AS B
    WHERE A.employeeid=B.EmployeeID AND A.OrderDate=B.orderdate;

这时没有了相关子查询的执行过程，逻辑IO大大减少了，同时也能用到索引，执行速度得到了很大提升。

EXISTS谓词

EXISTS是一个非常强大的谓词，它允许数据库高效地检查指定查询是否产生某些行。通常EXISTS的输入是一个子查询，并关联到外部查询，但这不是必须的。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其他谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN。如果子查询的过滤器为某行返回UNKNOWN，则表示该行不返回，因此，这个UNKNOWN被认为是FALSE。

目前在绝大多数的情况下，IN和EXISTS都具有相同的执行计划。但是要注意的是，NOT IN和NOT EXISTS具有非常不同的执行计划。

EXISTS与IN的一个区别体现在对三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，而对于IN，除了TRUE、FALSE值外，还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与使用EXISTS一样，SQL优化器会选择相同的执行计划。

但是输入列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现得非常明显了。输入列表中包含NULL值时，IN总是返回TRUE和UNKNOWN，因此NOT IN总是返回NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。而对于NOT EXISTS，其总是返回TRUE和FALSE。

个人心得：对于“非存在型”问题，使用NOT EXISTS谓词，其余使用关联子查询的场景应该尽可能改写成关联派生表的方式，并添加合适的索引。