我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

MySQL(三)：子查询

作者：我家小花儿 | 2024-07-23 21:10:20

踩

MySQL(三)：子查询

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
比如

‘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是<>ALL的别名，下面的两条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);

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);