MySQL 官方手册 8.0 Reference Manual - Subqueries with ANY, IN, or SOME
1.ANY
与ALL
对比
operand comparison_operator ANY (subquery)operand IN (subquery)operand comparison_operator SOME (subquery)
comparison_operator
包含:= > < >= <= <> !=
ANY
和SOME
是任意一个,是存在性。或者数学里的能成立
。也就是>ANY
大于最小的即可,<ANY
小于最大的即可。
ALL
是所有,是全部都满足。或者数学里的恒成立
。也就是>ALL
必须大于最大的,<ALL
必须小于最小的。
ANY
select * from student where 班级=’01’ and age > any (select age from student where 班级=’02’);# 就是说,查询出01班中,年龄大于 02班任意一个 的 同学# 相当于select * from student where 班级=’01’ and age > (select min(age) from student where 班级=’02’);
ALL
select * from student where 班级=’01’ and age > all (select age from student where 班级=’02’);# 就是说,查询出01班中,年龄大于 02班所有人的同学# 相当于select * from student where 班级=’01’ and age > (select max(age) from student where 班级=’02’);
《SQL完全手册》书中(p155),作者在解释“ANY"的用法中提到:
where x < any (select y ....)
不能读成
where x is less than any select y ......
而该读成
where,for some y, x is less than y .......
例:列出销售人员中不管理销售点的所有人员的名字和年龄
select name,age from salesreps
错误的筛选字句为
where empl_num <> any(select mgr from offices)
正确的筛选子句为
where not (emp_num=any(select mgr from offices))
在子查询中:IN
是=ANY
的别名
IN
和= ANY
表达式列表一起使用时不是同义词。IN
可以接受表达式列表,但= ANY
不能。
NOT IN
的别名不是<> ANY
,而是<> ALL
。
SOME
的别名是ANY
。
2.EXISTS
与IN
对比
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
exists
关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么exists
的结果为true
,此时外层的查询语句将进行查询;如果子查询没有返回任何行,那么exists
的结果为false
,此时外层语句将不进行查询。
传统上,EXISTS
子查询以 开头SELECT *
,但也可以以SELECT 5
或SELECT column1
或任何开头 。MySQL 会忽略SELECT
此类子查询中的列表,因此没有区别。
exists执行流程:
先遍历外表t1,然后每次遍历的时候,在检查内表t2是否符合匹配条件。即子查询是否为true。其中子查询可以使用索引,外查询全表扫描
相当于:遍历外表,然后拿着外表的id去子查询,根据数据库索引匹配查找。
因此,外查询越小,循环次数越少。但是子查询表大小都有索引优化查询速度。
IN执行流程:
先执行子查询(仅执行一次,然后把结果集存在临时文件中),然后再与外查询匹配。其中外查询与子查询都可以使用索引
相当于:遍历外表,然后拿着外表的id去子查询的临时文件中,内存中遍历匹配查找。
因此,子查询越小,在内存里查询越快,子查询大的时候肯定不如数据库索引查询速度快。
结论:外查询表小,用EXISTS;子查询表小,用IN。
3. using
关联查询的时候,用于替换 ON关键字,ON a.uid=b.uid
等价于using(uid)