子查詢中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是表示任意一個，不是所有，如果主查詢查的是大於
任意一個，則大於子查詢集中一個就可以了，小於任意一個，同樣的，則小於子查詢集中一個就可以了。這就是它們容易
混淆的地方，也是它們區別的地方。