[Oracle] 서브쿼리 -1-

---

2015. 7. 13. 10:41 Naver Blog 백업용

---

서브쿼리

 

- 메인쿼리 내부에 있는 또다른 쿼리

- 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장

- 비교 연산자(<,>,=)의 오른쪽에 기술 -> 반드시 행/컬럼이 하나씩이어야함

- 괄호안에 넣어야함

 

- 예제 1) SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력

SELECT ENAME,DEPTNO 

FROM EMP 

WHERE DEPTNO = (SELECT DEPTNO 

  FROM EMP 

  WHERE ENAME='SCOTT'); = 서브쿼리

 

- 예제 2) 급여의 평균보다 급여를 많이 받는 사람

SELECT ENAME,SAL

FROM EMP

WHERE SAL > ( SELECT AVG(SAL)

 FROM EMP);

 

 

서브쿼리의 종류

 

 

1. 단일행 서브쿼리 2. 다중행 서브쿼리 3. 다중컬럼 서브쿼리 4. 상관형 서브쿼리 4. FROM절에 사용하는 서브쿼리 = 인라인 뷰(INLINE VIEW)

 

 

1. 단일행 서브쿼리

 

- 서브쿼리의 결과가 하나의 행과 하나의 컬럼인 경우

 

- 예제 1) SALES 부서에서 근무하는 모든 사원의 이름과 급여를 출력

SELECT E.ENAME, E.SAL

FROM EMP E, DEPT D

WHERE D.DNAME = (SELECT DNAME

FROM DEPT

WHERE DNAME = 'SALES');

 

SELECT ENAME, SAL

FROM EMP

WEHRE DEPTNO = ( SELECT DEPT

FROM DEPT

WHERE DNAME='SALES');

 

- 예제 1) 자신의 직속상관이 KING인 사원의 이름과 급여를 출력해 보십시오.

SELECT ENAME,SAL

FROM EMP

WHERE MGR = ( SELECT EMPNO

 FROM EMP

 WHERE ENAME = 'KING' );

 

 

2. 다중행 서브쿼리

 

- IN / ANY SOME / ALL / EXIST 등의 다중행 연산자와 함께 사용한다.

 

- 예제 1) 연봉을 3000 이상 받는 사원이 소속된 부서에 근무하는 사원들의 정보를 출력

 

SELECT * 

FROM EMP 

WHERE DEPTNO = ( SELECT DEPTNO 

FROM EMP 

WHERE SAL>=3000 ); 

-> single-row subquery returns more than one row의 오류가 나옴

   ★★ 결과가 다중행이기 때문에

 

SELECT * 

FROM EMP 

WHERE DEPTNO IN ( SELECT DEPTNO 

FROM EMP 

WHERE SAL>=3000 ); 

 

-> ★ IN 연산자로 해결함!!! >_<

 

- 예제 2) IN / ANY / ALL

 

SELECT ENAME, DEPTNO, SAL 

FROM EMP 

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

 = DEPTNO = 10 인 SAL = 1300, 2450, 5000 값들이 출력

 

SELECT ENAME, DEPTNO, SAL 

FROM EMP 

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

= 1300, 2450, 5000 사이의 값들은 모두 출력됨

 

SELECT ENAME, DEPTNO, SAL 

FROM EMP 

WHERE SAL >= ALL(SELECT SAL FROM EMP WHERE DEPTNO=10);

= 5000 (최대값) 만 출력됨

 

P. 255) 12번 13번 풀기

 

12번 ) 직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다.

 

SELECT ENAME, SAL

FROM EMP

WHERE SAL >= ALL(SELECT SAL FROM EMP WHERE JOB='SALESMAN' AND DEPTNO <> 20);

 

OR

 

SELECT ENAME, SAL

FROM EMP

WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN' AND DEPTNO<>20);

 

13번 ) 직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다.

 

SELECT ENAME, SAL

FROM EMP

WHERE SAL >= ANY(SELECT SAL FROM EMP WHERE JOB='SALESMAN' AND DEPTNO <> 20);

 

 

각 부서의 최대 급여를 받는 사람의 사원명, 부서코드, 급여출력

 

SELECT DEPTNO, ENAME, SAL

FROM EMP

WGERE SAL IN ( SELECT MAX(SAL)

  FROM EMP

  GROUP BY DEPTNO); 
 

TO!!!

 

SELECT DEPTNO, ENAME, SAL

FROM EMP

WGERE (DEPTNO,SAL) IN ( SELECT DEPTNO, MAX(SAL)

  FROM EMP

  GROUP BY DEPTNO);

 

 

3. 다중컬럼 서브쿼리

 

- 두 개 이상의 컬럼과 서브쿼리의 결과를 비교해야 할 경우 사용하는 서브쿼리

- 형식

: WHERE절 안에서 (컬럼1, 컬럼2, ...) IN (서브쿼리) 

: 메인쿼리의 비교할 컬럼 순서와 서브쿼리의 비교할 컬럼 순서가 동일해야함

: in 연산자를 이용하면 where절에 조건을 두개 이상 정의해야할 내용을 한번에 비교하도   록 설정이 가능

: SELECT EMPNO,ENAME,DEPTNO,SAL 

  FROM EMP WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) 

FROM EMP 

GROUP BY DEPTNO);

 

4. 상관형 서브쿼리

 

= 상호연관 서브쿼리

 

- 서브쿼리를 실행할 때 메인쿼리로부터 값을 전달받아서 실행을 완료하고, 값을 메인으로 넘김

  MAIN 쿼리의 컬럼을 SUB 쿼리에서 사용하는 경우

  MAIN 쿼리의 한 레코드에서 SUB 쿼리가 한 번 씩 실행이 된다 

  SUB 쿼리의 결과가 매번 다른 경우 사용

  서브쿼리에서는 메인쿼리의 컬럼을 사용할 수 있지만, 메인쿼리에서는 서브쿼리의 컬럼 사용 X

 

- 실행흐름

1. MAIN쿼리에서 필요한 값을 가져온다
2. 가져온 값을 이용해서 서브쿼리를 실행한다
3. 서브쿼리 결과를 메인쿼리에 넘겨준다
4. 메인쿼리에서 서브쿼리 결과를 이요해서 WHERE를 적용
5. 1번부터 4번까지 모든 레코드에 반복 실행

 

- 사용방법 

SELECT 컬럼명

FROM 테이블명 메인쿼리의ALIAS

WHERE 비교컬럼 = ( SELECT 컬럼명

FROM 테이블명 서브쿼리의 alias

WHERE 비교컬럼 )

- 예시

SELECT FIRST_NAME, SALARY, DEPARTMENT_ID, HIRE_DATE

FROM EMPLOYEES MAIN

WHERE SALARY > (SELECT AVG(SALARY)

FROM EMPLOYEES SUB

 

WHERE SUB.DEPARTMENT_ID = MAIN.DEPARTMENT_ID)

 

-> 메인에서 값을 넘겨줄 때 많은 데이터의 이동이 있다.

 

5. FROM 절에 사용하는 서브쿼리 = INLINE VIEW

 

- 서브쿼리를 FROM절 / SELECT절 / HAVING절 / INSERT-UPDATE-DELETE문 모두 사용 가능

- 하나의 테이블을 이용해서 작업을 하는 경우 테이블의 양이 많다면,

  필요한 부분만 따로 가상의 테이블을 만들어서 실행할 수 있고 ( FROM절에서 서브쿼리 활용 )

  이렇게 작업하면 효율적으로 작업할 수 있다.

- FROM절에 기술한 서브쿼리를 인라인뷰(INLINE VIEW)라고 한다.

- 반드시 ALIAS를 줘야함

 

- 예시: 가상의 테이블을 만들어서 JOIN하겠다!

SELECT E.ENAME, E.SAL, E.DEPTNO, E.HIREDATE

FROM EMP E, (SELECT DEPTNO, AVG(SAL) AVGSAL 

FROM EMP

GROUP BY DEPTNO) MYVIEW

WHERE E.DEPTNO = MYVIEW.DEPTNO

  AND E.SAL > MYVIEW.AVGSAL;