ICT블로그

※ ANY와 ALL

ANY와 ALL은 ①서브쿼리와 함께 쓰이거나 ②값들의 집합과 함께 쓰인다. + 비교 연산자와 함께

②값들의 집합

select * from temp

where salary > all (10000000,20000000,30000000,40000000);

그러나 대게는 ①서브쿼리를 사용할 때 쓰이는데, 만약 질의에 필요할 결과값 집합을 미리 알고 있다면, 굳이 ANY나 ALL연산자를 사용해야 할 이유가 없기 때문이다.

예제

<1-1 ANY>

select emp_id, emp_name, salary

from temp

where salary > any (select salary

                      from temp

                     where lev = '과장');

<1-2 MIN을 사용할 경우 -- 연관성 없는 서브쿼리>    

select emp_id, emp_name, salary

from temp

where salary > (select min(salary)

                      from temp

                     where lev = '과장');

<2-1 ALL>

select emp_id, emp_name, salary

from temp

where salary > all (select salary

                      from temp

                     where lev = '과장');

<2-2 ALL -- 연관성 없는 서브쿼리>                     

select emp_id, emp_name, salary

from temp

where salary > (select max(salary)

                      from temp

                     where lev = '과장');

뇌를 자극하는 오라클 프로그래밍(홍형경) 392p

<1. 서브쿼리 사용 시>

select emp.first_name||' '||emp.last_name as EMP_NAMES , emp.department_id,

          (select dep.department_name

              from departments dep

             where emp.department_id=dep.department_id) dep_name

             from employees emp;

<2. 단순 조인 사용 시>

select first_name||' '||last_name as EMP_NAMES , emp.department_id, dep.department_name

from employees emp, departments dep

where emp.department_id=dep.department_id(+);

서브쿼리를 사용한 1과 2는 같은 결과를 산출하지만, 성능면에서는 OUTER JOIN을 사용하지 않아도 되는 1번 문장이 더 나을 수있다. 만약 emp.department_id 값이 null 이 아닌 컬럼이 있을 경우, 아래 쿼리처럼 단순 EQUI JOIN을 사용해도 무방하다.

<2-1. 단순 조인 사용시 - EQUI JOIN>

select first_name||' '||last_name as EMP_NAMES , emp.department_id, dep.department_name

from employees emp JOIN departments dep

on emp.department_id = dep.department_id;

결국 설계 단계에서 테이블이 어떻게 정의될지와 사용될 쿼리에 대한 계획이 있어야 불필요한 성능 저하를 막을 수 있다.

-- Inner Query에서 Outer Query의 어떤 컬럼 값을 사용하는 경우를 일컬음

-- Inner Query = Sub Query, Outer Query = Main Query

-- 대게의 경우 Main Query가 Sub Query의 결과를 이용하기만 하는데, CORERELATED 서브쿼리의 경우 Sub Query도 Main Query의 값을 이용하게 됨

-- 주의: Correlated Sub query는 Main Query에서 하나의 레코드가 조건 절에서 비교되기 위해서 Inner Query 가 꼭 한번씩 수행되어야 하므로, Sub Query의 response time이 늦고 Main Query에서 처리될 record 수가 많은 경우 속도가 저하 될 수 있음 --> InLine View 등 다른 방법을 강구해야 함.

[따라하기]

-- 틀린 답 (조인 사용)

select a.emp_id, a.emp_name from

   temp a, (select lev, avg(salary) as a_sal

            from temp

            group by lev) b

            where a.lev = b.lev

              and a.salary > b.a_sal;

-- 옳은 답 (연관성 있는 서브 쿼리 사용)

select a.emp_id, a.emp_name

  from temp a

  where a.salary > (select avg(salary)

                      from temp b

                      where b.lev = a.lev);

[예제2] UPDATE 문에서의 활용

update tdept a

      set a.boss_id = (select min(b.emp_id)

                      from temp b

                      where b.dept_code =a.dept_code);

[Q/A : 03-13] INSERT문의 이용

-- 실행 안되는 문장

insert into tcom values (select '2002',emp_id, 0.1, 0.1 * salary from temp);

-- 실행 되는 문장

insert into tcom (work_year, emp_id, bonus_rate, comm)

           select '2002',emp_id, 0.1, 0.1 * salary from temp;