ALL比较符的用法

ANY SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. Evaluates to FALSE if the query returns no rows.	SELECT * FROM employees   WHERE salary = ANY   (SELECT salary    FROM employees   WHERE department_id = 30)   ORDER BY employee_id;
ALL	Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. Evaluates to TRUE if the query returns no rows.	SELECT * FROM employees   WHERE salary >=   ALL (1400, 3000)   ORDER BY employee_id;

 

官方参考： http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions002.htm#sthref1908

 

以scott/tiger 的emp表作为示例：

scott@TEST0924> select * from emp;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

14 rows selected.

 

ALL:类似AND操作符，即要满足所有条件，大于所有值，即大于最大值。

scott@TEST0924> select empno,sal from emp

  2  where sal>all(2000,3000);

 

     EMPNO        SAL

---------- ----------

      7839       5000

等价于大于所有的值

scott@TEST0924> select empno,sal from emp

  2  where sal>2000 and sal>3000;

 

     EMPNO        SAL

---------- ----------

      7839       5000

等价于大于最大的那个值

scott@TEST0924> select empno,sal from emp

  2  where sal>3000;

 

     EMPNO        SAL

---------- ----------

      7839       5000

 

如果all后面跟着子查询：

scott@TEST0924> SELECT e2.sal FROM   emp e2 WHERE  e2.deptno = 20;

 

       SAL

----------

       800

      2975

      3000

      1100

      3000

 

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  e1.sal > ALL (SELECT e2.sal

  4  FROM   emp e2

  5  WHERE  e2.deptno = 20);

 

     EMPNO        SAL

---------- ----------

      7839       5000

等价于

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  e1.sal > ALL (800,2975,3000,1100,3000);

 

     EMPNO        SAL

---------- ----------

      7839       5000

等价于大于最大值：

scott@TEST0924> SELECT e1.empno, e1.sal FROM   emp e1

  2   WHERE  e1.sal > 3000;

 

     EMPNO        SAL

---------- ----------

      7839       5000

等价于

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  NOT (e1.sal <= ANY (SELECT e2.sal

  4  FROM emp e2

  5  WHERE e2.deptno = 20));

 

     EMPNO        SAL

---------- ----------

      7839       5000

也等价于

scott@TEST0924> SELECT e1.empno, e1.sal                                                                                                            

  2  FROM   emp e1

  3  WHERE  NOT EXISTS (SELECT e2.sal

  4  FROM emp e2

  5  WHERE e2.deptno = 20

  6  AND   e1.sal <= e2.sal);

 

 

     EMPNO        SAL

---------- ----------

      7839       5000

 

如果all的子查询有返回值，则

• "x = ALL (...)": The value must match all the values in the list to evaluate to TRUE.所有值都要匹配
• "x != ALL (...)": The value must not match any values in the list to evaluate to TRUE.至少有一个值不匹配
• "x > ALL (...)": The value must be greater than the biggest value in the list to evaluate to TRUE.大于最大的值
• "x < ALL (...)": The value must be smaller than the smallest value in the list to evaluate to TRUE.小于最小的值
• "x >= ALL (...)": The value must be greater than or equal to the biggest value in the list to evaluate to TRUE.大于等于最大的值
• "x <= ALL (...)": The value must be smaller than or equal to the smallest value in the list to evaluate to TRUE.小于等于最小的值

如果all的子查询返回值为0行，则

scott@TEST0924> SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100

  2  ;

 

no rows selected

=ALL，按照原来的值输出

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  e1.sal=ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

 

     EMPNO        SAL

---------- ----------

      7369        800

      7499       1600

      7521       1250

      7566       2975

      7654       1250

      7698       2850

      7782       2450

      7788       3000

      7839       5000

      7844       1500

      7876       1100

      7900        950

      7902       3000

      7934       1300

 

14 rows selected.

 

!=ALL也按照原来的值输出，但不知道按什么排序的？

scott@TEST0924> SELECT e1.empno, e1.sal FROM   emp e1

  2  WHERE  e1.sal!=ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

 

     EMPNO        SAL

---------- ----------

      7369        800

      7902       3000

      7788       3000

      7698       2850

      7782       2450

      7839       5000

      7654       1250

      7521       1250

      7499       1600

      7876       1100

      7900        950

      7566       2975

      7934       1300

      7844       1500

 

14 rows selected.

 

<ALL,按原来的值降序输出

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  e1.sal<ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

 

     EMPNO        SAL

---------- ----------

      7839       5000

      7902       3000

      7788       3000

      7566       2975

      7698       2850

      7782       2450

      7499       1600

      7844       1500

      7934       1300

      7521       1250

      7654       1250

      7876       1100

      7900        950

      7369        800

 

14 rows selected.

<=ALL和<ALL一样按原来的值降序输出

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  e1.sal<=ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

 

     EMPNO        SAL

---------- ----------

      7839       5000

      7902       3000

      7788       3000

      7566       2975

      7698       2850

      7782       2450

      7499       1600

      7844       1500

      7934       1300

      7521       1250

      7654       1250

      7876       1100

      7900        950

      7369        800

 

14 rows selected.

 

>ALL和>=ALL,按原来的升序输出

scott@TEST0924> SELECT e1.empno, e1.sal

  2  FROM   emp e1

  3  WHERE  e1.sal > ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

 

     EMPNO        SAL

---------- ----------

      7369        800

      7900        950

      7876       1100

      7521       1250

      7654       1250

      7934       1300

      7844       1500

      7499       1600

      7782       2450

      7698       2850

      7566       2975

      7788       3000

      7902       3000

      7839       5000

 

14 rows selected.

 

ANY比较符参考： http://blog.csdn.net/rlhua/article/details/12007309