toggle menu

Oracle 서브 쿼리 정리

2012.07.11 09:26 Database/Oracle

아래의 내용들은 JOIN 없이 서브 쿼리 만으로 가져오게 된다.

먼저 단일행 서브쿼리의 경우 서브쿼리의 결과가 여러 행이 아니라 단 하나의 행인 경우를 말한다.
따라서 WHERE 절 이후에 특정 필드명 = 서브쿼리 의 형태로 사용하게 된다.



SCOTT을 제외한 SCOTT과 같은 부서에서 근무하는 사원의 이름과 부서 번호

SQL> SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT' ) AND ENAME != 'SCOTT';

ENAME          DEPTNO
---------- ----------
SMITH              20
JONES              20
ADAMS              20
FORD               20






SCOTT과 동일한 직급을 가진 사원
SQL> SELECT * 
FROM EMP 
WHERE JOB = ( SELECT JOB FROM EMP WHERE ENAME = 'SCOTT' );

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7902 FORD       ANALYST         7566 81/12/03       3000                    20





SCOTT의 급여와 동일하거나더 많이 받는 사원명과 급여
SQL> SELECT ENAME, SAL 
FROM EMP 
WHERE SAL >= ( SELECT SAL FROM EMP WHERE ENAME = 'SCOTT' );

ENAME             SAL
---------- ----------
SCOTT            3000
KING             5000
FORD             3000






DALLAS에서 근무하는 사원의 이름, 부서 번호
SQL> SELECT ENAME, DEPTNO 
FROM EMP 
WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS' );

ENAME          DEPTNO
---------- ----------
SMITH              20
JONES              20
SCOTT              20
ADAMS              20
FORD               20






SALES 부서에서 근무하는 모든 사원의 이름과 급여
SQL> SELECT ENAME, SAL 
FROM EMP 
WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES' );

ENAME             SAL
---------- ----------
ALLEN            1600
WARD             1250
MARTIN           1250
BLAKE            2850
TURNER           1500
JAMES             950






자신의 직속상관이 KING인 사원의 이름과 급여
SQL> SELECT ENAME, SAL 
FROM EMP 
WHERE MGR = ( SELECT EMPNO FROM EMP WHERE ENAME = 'KING' );

ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450









위에 단일행 서브쿼리들이 서브쿼리의 결과가 하나의 로우에 해당해서 결과값과 = 기호로 비교한다면,
다중행 서브쿼리의 경우 IN, ALL, SOME, ANY, EXIST 등의 연산자를 활용해서 매칭시킬 수 있다.


JOB이 MANAGER인 사람이 속한 부서의 부서 번호와 부서명과 지역을 출력해보자. 먼저 SELECT 문 다음 결과로 원하는 필드명인 부서번호, 부서명, 지역을 입력하고 WHERE 문 다음 부서가 특정 사람들에게 속한 경우를 찾으므로 DEPTNO 이후 JOB이 MANAGER인 사람들의 DEPTNO를 서브쿼리로 가져와서 IN 으로 매칭시킨다.

SQL> SELECT DEPTNO, DNAME, LOC 
FROM DEPT 
WHERE DEPTNO IN ( SELECT DEPTNO FROM EMP WHERE JOB = 'MANAGER' );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO




이번에는 그룹 함수와 연계한 문제라 조금 까다롭다.
IN 연산자를 이용하여 부서별로 가장급여를 많이 받는 사원의 사원번호, 사원명, 급여, 부서번호를 출력해 보자.

먼저 SELECT 문으로 필요한 필드명을 가져오고,
WHERE 문으로 SAL가 그룹별로 최대 연봉들의 그룹과 일치하는 경우를 매치시키게 된다.
책에는 아래와 같이 설명하고 있지만,, 사실 우연히 SAL가 달라서 그렇지 만약 다른 부서에서 우연히 각 부서 최대 급여와 일치하는 경우가 생길 수도 있는 것 같다. 따라서 다소 문제가 있는 쿼리인 듯 싶다.


SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 
FROM EMP 
WHERE SAL IN ( SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO );

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7698 BLAKE            2850         30
      7788 SCOTT            3000         20
      7839 KING             5000         10
      7902 FORD             3000         20


강사님께서 다시 바로잡아주신 내용.
DEPTNO와 SAL을 가져와서 이를 NATURAL JOIN 하여 결과를 가져오는 방식.


SQL> SELECT DEPTNO, MAX(SAL) AS SAL 
FROM EMP 
GROUP BY DEPTNO;

    DEPTNO        SAL
---------- ----------
        30       2850
        20       3000
        10       5000

경   과: 00:00:00.01
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 
FROM EMP NATURAL JOIN ( SELECT DEPTNO, MAX(SAL) AS SAL FROM EMP GROUP BY DEPTNO );

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7698 BLAKE            2850         30
      7902 FORD             3000         20
      7788 SCOTT            3000         20
      7839 KING             5000         10

경   과: 00:00:00.07




ALL 함수는 다중행 서브쿼리의 결과 모두와 조건문으로 비교해서 모두 참인 경우를 찾을 수 있다.
아래는 직무가 SALESMAN인 모든 사람보다 연봉이 높은 사람의 이름과 연봉을 찾는 내용이다.

이름과 연봉을 EMP 테이블에서 가져옴을 선언하고
WHERE 문 아래에 서브쿼리를 통해 가져온 SALESMAN이 직무인 모든 사람들의 연봉과 SAL을 비교해서 더 큰경우만 출력하게 된다.

SQL> SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL( SELECT SAL FROM EMP WHERE JOB = 'SALESMAN' ) AND JOB != 'SALESMAN';

ENAME             SAL
---------- ----------
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000





ANY와 SOME 은 다중행 서브쿼리의 결과 중 하나라도 조건문으로 비교해서 참인 경우를 찾을 수 있다.
아래의 예에서 ANY와 SOME의 결과가 동일한 것을 확인할 수 있다.

SQL> SELECT ENAME, SAL
FROM EMP
WHERE SAL > ANY ( SELECT SAL FROM EMP WHERE JOB = 'SALESMAN' ) AND DEPTNO != 20;

ENAME             SAL
---------- ----------
KING             5000
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300

6 개의 행이 선택되었습니다.

SQL> SELECT ENAME, SAL
FROM EMP
WHERE SAL > SOME ( SELECT SAL FROM EMP WHERE JOB = 'SALESMAN' ) AND DEPTNO != 20;

ENAME             SAL
---------- ----------
KING             5000
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300

6 개의 행이 선택되었습니다.





Database/Oracle 관련 포스팅 더보기