ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...

[   ORACLE 서브쿼리와 ANY,ALL,IN에 대하여...  ]

메인 쿼리 안에서 메인 쿼리의 중간 결과로 사용되는 쿼리문을 "서브쿼리"라고 하는데,

서브 쿼리를 작성할 때 고려해야할 점은, 서브쿼리의 결과값이 단일 튜플이 나올 수도 있지만 다중 튜플일 경우이다.

일반적으로 salary(급여)가 (13000,6000,7000) 이 중 하나인 경우( salary = 13000 or salary = 6000, salary = 7000 )는

간단하게 salary IN (서브 쿼리) 로 "IN"을 사용해 작성하면 되지만

">"와 같은 연산자는 IN을 사용할 수가 없는 단일 비교 연산자이다. 이러한 경우에는 ANY와 ALL을 사용하게 된다.

ANY는 해당 비교 연산의 or결과로 ALL은 and 결과로 연산이 되는데 예를들자면

salary > ANY(다중 튜플을 결과로하는 서브쿼리) 는 

salary > 13000 or salary > 6000 or salary > 7000 의 결과가 된다. 즉, salary > 6000을 만족하는 결과들이 될 것이고,

salary > ALL(다중 튜플을 결과로하는 서브쿼리) 는

salary > 13000 and salary > 6000 and salary > 7000의 결과임으로, salary > 13000 을 만족하는 결과가 나올 것이다.

다음 아래는 해당 내용에 대한 예제 코드와 주석문이다.

--[ 서브 쿼리 ] -- 메인 쿼리 안에 또 다른 쿼리문이 들어가는 것! -- 메인쿼리를 구성하는 소단위 쿼리다. -- DML(select,insert,delete,update)에서 모두 사용가능하다. -- 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용된다. -- 서브 쿼리 자체는 일반 쿼리와 다를 바가 없다. -- 평균 급여보다 급여가 작은 사람들의 정보를 구할 때 select employee_id, first_name, last_name, salary from employees where salary < 6462; -- 이렇게하면 가변적으로 변하는 값에 대응할 수가 없음 -- 따라서, 서브쿼리를 이용해 평균을 구한 값을 중간 결과값으로 써서 구할 수 있다. select employee_id, first_name, last_name, salary from employees where salary < (select round(avg(salary)) from Employees); -- where절에선 집계함수 -- 사용이 불가능하기 때문에 서브쿼리를 이용해 중간 결과값을 가지고 와야 한다. --COUNTRY_ID값이 US인 location_id를 가지고와서 departments 테이블에 있는 부서명을 구해보자 select department_name from departments -- 조건절에서 서브쿼리 결과가 여러 튜플이 나올 수 있을 땐 IN을 사용해야 한다. where location_id IN (select location_id from locations where COUNTRY_ID = 'US'); --EX)Employees 테이블에서 월급이 가장 적은 사용자 정보(직급포함)를 가져와라 select emp.first_name, emp.last_name, job.JOB_TITLE from Employees emp, jobs job where salary = (select min(salary) from Employees) -- 서브쿼리 and emp.job_id = job.job_id; -- join(JOB_TITLE을 구하기 위함) --평균 급여보다 많은 급여를 받는 사원 정보를 출력(단, 급여가 높은순에서 낮은순으로) select emp.first_name || emp.last_name 이름, emp.salary 급여, j.JOB_TITLE 직급 from Employees emp, Jobs j where salary > (select avg(salary) from Employees) and emp.job_id = j.job_id order by emp.salary desc; -- 급여가 높은순으로 정렬 -- [ any, all 연산자 ] 를 활용한 서브쿼리 select salary from Employees where Department_id = 20; --department_id가 20인 부서에서 받는 급여보다 많은 급여를 받는 사람 -- 위의 서브쿼리 결과는 salary가 6000, 13000이 나옴 -- 따라서, 6000보다 많이받아도되고, 13000보다 많이 받아도 되는 사람들을 출력하도록 하자. select Employee_id, department_id, salary from Employees where salary > (select salary from Employees where Department_id = 20); -- 이 경우는 오류가 날 것이다. > 연산자나 = 연산자는 하나의 결과값과 비교하는 것인데 -- 위의 경우 서브쿼리의 결과가 여러 튜플이 나오게 된다. 따라서 그것보다 크다는 비교를 할수가 없게된다. -- in을 사용하려해도 in은 그 돈과 같은 돈을 받는 사람을 구하는 것이기 때문에 그것도 불가능함 -- 따라서, 아래처럼 해 주어야 한다. -- ANY를 사용하자. (다중값이 나올 때 각 비교의 하위 고려 대상을 기준으로 된다.) -- ex) 6000,13000 두개의 값이 나왔을 때 salary < ANY(6000,13000)일 경우 13000보다 작은 값들 -- salary > ANY(6000,13000)일 경우 6000보다 큰 값들이 나오게 됨 -- 더 쉽게 말해, salary < 6000 or salary < 13000일 경우 출력되는 상황과 같음 -- salary > 6000 or salary > 13000 -- SUMMARY) 즉, ANY는 서브쿼리의 결과가 여러개 나올 때, 그 튜플 결과의 둘중 하나만이라도 만족시키는 -- 값들은 다 나올 수 있도록 하는 "OR"와 같은 개념이고... -- ALL은 여러 튜플의 결과에 따른 비교 결과를 모두 만족시킬 수 있도록 나오게하는 "AND"와 같다. select Employee_id, department_id, salary from Employees where salary < ANY(select salary from Employees where Department_id = 20); -- ALL의 경우] -- 따라서 다 만족하게 되는 결과가 나오게 됨으로 6000보다 작은 얘들만 나오게 됨 select Employee_id, department_id, salary from Employees where salary < ALL(select salary from Employees where Department_id = 20);