9일차 # 6-16: ANY, ALL

  SELECT salary

  FROM   employees

  WHERE  job_id = 'IT_PROG';

  SELECT employee_id, last_name, job_id, salary

  FROM   employees

  WHERE  salary < ANY (SELECT salary

                       FROM   employees

                       WHERE  job_id = 'IT_PROG')

  AND    job_id <> 'IT_PROG';

  - <ANY : less than the maximum  ==> true

    >ANY : more than the minimum  ==> true

    =ANY : IN 과 같다

  - <ALL : less than the minimum  ==> true

    >ALL : more than the maximum  ==> true

    <>ALL : NOT IN 과 같다.

        salary <> ALL (9000, 6000, 4200)

        ==>     (salary <> 9000)   ==> salary NOT IN (9000, 6000, 4200)

            AND (salary <> 6000)

            AND (salary <> 4200)

  * 만약에 서브쿼리의 리턴 값 중에 null이 하나라도 있다면 <>ALL은 어떻게 될까요?

     => 조건이 N, F이 됩니다. 

     => 로우가 하나도 리턴되지 않습니다.

    salary := 8000 이라면

        where salary <> ALL (9000, null, 4200)

               ↓ ↓

        where salary NOT IN (9000, null, 4200) 

               ↓ ↓

        where (salary <> 9000) AND (salary <> null) AND (salary <> 4200)

                       T                    N                     T

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

  drop table t1 purge;

  drop table t2 purge;

  create table t1 (col1 number);

  insert into t1 values (1000);

  insert into t1 values (2000);

  insert into t1 values (3000);

  create table t2 (col1 number);

  insert into t2 values (1500);

  insert into t2 values (2500);

  commit;

  select * from t1

  where col1 > all (select col1 from t2);

         ↓ ↓

  select * from t1

  where col1 > all (1500, 2500);

  select * from t1

  where col1 > (select max(col1) from t2);

      ==> >ALL은 more than the maximum