Oracle SQL ANY和ALL语句

Oracle的嵌套子查询可以使用Some,Any和All对子查询中返回的多行结果进行处理。

Some表示满足其中一个的含义，是用or串起来的比较从句。

例如：SELECT * FROM emp WHERE empno = 'hmz' OR dept = '10'

Any也表示满足其中一个的含义，也是用or串起来的比较从句。 Some和Any的区别就是：Some用在“=”的比较关系中。

英文中的否定句中使用any肯定句中使用some，这一点是一样的。

some和any都有“一些”的意思 some用在肯定句中，any用在否定句或疑问句中。 【注1】但表示期望得到肯定回答的疑问句中用some。 【注2】any还有“任何”的意思，some没有

比较：She's too old to do any work．她年事已高，干不了什么活了。

She's old enough to do some work．她已长大了，可以干些活了。

All则表示满足其中所有查询结果的含义，使用and串起来的比较从句。

 

【实例】

查询比部门号为10的员工中的任何一个员工工资都要高的员工的姓名和个人工资。

也就是说只要比部门号为10中工资最少的员工高就满足条件。

SELECT ename,sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10);

这里推荐用any，如果你非要用some也是没有任何问题的，结果是一样的，只是一般来讲some用在“=”的比较从句中。

例如： SELECT ename,sal FROM emp WHERE sal = SOME(SELECT sal FROM emp WHERE deptno = 30) AND deptno NOT IN (SELECT deptno FROM emp WHERE deptno = 30);

含义是找到和30号部门员工的任何一个人工资相同的那些员工。

最后一个关键字all的用法就是要与子查询的每一结果都要匹配。

SELECT ename,sal FROM emp WHERE sal > ALL(select sal from emp where deptno = 20);

上面的SQL语句的意义与前面的就完全不一样了，其意义是找到比部门号为20的员

工的所有员工的工资都要高的员工，也就是比那个工资最高的员工的还要高的员工。

总的来说some和any用法意义是一样的，仅在词法上有不同，都表示对子查询结果

集“或”的比较关系，而all则是对子查询结果集总每一个结果“与”的关系。

SQL> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
14 rows selected

SQL> select * from emp order by deptno;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
14 rows selected

SQL> select ename,deptno,sal from emp where sal > any (select sal from emp where deptno=10); 
ENAME      DEPTNO       SAL
---------- ------ ---------
KING           10   5000.00
FORD           20   3000.00
SCOTT          20   3000.00
JONES          20   2975.00
BLAKE          30   2850.00
CLARK          10   2450.00
ALLEN          30   1600.00
TURNER         30   1500.00
8 rows selected

SQL> select ename,deptno,sal from emp where sal > all (select sal from emp where deptno=10);
ENAME      DEPTNO       SAL
---------- ------ ---------

SQL> select ename,deptno,sal from emp where sal > all (select sal from emp where deptno=20);
ENAME      DEPTNO       SAL
---------- ------ ---------
KING           10   5000.00

SQL> 

 

SQL> select ename,deptno,sal from emp where sal < all (select sal from emp where deptno=10);
ENAME      DEPTNO       SAL
---------- ------ ---------
WARD           30   1250.00
MARTIN         30   1250.00
ADAMS          20   1100.00
JAMES          30    950.00
SMITH          20    800.00

SQL> 
SQL> 
SQL> 
SQL> select ename,deptno,sal from emp where sal < any (select sal from emp where deptno=10);
ENAME      DEPTNO       SAL
---------- ------ ---------
SMITH          20    800.00
JAMES          30    950.00
ADAMS          20   1100.00
WARD           30   1250.00
MARTIN         30   1250.00
MILLER         10   1300.00
TURNER         30   1500.00
ALLEN          30   1600.00
CLARK          10   2450.00
BLAKE          30   2850.00
JONES          20   2975.00
SCOTT          20   3000.00
FORD           20   3000.00
13 rows selected

SQL> select sal from emp where deptno=10
  2  ;
      SAL
---------
  2450.00
  5000.00
  1300.00

SQL> select ename,deptno,sal from emp where sal < any (select sal from emp where deptno=20);
ENAME      DEPTNO       SAL
---------- ------ ---------
SMITH          20    800.00
JAMES          30    950.00
ADAMS          20   1100.00
WARD           30   1250.00
MARTIN         30   1250.00
MILLER         10   1300.00
TURNER         30   1500.00
ALLEN          30   1600.00
CLARK          10   2450.00
BLAKE          30   2850.00
JONES          20   2975.00
11 rows selected

SQL> select sal from emp where deptno=20;
      SAL
---------
   800.00
  2975.00
  3000.00
  1100.00
  3000.00

SQL> select ename,deptno,sal from emp where sal < all(select sal from emp where deptno=20);
ENAME      DEPTNO       SAL
---------- ------ ---------

SQL> 

<ANY是比最大的还要小

>ANY是比最小的还要大

<ALL是比所有值都要小，即比最小的还要小

>ALL是比所有值都要大，即比最大的值还要大