SQL中的ANY、SOME、ALL关键字

最新推荐文章于 2022-11-27 20:42:33 发布

lsls22

最新推荐文章于 2022-11-27 20:42:33 发布

阅读量1.1k

点赞数

分类专栏： Database 文章标签： sql manager

Database 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

带any嵌套查询
select emp.empno,emp.ename,emp.sal from scott.emp where sal>any(select sal from scott.emp where job='manager');
等价于
select sal from scott.emp where job='manager' 结果为 sal：2975 2850 2450
select emp.empno,emp.ename,emp.sal from scott.emp where sal>2975 or sal>2850 or sal>2450

带some嵌套查询
select emp.empno,emp.ename,emp.sal from scott.emp where sal=some(select sal from scott.emp where job='manager');
等价于
select sal from scott.emp where job='manager' 结果为 sal：2975 2850 2450
select emp.empno,emp.ename,emp.sal from scott.emp where sal=2975 or sal=2850 or sal=2450
注：any与some是等价的

带all嵌套查询
select emp.empno,emp.ename,emp.sal from scott.emp where sal>all(select sal from scott.emp where job='manager');
等价于
select sal from scott.emp where job='manager' 结果为 sal：2975 2850 2450
select emp.empno,emp.ename,emp.sal from scott.emp where sal>2975 and sal>2850 and sal>2450