关于多行子查询结果集中ALL、ANY的用法问题

相关代码：

SELECT e.sal,e.ename FROM t_emp e 
JOIN (SELECT sal FROM t_emp WHERE ename IN ("FORD","MARTIN")) t 
WHERE e.sal> ALL t.sal;

相关代码：

SELECT sal,ename FROM t_emp 
WHERE sal>=ANY (SELECT sal FROM t_emp WHERE ename IN ("FORD","MARTIN"))

下面的是视频中演示ALL、ANY用的WHERE子查询方法，可以运行。

然后我自己写了上面的方法想通过FROM表连接的子查询试一试ALL、ANY的用法，结果是报错，是ALL、ANY 不能这么用吗，还是我有低级错误脑抽了没发现呢。如果是用法有误，那么正确的用法是什么呢？

慕仔8113846 2021-01-06

源自：数据库的高级查询 4-3 单行和多行子查询语法规则（重点）

收起

2回答

好帮手慕小轩 2021-01-07 13:54:46

同学，你好！同学写的select语句不正确，同学使用的是表连接，where后的可以理解为表链接的条件，t.sal是具体的值，所以不能使用ALL，ALL和ANY后面应该是结果集，同学使用课程中讲解的方法实现即可

加油，祝学习愉快~~~

收起回答

提问者慕仔8113846 #1

也就是说，类似的写法只能用WHERE子查询而不能用更有效率的FROM 子查询吗。
而如果想有相同的效果，则通过在子查询中SELECT语句后用MAX 或MIN语句来达到相似的效果可以吗

2021-01-07 16:14:06
好帮手慕小轩回复提问者慕仔8113846 #2

同学，你好！
1. 是的，只能用where子查询
2. SELECT语句后用MAX或MIN只能获取最大值和最小值，功能不一样，所以不能实现相似的效果
祝学习愉快~~~

2021-01-07 17:16:24
科伟 #3

一开始我也和你一样的想法，然后看了老师给你的回答，我突然理解了。和你分享一下我的想法：

ALL、ANY 这两个关键字后面输入的参数，都是一个范围。当使用表连接时，我们指定的连接条件，是左表的一个值比对右表的一个值，符合条件才加入结果集，比如
SELECT e.ename
FROM t_emp e JOIN (SELECT sal FROM t_emp WHERE ename IN ('FORD','MARTIN')) t
ON e.sal > ALL( t.sal );
上例中，表连接的条件，应该理解为 e 表 sal 字段的一个值，是否大于 t 表 sal 字段的一个值。
所以上例连接条件中的 t.sal 表示的是一个值，而不是一个集合，所以会报错。
ALL、ANY 关键字后面的参数要求是一个集合。当使用课程视频里的方法：
ALL( SELECT sal FROM t_emp WHERE ename IN ('FORD','MARTIN') ) 时，子查询返回的就是一个结果集

如果非得使用表连接的形式得到想要的结果，我尝试了使用以下语句：
SELECT e1.ename
FROM t_emp e1 JOIN t_emp e2
ON e1.ename=e2.ename
AND e1.sal > ALL(SELECT sal FROM t_emp WHERE ename IN ('FORD','MARTIN'));
但是 ALL 关键字还是写在了连接条件里，所以其实和写在 WHERE 子句里的子查询是一样的低效率。每次判断都需要进行一次子查询。

我发现，使用以上表连接形式写的查询语句，如果将ALL的参数改为具体的数字集合，也会报错：
SELECT e1.ename
FROM t_emp e1 JOIN t_emp e2
ON e1.ename=e2.ename
AND e1.sal > ALL(1250,3000);
报错：1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1250,3000)' at line 3

即使是在 WHERE 子句中，也会报错。所以 ALL 接收的参数，应该要求是一个子查询返回的结果集，即使这个结果集里只有一个参数也不会报错，如：
SELECT ename
FROM t_emp
WHERE sal > ALL(SELECT sal FROM t_emp WHERE ename='FORD');
还是可以正常得到结果。

所以我的结论：ALL、ANY 关键字后面输入的参数，必须是子查询返回的结果集，无论这个结果集里有多少个元素。
类比理解：就像Python里的元组，哪怕元组里面什么都没有，数据类型也是元组。而单个值就会有具体的数据类型，比如数字、字符串、时间等。ALL、ANY 关键字能够接收的参数，是子查询返回的 '元组'，如果输入具体的值就会报错。

不知我的理解是否正确，请老师指正 ^_^

2021-02-08 08:52:58

点开查看后面1条评论

CharlieJJJJ 2021-01-06 22:33:10

据我了解ALL 和 ANY的完整语法是 ALL（）和ANY（），所以说你这样的写法是错的，后面应该需要加括号。

收起回答

CharlieJJJJ #1

然后把子查询语句写在括号内

2021-01-06 22:34:36
提问者慕仔8113846 #2

如果把子查询语句写在括号内，那就还是下面哪种WHERE子查询。我想请问类似的用法可以通用FROM子查询使用吗，我上面的写法改成ALL(t.sal)还是报错。

2021-01-07 09:32:16