子查询中all与any的区别

有些人往往对子查询中涉及到的all与any容易混淆，以为两个表达的是同一个意思，
就以理解英文字面上的大概意思去理解。这里，all是所有，any是指任意一个。
以下通过一些例子更明了地去讲述这两个词在查询中的意思。

数据背景，都以该查询结果作为子查询的结果：
SQL> SELECT distinct salary

  2  FROM   employees

  3  WHERE  job_id = 'IT_PROG';

    SALARY

----------

      9000

      4800

      4200

      6000

1、All：
1》小于：

SQL> SELECT employee_id, last_name, job_id, salary

  2  FROM   employees

  3  WHERE  salary < ALL

  4  (SELECT salary

  5  FROM   employees

  6  WHERE  job_id = 'IT_PROG')

  7  AND    job_id <> 'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

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

        185 Bull                      SH_CLERK         4100

        192 Bell                      SH_CLERK         4000

        193 Everett                   SH_CLERK         3900

        188 Chung                     SH_CLERK         3800

        137 Ladwig                    ST_CLERK         3600

        189 Dilly                     SH_CLERK         3600

        141 Rajs                      ST_CLERK         3500

......

      128 Markle                    ST_CLERK         2200

        136 Philtanker                ST_CLERK         2200

        132 Olson                     ST_CLERK         2100

44 rows selected.

2》大于：

SQL> SELECT employee_id, last_name, job_id, salary

  2  FROM   employees

  3  WHERE  salary >ALL

  4  (SELECT salary

  5  FROM   employees

  6  WHERE  job_id = 'IT_PROG')

  7  AND    job_id <> 'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

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

        157 Sully                     SA_REP           9500

        151 Bernstein                 SA_REP           9500

        163 Greene                    SA_REP           9500

        170 Fox                       SA_REP           9600

......

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

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

        100 King                      AD_PRES         24000

23 rows selected.

2、Any:
1》小于：

SQL> SELECT employee_id, last_name, job_id, salary

  2  FROM   employees

  3  WHERE  salary < ANY

  4  (SELECT salary

  5  FROM   employees

  6  WHERE  job_id = 'IT_PROG')

  7  AND    job_id <> 'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

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

        132 Olson                     ST_CLERK         2100

        136 Philtanker                ST_CLERK         2200

        128 Markle                    ST_CLERK         2200

        135 Gee                       ST_CLERK         2400

... ...

        177 Livingston                SA_REP           8400

        176 Taylor                    SA_REP           8600

        175 Hutton                    SA_REP           8800

76 rows selected.

2:》大于：

SQL> SELECT employee_id, last_name, job_id, salary

  2  FROM   employees

  3  WHERE  salary > ANY

  4  (SELECT salary

  5  FROM   employees

  6  WHERE  job_id = 'IT_PROG')

  7  AND    job_id <> 'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

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

        100 King                      AD_PRES         24000

        101 Kochhar                   AD_VP           17000

        102 De Haan                   AD_VP           17000

        145 Russell                   SA_MAN          14000

... ...

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

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

        124 Mourgos                   ST_MAN           5800

        200 Whalen                    AD_ASST          4400

57 rows selected.

从上面的例子看到，all是表示所有的，如果主查询查的是大于所有的，则大于子查询最大的那个就是主查询的查询结果，
小于所有的，则小于子查询最小的那个就是主查询的查询结果。any是表示任意一个，不是所有，如果主查询查的是大于
任意一个，则大于子查询集中一个就可以了，小于任意一个，同样的，则小于子查询集中一个就可以了。这就是它们容易
混淆的地方，也是它们区别的地方。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2125962/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2125962/