in 是确定集合
select deptno, dname
from dept
where deptno in (10,20)
/
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
all 是查询还可以是子查询
用于查询. 如:
select deptno from emp; select all deptno from emp; select distinct deptno from emp;
DEPTNO DEPTNO DEPTNO
---------- ---------- ----------
20 20 30
30 30 20
30 30 10
20 20
30 30
30 30
10 10
20 20
10 10
30 30
20 20
30 30
20 20
10 10
其中deptno前省略了all. deptno前可以加 all | distinct
all 是所有记录
distinct 是不可重复的记录
用于子查询. 如:
select ename, deptno from emp where deptno >= all(select deptno from dept where deptno < 40);
ENAME DEPTNO
---------- ----------
WARD 30
TURNER 30
ALLEN 30
JAMES 30
BLAKE 30
MARTIN 30
any some 用于子查询
带[any]的嵌套查询和[some]的嵌套查询功能是一样的.
早期的sql仅仅允许使用[any], 后来的版本为了和英语的[any]相区分,
引入了[some], 同时还保留了[any]关键词.
any:
select empno, ename, job, sal
from emp
where sal > any(select sal from emp where job='MANAGER');
带any的查询过程等价于2步的执行过程.
1. 先执行 2. 再执行
select sal from emp where job='MANAGER'; select empno, ename, job, sal from emp where sal > 2975 or sal > 2850 or sal > 2450;
SAL EMPNO ENAME JOB SAL
---------- ---------- ---------- --------- ----------
2975 7566 JONES MANAGER 2975
2850 7698 BLAKE MANAGER 2850
2450 7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
some:
select empno, ename, job, sal
from emp
where sal = some(select sal from emp where job='MANAGER');
带some的嵌套查询与any的步骤相同:
1. 子查询执行 2. 父查询执行下列语句.
select sal from emp where job='MANAGER'; select empno, ename, job, sal from emp where sal = 2975 or sal = 2850 or sal = 2450;
SAL EMPNO ENAME JOB SAL
---------- ---------- ---------- --------- ----------
2975 7566 JONES MANAGER 2975
2850 7698 BLAKE MANAGER 2850
2450 7782 CLARK MANAGER 2450
阅读(380) | 评论(0) | 转发(0) |