[DBMS] 서브쿼리


서브쿼리


1. 서브쿼리의 개념

서브쿼리는 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 의미합니다.

SQL 에 포함된 또 다른 SQL을 서브쿼리라고 합니다. 서브쿼리는 SELECT절, FROM절, WHERE절, HAVING절에 사용될 수 있습니다.

서브쿼리는 메인 쿼리가 실행되기 전에 먼저 실행되며 서브쿼리에서는 메인 쿼리의 모든 컬럼을 참조할 수 있지만 메인 쿼리에서는 서브쿼리의 컬럼을 참조할 수 없습니다.

  • 서브쿼리 개념


2. 서브쿼리가 가능한 곳은 다음과 같다.

SELECT CLAUSE

FROM CLAUSE

WHERE CLAUSE

HAVING CLAUSE

ORDER BY CLAUSE

INSERT VALUES CLAUSE

UPDATE SET CLAUSE


3. 서브쿼리의 유형

(1) 단일 행(Sing-Row) 서브쿼리 : SELECT 문장으로 부터 오직 하나의 행 만을 검색하는 질의이다.

서브쿼리에서 한 행에 대한 결과 값만 반환하는 것을 단일행 서브쿼리라고 합니다 (=, >, >=, <, <=, <>, !=)

- WHERE 절에서 사용된 서브쿼리에서 한 개의 결과 값만 반환할 경우 사용하는 연산자는 다음과 같습니다. 이 외의 연산자를 사용할 경우 에러가 발생할 수 있습니다.

연산자의미
=같음
>보다 큼
>=크가나 같음
<보다 작음
<=작거나 같음
, !=같지 않음

▶ 구문

SELECT select_list

FROM table_name

WHERE 표현식 연산자 (SELECT select_list

                       FROM table_name

                       WHERE 조건식);

표현식 연산자 : subquery는 비교 연산자 우측에 () 안에 작성한다.


▶ 예제

employee 테이블에서 gildong보다 급여를 많이 받는 사원을 출력하시오.

SELECT ename, salary

FROM employee

WHERE salary > (SELECT salary

                         FROM employee

                         WHERE ename = 'gildong') ;

 

20번 사원과 같은 부서에서 일하는 사원을 출력하시오.

SELECT ename, dno

FROM employee

WHERE dno = (SELECT dno

                   FROM employee

                   WHERE eno = 20) ;



(2) 다중 행(Multiple-Row) 서브쿼리 : SELECT 문장으로부터 하나 이상의 행을 검색하는 질의이다.

서브쿼리에서 여러 행에 대한 결과 값을 반환하는 것을 다중행 서브쿼리라고 합니다. (IN, NOT IN, ANY, ALL, EXISTS)

- 다중 행 subquery를 이용하여 데이터를 비교할 경우에는 다중 행 연산자를 사용합니다. 만약 다중 행 subquery에 단일 행 연산자를 사용한다면, 다음과 같은 오류가 발생합니다.

다중 행 연산자설명
IN(서브쿼리)서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다. (Multiple OR 조건)
비교연산자 ALL(서브쿼리)서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. 비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 모든 결과 값을 만족해야 하므로, 서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족한다. ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다.
비교연산자 ANY(서브쿼리)서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. 비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면되므로, 서브쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족한다. ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다. (SOME은 ANY와 동일함.)
EXISTS(서브쿼리)서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않는다.

▶ 예제1 (IN)

각 부서에서 급여를 가장 적게 받는 사원들을 출력하시오
SELECT dno, ename, salary
FROM employee
WHERE salary = (SELECT MIN(salary)
                      FROM employee
                      GROUP BY dno);
[ERR-31002 : A single-row subquery returns more than one row.] 단일 행 하위 질의에 2개 이상의 행이 리턴되었다. 

SELECT dno, ename, salary
FROM employee
WHERE salary IN (SELECT MIN(salary)
                       FROM employee
                       GROUP BY dno) ;

10 rows selected.  


SELECT ENAME, SAL, DEPTNO FROM EMP
WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO
FROM EMP 
WHERE SAL>=3000);

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


▶ 예제2 (ANY)

SALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제

SELECT ename, sal

FROM emp

WHERE deptno != 20

AND sal > ANY (SELECT sal 

                        FROM emp 

                        WHERE job='SALESMAN');

ENAME             SAL

---------- ----------

ALLEN            1600

BLAKE            2850

CLARK            2450


▶ 예제3 (ALL)

모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제

SELECT ename, sal

FROM emp

WHERE deptno != 20

AND sal > ALL (SELECT sal 

                       FROM emp 

                       WHERE job='SALESMAN');

ENAME             SAL

---------- --------

CLARK            2450

BLAKE            2850

KING               5000


▶ 예제4 (EXISTS)

-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우

-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 

-- 체크해야 한다.

-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 

-- DISTINCT로 중복 제거를 한다.

SELECT DISTINCT d.deptno, d.dname

FROM dept d, emp e

WHERE d.deptno = e.deptno;

 

-- EXISTS를 사용하는 Subquery로 변경

-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해 

-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.

SELECT d.deptno, d.dname

FROM dept d

WHERE EXISTS 

      (SELECT 1

        FROM emp e

        WHERE e.deptno = d.deptno);


(3)  다중 열(Multiple-Column)[다중 컬럼] 서브쿼리 : SELECT 문장으로부터 하나 이상의 컬럼을 검색하는 질의이다.

서브쿼리에서 여러 열에 대한 결과 값을 반환하는 것을 다중열 서브쿼리라고 합니다. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다. 


▶ 예제

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 

FROM PLAYER 

WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) 

                            FROM PLAYER 

                            GROUP BY TEAM_ID) 

ORDER BY TEAM_ID, PLAYER_NAME; 

다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.


(4) FROM절상의 서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용 한다.

SELECT 의 FROM 절에서 사용하는 서브쿼리를 inline view라고 합니다. view와는 달리 inline view에서 사용하는 SELECT문을 저장하지 않기 때문에 dynamic view 라고도 부릅니다.

다른 서브쿼리와는 다르게 inline view에서 질의하는 칼럼들을 메인 서브쿼리에서 사용할 수 있습니다.


▶ 예제1

사원 정보와 각 사원이 속한 부서의 평균 급여를 함께 출력하라.

SELECT e.ename, e.salary, a.avg_sal

FROM employee e, (SELECT dno, AVG(salary) avg_sal FROM employee GROUP BY dno) a

WHERE e.dno = a.dno;

ENAME             SALARY       AVG_SAL     

-----------------------------------------

HSCHOI            2000000      2150000     

JHCHOI            2300000      2150000     

HYCHOI            1700000      1340000     

KWKIM              980000      1340000     

YHBAE             4000000      2438250

 


▶ 예제2

 부서번호 20의 평균 급여보다 크고, 부서번호 20에 속하지 않은 관리자를 조회하는 예제이다.

SELECT b.empno, b.ename, b.job, b.sal, b.deptno

FROM (SELECT empno

          FROM emp  

          WHERE sal >(SELECT AVG(sal) 

                              FROM emp 

                              WHERE deptno = 20)) a, emp b

   WHERE a.empno = b.empno

   AND b.mgr is NOT NULL

   AND b.deptno != 20;

 EMPNO ENAME      JOB         SAL     DEPTNO

 ----- --------- --------- ------- ---------

  7698 BLAKE      MANAGER     2850        30

  7782 CLARK      MANAGER     2450        10


(5) SCALAR SUBQUERY

SELECT 절에서 사용하는 서브쿼리를 스칼라 서브쿼리(Scalar Subquery)라 한다.

스칼라 서브쿼리는 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말한다. 

스칼라 서브쿼리는 메인 서브쿼리의 row수 만큼 반복되어 실행이 되고, 만약 조건에 만족하는 스칼라 서브쿼리의 결과가 없다면 NULL 을 리턴하게 된다.


▶ 예제

사원 정보와 각 사원이 속한 부서의 평균 급여를 함께 출력하시오.

SELECT ename, salary, (SELECT AVG(salary) FROM employee WHERE dno = e.dno) avg_sal 

FROM employee e;

ENAME            SALARY       AVG_SAL     

-------------------------------------------

HSCHOI           2000000      2150000     

KSKIM             1800000      1800000


cp.) HAVING 절에서 서브쿼리 사용하기

HAVING 절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.

SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키 

FROM   PLAYER P, TEAM T 

WHERE  P.TEAM_ID = T.TEAM_ID 

GROUP  BY P.TEAM_ID, T.TEAM_NAME 

HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT) 

                        FROM PLAYER 

                        WHERE TEAM_ID ='K02');


cp.) UPDATE문의 SET 절에서 사용하기

UPDATE TEAM A 

SET  A.STADIUM_NAME = (SELECT X.STADIUM_NAME 

                         FROM STADIUM X 

                         WHERE X.STADIUM_ID = A.STADIUM_ID); 


cp.)  INSERT문의 VALUES절에서 사용하기

- INSERT INTO 다음에 VALUES 절을 사용하는 대신에 서브 쿼리를 사용할 수 있습니다. 

- 이렇게 하면 기존의 테이블에 있던 여러 행을 복사해서 다른 테이블에 삽입할 수 있습니다. 

- 이 때 주의할 점은 INSERT 명령문에서 지정한 컬럼의 개수나 데이터 타입이 서브 쿼리를 수행한 결과와 동일해야 한다는 점입니다. 

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID) 

VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06'); 




'Server > DBMS' 카테고리의 다른 글

[DBMS] HAVING 조건  (0) 2014.12.02
[DBMS] Group By  (0) 2014.12.02
[DBMS] NULL도 데이터이다  (0) 2014.12.02
[DBMS] 서브쿼리  (0) 2014.11.30
[DBMS] 집합연산자  (0) 2014.11.30
[DBMS] 조인  (0) 2014.11.30