[   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 을 만족하는 결과가 나올 것이다.



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


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
--[ 서브 쿼리 ]
    -- 메인 쿼리 안에 또 다른 쿼리문이 들어가는 것!
    -- 메인쿼리를 구성하는 소단위 쿼리다.
    -- 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);


저작자표시 비영리

'스터디 > DB(ORACLE)' 카테고리의 다른 글

ORACLE DML( insert, update, delete, select )  (0) 2017.09.06
ORACLE DDL( create, drop, alter, truncate )  (0) 2017.09.06
ORACLE JOIN(Inner join,Outer Join,Self Join)  (0) 2017.09.05
ORACLE 그룹핑 GROUP BY, HAVING, ROLLUP  (0) 2017.09.05
ORACLE 날짜함수, 변환함수, decode(), case()  (0) 2017.09.04

+ Recent posts

티스토리툴바