[ORACLE] 서브쿼리(여기부터 복습해야함)

서브쿼리

• 하나의 SQL문장절에 포함된 또 다른 SELECT문장.
• 두 번 질의를 해야 얻을 수 있는 결과를 한번의 질의로 해결이 가능하게 하는 쿼리
• 서브쿼리 실행 후 서브쿼리 결과를 메인쿼리로 전해주면 그 결괏값을 기다리고 있던 메인쿼리가 실행
• 특징

1. 서브쿼리 부분은 WHERE 절 연산자 오른쪽에 위치해야 하며 반드시 괄호를 묶어야 한다.
2. 특별한 경우(TOP-N절)을 제외하고는 서브쿼리 절에 ORDER BY절이 올 수 없다.

1. 단일행 서브쿼리

• 서브쿼리의 실행결과가 하나의 행만을 리턴해 주는 쿼리. 즉 하나의 데이터만을 리턴
• WHERE절에서 사용되는 연산자 : =, <>(같지 않다), >, >=, <, <=

예제&퀴즈

예1) 사원번호가 7369인 사원과 같은 직업을 갖는 사원들의 사원번호,이름,직업을 출력하시오.

SELECT JOB FROM EMP WHERE EMPNO=7369; --서브쿼리
SELECT EMPNO ,ENAME,JOB FROM EMP WHERE JOB='CLERCK'; --메인쿼리

서브쿼리를 사용해보면

SELECT EMPNO ,ENAME,JOB 
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7369);

     EMPNO ENAME      JOB      
---------- ---------- ---------
      7369 SMITH      CLERK    
      7900 JAMES      CLERK    
      7934 MILLER     CLERK 

예2) 사원급여가 평균급여보다 많은 사원들의 사원번호와 급여를 조회하시오.

SELECT EMPNO,SAL
FROM EMP
WHERE SAL>(SELECT AVG(SAL) FROM EMP);

     EMPNO        SAL
---------- ----------
      7566       2975
      7698       2850
      7782       2450
      7839       5000
      7902       3000
      1001       3000
      1004       3200
      1003       3500

Q1) 'ALLEN'과 같은 부서에 근무하는 사원들의 모든 정보를 조회하시오.

SELECT * FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='ALLEN');

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7900 JAMES      CLERK           7698 81/12/03        950                    30

심화문제(문제5)

Q1) 'MARTIN'과 같은 급여를 받는 사원의 이름,사원번호,급여를 출력해 보세요.

SELECT ENAME,EMPNO,SAL
FROM EMP
WHERE SAL IN(SELECT SAL FROM EMP WHERE ENAME='MARTIN');

ENAME           EMPNO        SAL
---------- ---------- ----------
WARD             7521       1250
MARTIN           7654       1250

Q2) 최고급여를 받는 사원의 모든 정보를 출력하세요.

SELECT * FROM EMP
WHERE SAL=(SELECT MAX(SAL) FROM EMP);

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT            81/11/17       5000                    10

Q3) 사원번호가 7369인 사원과 같은 직업을 갖고, 7369인 사원보다 많은 급여를 받는 사원의 모든정보를 조회하세요.

SELECT * FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7369)
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7369);

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

WHERE절 조건이 여러개면 AND, OR 로 묶어주기

Q4) 부서별 최소급여가 30번부서의 최소급여보다 많은 부서번호와 해당 부서의 최소급여를 출력하세요.

SELECT DEPTNO,MIN(SAL) 최소급여
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

    DEPTNO       최소급여
---------- ----------
        10       1300

WHERE (X) HAVING (O)
WHERE 절에는 그룹함수를 쓸 수 없음!!
그룹함수 => MIN(SAL)

Q5) 'JONES'와 같은 부서에 근무하는 모든 사원의 부서번호,사원번호,사원이름,급여를 출력하시오.

SELECT DEPTNO,EMPNO,ENAME,SAL
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='JONES');

    DEPTNO      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
        20       7369 SMITH            1320
        20       7566 JONES            2975
        20       7902 FORD             3000

2. 복수행 서브쿼리

• 서브쿼리의 실행결과가 여러개의 행을 리턴
• WHERE절에서 사용되는 연산자 : IN(같은 값),ANY(둘 중 하나 조건 만족),ALL(모든 조건 만족)
• =,>,>=,<,<=,<> 연산자는 사용할 수 없음

예제

예1) 부서번호가 10번인 사원들의 급여와 같은 급여를 받는 사원들의 이름과 급여를 출력하시오.

SELECT ENAME,SAL FROM EMP WHERE DEPTNO=10; --서브쿼리 (질의 결과가 여러개의 행이 나옴)

--서브쿼리 사용해 보기
SELECT ENAME,SAL
FROM EMP
WHERE SAL=ANY(SELECT SAL FROM EMP WHERE DEPTNO=10);

--또는
SELECT ENAME,SAL
FROM EMP
WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=10);

ENAME             SAL
---------- ----------
CLARK            2450
KING             5000
MILLER           1300
이디비           1000
이씨             1000
홍               1000

SELECT ENAME,SAL
FROM EMP
WHERE SAL=ANY(SELECT SAL FROM EMP WHERE DEPTNO=10);
-- WHERE절 오류 : 복수형 서브쿼리에는 =,>,>= 등의 연산사 불가

예2) 부서번호가 10번인 사원들의 급여와 같거나 많은 급여를 받는 사원의 이름과 급여를 출력하시오.

SELECT ENAME,SAL
FROM EMP
WHERE SAL>=ANY(SELECT SAL FROM EMP WHERE DEPTNO=10);

ENAME             SAL
---------- ----------
KING             5000
김태연           3500
임윤아           3200
김자바           3000
FORD             3000
JONES            2975
...

예3) 30번 부서의 가장 많은 급여보다 더 많은 급여를 받는 사원번호,급여를 조회하시오.

SELECT EMPNO,SAL
FROM EMP
WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);

     EMPNO        SAL
---------- ----------
      7566       2975
      7839       5000
      7902       3000

Q1) 10번 부서사원들중에 20번 부서의 사원과 같은 직업을 갖는 사원의 사원번호,직업,입사일을 조회하시오.

SELECT EMPNO,JOB,HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO =20);

SELECT EMPNO,JOB,HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB=ANY(SELECT JOB FROM EMP WHERE DEPTNO =20);

     EMPNO JOB       HIREDATE
---------- --------- --------
      7934 CLERK     82/01/23
      7782 MANAGER   81/06/09

Q2) 급여가 30번 부서의 최저급여보다 낮은 사원의 사원번호,이름,급여를 조회하시오.

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL<ALL(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20);

3. 복수컬럼 서브쿼리

• 서브쿼리의 실행결과가 여러개의 컬럼과 여러개의 행을 리턴해
  주는 쿼리

예제

예1) 부서번호가 30번인 사원들의 급여와 커미션이 같은 사원들의 이름과 급여,커미션을 조회하시오.

4. TOP-N절

• ROWID,ROWNUM
• 오라클에서 테이블을 생성하면 자동으로 생성되는 컬럼
• ROWID : ROW의 고유번호(중간에 ROW를 삭제해도 변하지 않음)
• ROWNUM : 행의 인덱스번호(중간에 ROW를 삭제하면 변함), 행의 일련번호

DESC EMP; --테이블 구조 보는 명령어
ROLLBACK; --작업취소

예제&퀴즈

예1) EMP테이블에서 급여가 가장 낮은 5명의 사원번호,이름,급여를 조회하시오.

SELECT * FROM (
SELECT EMPNO,ENAME,SAL
FROM EMP
ORDER BY SAL ASC
) WHERE ROWNUM<6;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7900 JAMES             950
      7521 WARD             1250
      7654 MARTIN           1250
      7934 MILLER           1300

Q1) 입사일이 가장 빠른 사원 3명의 사원번호,이름,입사일 조회해 보세요.

SELECT * FROM (
SELECT EMPNO,ENAME,HIREDATE
FROM EMP
ORDER BY HIREDATE ASC
) WHERE ROWNUM<4;

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7369 SMITH      80/12/17
      7499 ALLEN      81/02/20
      7521 WARD       81/02/22

Q2) 부서별 월급총액이 가장 많은 순서대로 2위까지 해당하는 부서와 월급총액을 구하세요.

SELECT DEPTNO,SUMSAL FROM(
SELECT DEPTNO,SUM(SAL) SUMSAL
FROM EMP
GROUP BY DEPTNO
ORDER BY SUMSAL DESC
) WHERE ROWNUM<3;

Q3) EMP테이블에서 인원수가 가장 많은 JOB과 인원수를 조회해 보세요.(ROWNUM사용)

SELECT * FROM(
SELECT JOB,COUNT(*) 인원수
FROM EMP
GROUP BY JOB
ORDER BY 인원수 DESC
) WHERE ROWNUM=1; --WHERE ROWNUM<2;

5. 스칼라서브쿼리

• SELECT 절에 오는 서브쿼리
• 조회되는 데이터의 양이 적을 경우는 JOIN보다 수행속도가 빠르다.
• 스칼라서브쿼리는 결과값이 행이 하나 열이 하나인 경우만 가능하다.

예제&퀴즈

예1) 사원번호,사원이름,부서번호,부서이름을 조회하시오.

SELECT EMPNO,ENAMEMDEPTNO,
(SELECT

Q1) 사원번호,이름,직업,부서번호,근무지역(LOC)를 조회하세요.