SQL> SELECT sal FROM scott.emp WHERE job='MANAGER';
SAL
---------
2975.00
2850.00
2450.00
any：
SQL> SELECT emp.empno,emp.ename,emp.job,emp.sal FROM scott.emp WHERE sal>any(SELECT sal FROM scott.emp WHERE job='MANAGER');
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7839 KING PRESIDENT 5000.00
7902 FORD ANALYST 3000.00
7788 SCOTT ANALYST 3000.00
7566 JONES MANAGER 2975.00
7698 BLAKE MANAGER 2850.00
对比我们可以发现 这个查询出的sal>2450 ,也即是说>any的意思是大于子查询的最最小的sal
SQL> SELECT emp.empno,emp.ename,emp.job,emp.sal FROM scott.emp WHERE sal<any(SELECT sal FROM scott.emp WHERE job='MANAGER');
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7369 SMITH CLERK 800.00
7900 JAMES CLERK 950.00
7876 ADAMS CLERK 1100.00
7521 WARD SALESMAN 1250.00
7654 MARTIN SALESMAN 1250.00
7934 MILLER CLERK 1300.00
7844 TURNER SALESMAN 1500.00
7499 ALLEN SALESMAN 1600.00
7782 CLARK MANAGER 2450.00
7698 BLAKE MANAGER 2850.00
10 rows selected
在和子查询的sal对比，可以看出sal<2950，sal<any ()意思是小于子查询中最大的sal
SQL> SELECT emp.empno,emp.ename,emp.job,emp.sal FROM scott.emp WHERE sal=any(SELECT sal FROM scott.emp WHERE job='MANAGER');
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7566 JONES MANAGER 2975.00
7698 BLAKE MANAGER 2850.00
7782 CLARK MANAGER 2450.00
从结果可以看出就是子查询
all:
SQL> SELECT emp.empno,emp.ename,emp.job,emp.sal FROM scott.emp WHERE sal>ALL(SELECT sal FROM scott.emp WHERE job='MANAGER');
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7788 SCOTT ANALYST 3000.00
7902 FORD ANALYST 3000.00
7839 KING PRESIDENT 5000.00
查询结果显示：sal>all()是大于子查询中最大的sal
SQL> SELECT emp.empno,emp.ename,emp.job,emp.sal FROM scott.emp WHERE sal<ALL(SELECT sal FROM scott.emp WHERE job='MANAGER');
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7499 ALLEN SALESMAN 1600.00
7844 TURNER SALESMAN 1500.00
7934 MILLER CLERK 1300.00
7521 WARD SALESMAN 1250.00
7654 MARTIN SALESMAN 1250.00
7876 ADAMS CLERK 1100.00
7900 JAMES CLERK 950.00
7369 SMITH CLERK 800.00
8 rows selected
查询结果显示：sal>all()是小于子查询中最小的sal
SQL> SELECT emp.empno,emp.ename,emp.job,emp.sal FROM scott.emp WHERE sal=all(SELECT sal FROM scott.emp WHERE job='MANAGER');
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
some： some和any的用法一样 ，sal>some(sal) 大于子查询中最小的sal，sal<some(sal)小于子查询中最大的sal