Oracle Chapter7. 서브 쿼리 (오라클 입문자용)

현 블로그는 모바일 환경이 아닌 PC환경에 최적화 되어있습니다.

Mir의 운영환경

(필자가 맥 vmware위에 리눅스를 올려서 오라클을 깐다고 이틀동안 고생했다 ㅠㅠ..
아무튼 이번 Chapter부터 출력문의 색상과 양식이 다를것이다. ;; 차차 다른 챕터도 똑같이 수정하겠다 ; ㅁ;)


자 이번 Chapter에서  배울것은 서브쿼리문이다 
앞서 Chapter에서 조인문을 배울때 두개의 명령을 내려 구할수 있는 값을 테이블을 조인해주어 하나의 명령으로 구해보았다.

서브쿼리문도 두개의 명령을 사용하여 얻는 결과값을 하나의 명령으로 만드는 것인데
조인문처럼 테이블을 연결시키는것이 아니라 하나의 테이블에서 검색한 결과를 다른 테이블에 전달하여 검색하는것이다.
말보다 한번 서브쿼리문으로 SMITH의 부서명을 구해보자.
아래 SMITH의 부서를 구할수 있는 두개의 쿼리문이 있다. 

SELECT deptno
FROM emp
WHERE ename='JONES'; 

SELECT dname

FROM dept
WHERE deptno=20; 



첫번째 쿼리문에서 JONES의 부서번호를 emp테이블에서 구하고 난뒤
두번째 쿼리문에서 emp테이블에서 구한값(20)에 대한 부서명을 dept테이블에서 찾아 출력시켰다.

자 그럼 여기서 "20"은 첫번째 쿼리문에서 구한 값이다.
즉 20 = SELECT deptno
              FROM emp
              WHERE ename='JONES'  의 공식이 성립된다.

이것을 두번째 쿼리문의 20대신에 사용하면 아래와 같은 식이 된다.

SELECT dname

FROM dept
WHERE deptno = ( SELECT deptno
                               FROM emp

                               WHERE ename='JONES');

이렇게 ( )안에 있는 쿼리문을 서브쿼리문이라고 불리우며
이 서브쿼리문은 다른 테이블(emp)에서 검색한 결과 (20)을 실행되고 있는 쿼리문에 전달하는 역활을 한다.
즉 쿼리문 안에 또 다른 쿼리문이 생기는것이다.

자 그럼 쿼리문의 종류와 좀더 다양한 쓰임새에 대해 더욱더 알아보자.



단일 행 서브 쿼리

단일 행 서브 쿼리는 하나의 행만 검색하여 그 결과를 메인 쿼리에 보내는 것이다.
위 JONES의 부서이름을 구하는 서브쿼리문이 단일 행 서브 쿼리이다.
부서번호를 알아내기 위한 쿼리(20을 출력하기 위한 쿼리문)가 서브 쿼리로 사용되고
이 서브쿼리는 하나의 결과값만을 얻기 때문에 단일 행 서브 쿼리가 된다.

자 이번엔 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명을 출력해보자.
일반적인 조인문을 사용하면 아래와 같은 쿼리문이 사용된다.

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.deptno = 10;




조인을 해주고 난뒤에 또다른 조건문을 넣을려면 AND를 추가하고 조건문을 넣어주면 된다.

자 그럼 서브쿼리를 사용하여 한번 출력해보자.
우선 어떤값을 서브쿼리문으로 가져올지 생각해보자.
10번 부서에 근무하는 부서명을 출력해야되니 deptno = 10과 10번 부서의 dname(부서명)을 가져와야 되겠다.

SELECT e.name, d.dname
FROM emp e, (
                          SELECT deptno, dname
                          FROM dept
                          WHERE deptno=10
                         ) d
WHERE e.deptno=d.deptno;



위 FROM절에서 테이블 e(emp)와 d를 호출하였는데 테이블 d는

SELECT deptno, dname
FROM dept
WHERE deptno=10;
의 결과값(부서번호 10번과
10번의 부서명)을 가진 테이블이다.

그러면 테이블의 공통되는 값이 부서번호가 10일때 밖에 없다.
그래서 별다른 조건문이 없이 그냥 조인만 시켜주면 부서번호가 10인 사원명을 출력할수 있는것이다.
 
서브쿼리문으로 내가 검색, 출력할때 필요한 정보만 뽑아서 테이블을 새로 만드는것이다.
첫번째 SMITH의 부서번호만을 가진 테이블을 만들어 보았고
두번째는  부서번호 10번과 10번의 부서명을 가진 테이블을 만들어 보았다.
모두 하나의 행으로만 만들었기 때문에 단일 행 서브쿼리로 부를수 있다. 

자 그럼 단일 행 서브쿼리를 조금더 응용을 해보도록 하자.
서브쿼리를 그룹함수 및 연산자를 사용하여 만들면 다양한 검색을 할수 있는데
예를 들어 사원들의 평균급여를 서브쿼리문으로 구하여
평균급여보다 더 많은 급여를 받는 사원들만 출력시킬수 있다.

우선 서브쿼리문을 만들어보자.

SELECT avg(sal)
FROM emp;



사원들의 평균급여를 구하는 쿼리문이다. (avg를 모른다면 Chapter5를 다시한번 보자!)
이 서브쿼리문을 이용하여 평균급여보다 더 많은 급여를 받는 사원을 출력해보자.

SELECT ename, sal
FROM emp
WHERE sal >= (SELECT avg(sal)
                           FROM emp);








WHERE문에서 sal(급여)가 평균급여(쿼리문) 보다 많으면 출력시키면된다.

이렇게 자기가 원하는값을 가진 테이블을 생성하여 그것을 쿼리문으로 넣으면
여러조건을 좀더 쉽게 검색할수 있는것이다.

서브쿼리문은 반드시 ( )로 감싸주어야 한다. 
먼저 값을 구하여 메인쿼리에 값을 주어야 하기때문에 ( )가 꼭 필요한것이다.
그리고 완성된 서브쿼리문은 자기혼자서도 작동을 해야된다.
그럼 문제로 조금더 익혀보도록 하자 


--- Question &---

1. 사원이름이 'JONES'인 직원의 부서명을 출력하세요.

2. 평균급여보다 더 많은 급여를 받는 사원의 이름, 사번, 급여를 검색하되 급여가 많은 순서로 나열하세요.

3. 부서번호가 10인 사원중에서 최대급여를 받는 사원과 동일한 급여를 받는 사원의 사번과 이름을 출력하세요.





--- & Answer ---

 

1. 사원이름이 'JONES'인 직원의 부서명을 출력하세요.

SELECT dname
FROM dept
WHERE deptno = (SELECT deptno
                               FROM emp
                               WHERE ename = 'JONES');




2. 평균급여보다 더 많은 급여를 받는 사원의 이름, 사번, 급여를 검색하되 급여가 많은 순서로 나열하세요.

SELECT ename,empno,sal 

FROM emp 

WHERE sal > (SELECT avg(sal)
                         FROM emp) 

order by sal desc;

3. 부서번호가 10인 사원중에서 최대급여를 받는 사원과 동일한 급여를 받는 사람의 사번과 이름 월급을 출력하세요.

SELECT empno, ename, sal
FROM emp
WHERE sal = (SELECT max(sal)
                         FROM emp
                        WHERE deptno=10);






다중 행 서브 쿼리

위에서 단일 행 서브 쿼리를 살펴보았는데 다중 행 서브 쿼리는 말 그대로 여러 행을 가진 서브쿼리를 생성하는거다.
다중 행 서브 쿼리는 반드시 다중행 연산자와 함께 사용해야 하는데 하나하나 알아보자.

IN연산자 - 서브쿼리의 출력결과중 하나라도 일치하면 참이 된다.
ALL 연산자 - 서브쿼리의 출력결과와 모두 일치하면 참이된다.
ANY, SOME 연산자 - 서브쿼리의 출력결과와 하나이상의 조건이 만족하면 참이 된다.
EXIST 연산자 - 서브쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참이 된다.

위 4개의 연산자가 다중행 연산자인데 ALL 연산자 빼고는 다들 비슷비슷 해보인다.
그럼 자세히 알아보도록 하자.

IN연산자는 앞서 Chapter3에서도 한번 나왔는데 or을 좀더 편하게 사용하기 위해 쓰였다.
여기서도 IN연산자는 결과값중에 하나라도 일치하면 조건식에서 참을 주게 되는데
예를 들어 급여를 3000이상 받는 사원이 속해있는 부서의 모든 사람을 출력하는 쿼리문을 만들어보자.

우선 급여를 3000이상 받는 사원들이 있는 부서를 쿼리문으로 작성해보자.

SELECT distinct deptno
FROM hemp
WHERE sal >= 3000;



(중복된값을 제거하기 위해 distinct를 사용해주었다.)
급여를 3000이상 받는 사람이 있는 부서는 10번부서와 20번 부서이다.

일반쿼리문으로 구할때는 우선 위 쿼리문으로 급여를 3000이상 받는 사람이 있는 부서번호 20과 10을 구하고 난뒤

SELECT ename, sal, deptno
FROM emp
WHERE deptno = 10 OR deptno= 20;

이렇게 or연산자를 이용하여
부서번호가 10번 혹은 20번인 사원의
정보를 출력한다.






그럼 이 두개의 쿼리문을 합쳐보도록 하자.

SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (SELECT distinct deptno
                                FROM emp
                                WHERE sal >= 3000);

단일 행 서브 쿼리문은 일반 연산자를 사용하여
서브쿼리문과의 조건식을 적어주면 되지만
(WHERE X = [서브쿼리문]) 
 
다중 행 서브 쿼리문은 하나 이상의 행이
존재하기때문에 둘중 하나라도 만족하면
(부서번호가 10번이거나 20번이면)
참이 되도록 IN연산자를 사용하였다.
(WHERE X IN [서브쿼리문])


다음은 ANY, SOME연산자를 알아보자.
ANY, SOME연산자는  WHERE문의 조건과 비교하여 하나이상이 일치하면 출력을 하게 된다.
IN연산자와 ANY, SOME연산자의 차이점은  
IN연산자는 값을 일치(=)되는것이 하나라도 있을때 사용하는것이고
ANY연산자는 값이 하나라도 크거나 작거나 같거나 (>, <, =)등의 비교연산자를 사용할때 사용된다.
 

즉 > ANY 라고 표시를 하면 "비교값 중 하나보다 크냐" 라고 묻는 것이 되며
그 값들 중 어느 하나 보다 더 크면, 즉 값들의 최소값보다 더 크면 참이 된다.

아래는 30번 부서에 소속되어 있는 사원들의 급여를 출력한것이다.

SELECT sal
FROM emp
WHERE deptno = 30;






그럼 이 값을 이용하여 30번 부서에서 급여가 가장낮은 사람(950) 보다 많은 급여를 받는 사원의 이름, 급여를 출력해보자.

SELECT ename, sal
FROM  emp
WHERE sal > ANY (SELECT sal

                                   FROM emp

                                   WHERE deptno = 30); 


마찬가지로 단일행에서는 > 혹은 < 으로 값을 비교하지만
다중행이기 때문에 ANY, SOME을 사용하여
서브쿼리문에 있는 모든 행에대해 비교하게 된다. 

모든 행중과 비교한뒤 하나라도 만족하면(크거나 작으면)
출력을 하게 되는것이다.

ANY는 비교연산자를 사용하는데 비교연산자 중에서
(=)도 있기때문에 IN연산자대신에 사용할수도 있다.
(위의 IN연산자 예제를 IN대신에 = ANY로
바꿔 보았다.)
 
SELECT ename, sal, deptno
FROM emp
WHERE deptno = ANY (SELECT distinct deptno
                                       FROM emp
                                       WHERE sal >= 3000);

다음은 ALL연산자에 대해 알아보자.
> ANY가 최소값보다 더큰지 묻는 쿼리문이라면 > ALL은 최대값보다 더큰지 묻는 쿼리문이 될것이다.
ALL연산자는 모든 조건을 만족시켜야 되는데 반대로 말하면
ALL연산자는 모든 행과 비교한뒤에 하나라도 만족하지 못하면 출력을 하지 않게 된다.

즉 > ALL은 "최대값보다 더 크냐"라고 묻는 뜻이 된다.
위 > ANY 예제 쿼리문을 > ALL로 바꿔보자.

SELECT ename, sal
FROM  emp
WHERE sal > ALL (SELECT sal

                                FROM emp

                                WHERE deptno = 30);

> ANY를 사용하였을때는 30번 부서에서 급여가
가장 낮은사람보다 큰사람을 출력하였는데

> ALL을 사용하니 30번 부서에서 급여가 가장 높은 사람(2850)보다 큰사람을 출력시켰다.
예를 들어 급여가 2800이면 서브쿼리문의 모든 행과 비교했을때
다른 조건은 만족하지만 2800 > 2850 을 만족하지 못한다.
그래서 급여가 2800인 사람은 출력되지 못하고 2850이상인 사람만 출력되는 것이다.

그럼 = ALL문은 어떻게 사용될까?
만약 위의 IN연산자 예제에 IN대신에 = ALL을 사용하면
부서번호가 10번이가나 20번인 사람(OR)이 아닌 부서번호가 10번이면서 20번인사람(AND)로 사용될것이다.
물론 10번이면서 20번인 사람은 없으니 출력결과물은 하나도 없을것이다.


마지막으로 EXIST연산자를 알아보자. 
(EXIST연산자는 잘 사용이 안되는지 책에도 제대로 설명이 안나와 있어서 필자의 설명이 틀릴수도 있다.)
EXIST연산자는 만족하는 값이 하나라도 존재하면 참이 되는 연산자인데
서브쿼리 값이 존재하는지 존재하지 않는지 알아보고 존재하는 값들만 반환하는 것이다.
말 그대로 존재만 하면 출력이 된다.

예를 들어 dept테이블에서 부서명과 부서코드를 출력하는데 emp테이블에서 존재하는 부서코드만 출력시키는 것이다.

SELECT dname, deptno
FROM dept
WHERE exists (SELECT *
                           FROM emp
                           WHERE dept.deptno = emp.deptno);

이렇게 서브쿼리문에서 dept테이블에서의
부서번호와 emp테이블에서의 부서번호를
비교하여서 두 테이블에 모두 있는 값
(emp테이블에 존재하는 값)
을 출력시키게 된다.

하나만 더 해보자. mgr에 존재하는 사원의 사번과 이름, 월급을 한번 출력시켜보자.
즉 mgr과 empno를 비교해서 두 컬럼에 모두 있는 값을 출력시키면 된다.

SELECT empno, ename, sal
FROM emp e
WHERE exists (SELECT *
                         FROM emp m
                         WHERE e.empno = m.mgr);

이렇게 exists는 별다른 조건이 없이
서브쿼리문 안에서 두개의 값을 비교하여
하나라도 값을 가지고 있으면(존재하면)
출력시키게 된다.



exists연산자를 사용하는 쿼리문의 가장 큰 특징은 서브쿼리문 혼자서는 값이 출력이 되지 않을수도 있는것이다.
단일 행 서브 쿼리문의 경우는 혼자서 값을 출력시킬수 있는것이 특징중의 하나였으며
여타 다른 쿼리문도 서브쿼리문 혼자서 값을 출력시킬수 있지만 exists는 그렇지 않다는거! 기억해두자.


자 그럼 다중 행 서브 쿼리의 문제를 풀어보자!
(점점 가면갈수록 문제가 복잡해 진다..ㅠㅠ 문제를 내는 필자의 머리도 터질꺼 같다..)


--- Question &---
 

1. SCOTT의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력하세요.

2. 직급이 사원인 'CLERK'인 사람의 부서의 부서번호와 부서명을 출력하세요.

3. 이름에 T를 포함하고 있는 사원들과 같은 부서에서 근무하는 사원의 사번과 이름을 출력하세요.

4. 부서 위치가 DALLAS인 모든 사원의 이름, 부서 번호를 출력하세요.

5. SALES 부서의 모든 사원의 이름과 급여를 출력하세요.

6. KING에게 보고하는(=매니져가 KING인 사원) 모든 사원의 이름과 급여를 출력하세요.

7. 자신의 급여가 평균급여보다 많고 이름에 S가 들어가는 사원과 동일한 부서에서
    근무하는 모든 사원의 이름, 급여를 출력하세요.

 


--- & Answer ---

1. SCOTT의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력하세요.

SELECT ename, sal
FROM emp
WHERE sal >= (SELECT sal
                          FROM emp
                          WHERE ename = 'SCOTT');






2. 직급(job)이 'CLERK'인 사람의 부서의 부서번호와 부서명을 출력하세요.

SELECT dname, deptno
FROM dept
WHERE deptno in (SELECT deptno
                               FROM emp
                               WHERE job = 'CLERK');






3. 이름에 T를 포함하고 있는 사원들과 같은 부서에서 근무하는 사원의 사번과 이름을 출력하세요.

SELECT empno, ename
FROM emp
WHERE deptno in (SELECT deptno
                                FROM emp
                                WHERE ename like %T%);














4. 부서 위치(loc)가 DALLAS인 모든 사원의 이름, 부서 번호를 출력하세요.

SELECT ename, deptno
FROM emp
WHERE deptno = (SELECT deptno
                               FROM dept
                               WHERE loc = 'DALLAS');








5. SALES 부서의 모든 사원의 이름과 급여를 출력하세요.

SELECT ename, sal
FROM emp
WHERE deptno = (SELECT deptno
                               FROM dept
                               WHERE dname = 'SALES');









6. KING에게 보고하는(=매니져(mgr)가 KING인 사원) 모든 사원의 이름과 급여를 출력하세요.

SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno
                           FROM emp
                           WHERE ename = 'KING');







7. 자신의 급여가 평균급여보다 많고 이름에 S가 들어가는 사원과
    동일한 부서에서 근무하는 모든 사원의 이름, 급여를 출력하세요.
SELECT ename, sal
FROM emp
WHERE deptno in (SELECT deptno
                                FROM emp
                                WHERE sal > (SELECT avg(sal)
                                                         FROM emp)
                                AND ename like '%S% );

 



마지막 문제처럼 서브쿼리문안에 또 서브쿼리문을 넣을수 있다.
후에 가면 진짜 복잡하다.;;

자 이번Chapter에서 쿼리문안에 또다른 쿼리문을 넣어 다른 테이블의 값을 가져와
혹은 자기가 원하는 테이블을 만들어서  결과물을 얻어내는 서브쿼리문이라는 방법을 알아보았다.
이제다음 Chapter에서는 직접 테이블을 만들고 변경하는 방법을 알아보자!