MySQL(三)：子查询，从零开始学Java编程

子查询的限制

• 子查询的限制是其外部语句必须是以下语句之一：SELECT、INSERT、UPDATE、DELETE、SET或DO

• 另一个限制就是，目前用户不能既在一个子查询中修改一个表，又在同一个表中进行选择，即当外部语句使用了子查询时，子查询里面的字段是不可以直接使用的，要想使用就要再写多一次子查询

使用子查询进行比较

比如

‘a’ = (SELECT column1 FROM t1)

SELECT column1 FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);

//找到t1表中，哪些数据出现了两次

SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

使用ANY、IN和SOME进行子查询

使用ANY

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

//找到t1表中的s1大于t2表中的s1列中任意一个值的数据

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

再比如

这是t5表

这是t6表

SELECT t5.id,t5.s1 FROM t5 WHERE t5.s1 > ANY(SELECT t6.s1 FROM t6);

结果为

出现这种结果是因为，t5表中除了s1=1之外，其他都可以在t6表中的s1列找到比其更小的值（表达式会返回TRUE，即t5.s1 > ANY(…)返回TRUE）,但注意，如果t6中的s1列存在NULL的话，那么表达式会返回UNKNOWN。

使用IN

IN其实是"=ANY"的别名，因此，下面这两个SQL是一样的

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

SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);

使用SOME

SOME的话，其实就是ANY的别名，下面两条SQL是一样的效果，所以SOME几乎没什么人用，但SOME也是很有意义的，也就是从理解上，比如s1 > ANY(…)，从英语上看，应该是s1大于任何一个，但实际上在SQL中只是大于一部分（至少一个），而使用SOME，s1 > SOME(…)，从英语上看，是s1大于一些，与SQL表达的意思更为贴近。

SELECT s1 FROM t1 WHERE s1 <比较符> ANY(SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <比较符> SOME(SELECT s1 FROM t2);

使用ALL进行子查询

使用ALL进行子查询的语法

operand comparison_operator ALL(subquery)

词语ALL必须与比较操作符一起使用，ALL的意思，对于子查询返回的列中的所有值进行比较，如果都为TRUE，返

回TRUE。

比如还是上面的t5和t6

SELECT t5.id,t5.s1 FROM t5 WHERE t5.s1 > ALL(SELECT t6.s1 FROM t6);

可以看到什么都没有返回，因为t5中的s1最大的为5，t6中的s1最大也为5，所以，t5的s1肯定不会出现比t6的s1列中所有值大的数，也就是表达式返回的肯定为FALSE。

同理，如果出现跟NULL进行比较的话，也是会返回UNKNOWN，如果ALL(…)里面的整张表是一张空表，那么返回的最终结果是为TRUE。

//如果t2表是一张空表，那么最终比较返回的结果为TRUE

SELECT * FROM t1 WHERE 1 > ALL(SELECT s1 FROM t2)

//当t2表是一张空表时，最终比较返回的结果为NULL

SELECT * FROM t1 WHERE 1 > (SELECT S1 FROM t2)

//当t2表是一张空表时，最终比较返回的结果为NULL

SELECT * FROM t1 WHERE 1 > ALL(SELECT MAX(s1) FROM t2)

//因为使用MAX了之后，只剩下一个值，比较的话跟下面的一致

SELECT * FROM t1 WHERE 1 > (SELECT MAX(s1) FROM t2)

**NOT IN是<>AL

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】

开源分享完整内容戳这里

L的别名**，下面的两条SQL是相同的

SELECT s1 FROM t1 WHERE s1 <> ALL(SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

独立子查询

子查询可以按两种方式进行分类，若按照期望值的数量（这里的期望值是外部查询希望的，也就是需要子查询返回的值个数），可以将子查询分为标量子查询和多值子查询（标量就是前面我们提到的子查询返回的结果只有一个，多量的话就是多个，其实就是多列或多行）；若按查询对外部查询的依赖可以分为独立子查询（self-contained subquery）和相关子查询（correlated subquery）。标量子查询和多值子查询可以是独立子查询，也可以是相关子查询。

独立查询是指不依赖外部查询而运行的子查询，与相关子查询相比，独立查询更便于SQL语句的调试。

标量子查询可以出现在查询中希望产生标量值的任何地方，而多值子查询可以出现在查询中希望产生多值集合的任何地方，只要标量子查询返回的是单个值或者NULL，就说明这个标量子查询是合理的，但如果返回的是多个值，那么数据库是会抛错的。

举个栗子

//这两条都是合理的标量子查询

//外部查询希望的是一个标量，子查询也是标量子查询

SELECT ‘a’ = (SELECT ‘a’) AS t;

SELECT ‘a’ = (SELECT NULL) AS t;

//而下面的子查询会抛出异常

//因为外部查询希望的是一个标量，而子查询是多值子查询，所以会报错

//union和Union all 其实是将两个select查询的结果集合成一个表返回

SELECT ‘a’ = (SELECT ‘a’ UNION ALL SELECT ‘b’) AS t;

拓展一下

其实独立子查询在官方文档中是没有介绍的，这是因为很多时候，独立子查询会经过Mysql的引擎自带的优化变为相关子查询，比如下面的sql

这里先介绍一下EXISTS

EXISTS代表的意思为存在，在子查询用EXISTS时，只会返回TRUE或者FALSE

//很明显看到，下面这条SQL是独立子查询，而且是多值子查询

SELECT … FROM t1 WHERE t1.column1 IN (SELECT b FROM t2);

//但其实，MySQL的优化器会将其变为下面这种形式

//可以看到，子查询变为了相关子查询

SELECT … FROM t1 WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.b = t2.a);

相关子查询

相关子查询是指引用了外部查询列的子查询（通常相关子查询用外部查询的列来进行自身过滤数据），也就是跟外部的查询产生了联系，而不是像独立子查询一样，自己查自己的。

下面举个栗子

比如现在有一个需求，要查询每个员工负责的最大订单日期的订单

错误的SQL

SELECT orderid,customerid,employeeid,orderdate,requireddate

FROM orders

WHERE orderdate IN (SELECT MAX(orderdate) FROM orders GROUP BY employeeid);

分析一下这条SQL，一样按照之前SQL的执行流程来进行分析

首先执行FROM，根据orders表产生了VT1虚拟表，然后到WHERE进行过滤，子查询是一个多值子查询，得到的结果是，每个员工负责处理订单的最大日期表，然后判断orderdate是否再最大日期表中。

这很明显是错误的，因为这样是会返回不是最大日期的订单信息，因为根本没有将员工进行匹配，只是单纯的将orderdate判断是否在每个员工的最大日期表中，可能会存在一个员工的最大日期为2021/02/01，另一个为2021/03/28，但这个员工还有一个订单的日期为2021/02/01，那么后面的这个2021/02/01也会成功匹配上。

解决这个问题的方法就是使用关联子查询

SELECT orderid,customerid,employeeid,orderdate,requireddate

FROM orders AS a

WHERE orderdate = (SELECT MAX(orderdate) FROM orders AS b

WHERE b.employeeid = a.employeeid;

这种关联子查询就不再是像上面一样，子查询自己查自己的了，首先执行FROM，得到虚拟表VT1，然后执行WHERE过滤，这里执行子查询，注意这里子查询，先将当前的a.employeeid赋值上去，然后产生了一张虚拟表，然后WHERE orderdate进行匹配，然后到下一层的a.employeeid再赋值上去，然后产生了另一张虚拟表，然后再匹配，这也是为什么子查询会慢的原因，关联查询需要产生很多的派生表。

EXISTS谓词

EXISTS

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

SELECT customerid,companyname FROM customers AS A

WHERE country = “Spain”

AND EXISTS(SELECT * FROM orders AS B WHERE A.customerid = B.customerid)

上面这条SQL说明了EXISTS的用法，相关子查询就如上面所说的，就像遍历一样，把当前的A.customerid放进去匹配，如果相关子查询可以返回行出来，那就EXISTS(…)会返回TRUE，如果返回NULL，也就是查不到数据，就会返回FALSE，会影响WHERE子句的判断。

//将该语句改为IN子查询

SELECT customerid,companyname FROM customers AS A

WHERE country = “Spain”

AND customerid IN (SELECT customerid FROM orders);