Oracle的SOME,ANY和ALL操作


SOME和ANY一样，是比较宽松的，类似于OR。满足其中任何一个都可以。

ALL要求严格一些，类似于AND，必须全部满足才可以。

不能单独使用： 必须与 =, !=, >, <, <=, >=.联合使用


SOME (ANY)

Evaluates to FALSE if the query returns no rows.

ALL

Evaluates to TRUE if the query returns no rows.

做个测试吧。

先看基本信息。

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
      24000

SQL> select min(salary) from employees;

MIN(SALARY)
-----------
       2100

SQL> select count(1) from employees;

  COUNT(1)
----------
       107

SQL>

再试试结果。

SQL> SELECT count(1) FROM employees WHERE salary >= any (select 1 from dual where 1 < 1);

  COUNT(1)
----------
         0

SQL> SELECT count(1) FROM employees WHERE salary >= some (select 1 from dual where 1 < 1);

  COUNT(1)
----------
         0

SQL> SELECT count(1) FROM employees WHERE salary <= ALL (select 1 from dual where 1 < 1);

  COUNT(1)
----------
       107

SQL>



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/edwzhang/archive/2009/12/24/5069158.aspx