오라클 기초#3 (subquery)

[ 서브쿼리]

- 하나의 SQL문장절에 포함된 또 다른 SELECT문장.
두번 질의를 해야 하는 얻을 수 있는 결과를 한번의 질의로 해결이 가능하게 하는 쿼리

- 특징

* 괄호를 묶어야 한다.

* 실행순서는 대부분 서브쿼리가 먼저 수행되고 메인쿼리가 수행된다.

* 서브쿼리는 아래와 같은 위치에 올 수 있다.
SELECT/DELETE/UPDATE 문장의 FROM절/WHERE절
INSERT 문장의 INTO절
UPDATE문장의 SET절

 

1) 단일행 서브쿼리

- 서브쿼리의 실행결과가 하나의 컬럼과 하나의 행만을 리턴해주는 쿼리. 즉 하나의 데이터만을 리턴

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

SELECT JOB FROM EMP WHERE EMPNO=7369;-- 서브쿼리

 

SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB='CLERK'; -- 메인쿼리

==> 위의 작업을 서브쿼리로 바꿔보면

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) 급여가 평균급여보다 많은 사원들의 사원번호,급여를 출력하시오.

SQL> SELECT ROUND(AVG(SAL)) FROM EMP; -- 서브쿼리

 

SQL> SELECT EMPNO,SAL FROM EMP WHERE SAL>2077; -- 메인쿼리

-->위의 작업을 서브쿼리로 바꿔보기

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

 

Q1) 'ALLEN'과 같은 부서에 근무하는 사원들의 모든 정보를 출력하세요.

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

 

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

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

 

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

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

 

Q4) 사원번호가 7369인 사원과 같은 직업을 갖고, 7369인 사원보다 많은 급여를 받는 사원을 조회하시오.

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

 

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

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

[결과]
DEPTNO  MIN(SAL)
-------- ----------
10           1300

 

2) 복수행 서브쿼리

 

- 서브쿼리의 실행결과가 여러개의 행을 리턴

- 연산자 : IN,ANY,ALL,EXISTS

 

 

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

SELECT SAL FROM EMP WHERE DEPTNO=10;-- 서브쿼리

 

SAL
----------
2450
5000
1300

==> 위와 같이 서브쿼리의 결과가 여러행인 경우는 =을 쓸 수 없다.

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

  

예2) 부서번호가 10번인 사원의 급여과 같거나 많은 급여를 받는 사원의 이름과 급여를 출력(IN연산자는 >=등과 같은 연산자 사용못함)

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

 

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

SELECT SAL FROM EMP WHERE DEPTNO=30;-- 서브쿼리

-- ALL은 모든 조건이 모두 만족해야 함

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

 

 예4) EXISTS - 데이터가 존재하면 수행

SELECT * FROM DEPT
WHERE EXISTS(SELECT *
                          FROM EMP
                       WHERE DEPTNO=30);

-- 부하직원이 있는 사원을 출력하시오.(상호관련서브쿼리)
SELECT *
 FROM EMP E1
WHERE EXISTS(SELECT * FROM EMP E2
                    WHERE E2.MGR=E1.EMPNO);

EMPNO ENAME JOB          MGR HIREDATE SAL COMM DEPTNO
------ --------- ----         ----- ---------- ---- ------- -------
7902    FORD    ANALYST     7566   81/12/03 3000 20
7698    BLAKE   MANAGER   7839   81/05/01 2850 30
7839    KING      PRESIDENT          81/11/17 5000 10

 

 

3) 복수컬럼 서브쿼리

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

 

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

SELECT ENAME,DEPTNO,COMM
FROM EMP
WHERE (SAL,COMM) IN(SELECT SAL,COMM FROM EMP WHERE DEPTNO=30);