[SQLD : Ⅳ. SQL 활용] 4. 서브쿼리

 - 서브쿼리는 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 말함

 - 알려지지 않은 기준을 이용한 검색을 위해 사용

 - 조인에 참여하는 모든 테이블이 대등한 관계, 참여하는 모든 테이블의 컬럼을 어느 위치에서 자유롭게 사용 가능

 - 1:1 관계의 테이블이 조인하면 레벨의 집합이 생성, 1:M 관계의 체이블을 조인하면 M 레벨의 집합 생성

   M:N 관계 테이블을 조인하면 MN 레벨의 집합이 결과로 생성

 

① 서브쿼리를 괄호로 감싸서 사용한다.

② 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다.

   단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고

   복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.

③ 서브쿼리에서는 ORDER BY를 사용하지 못한다.

   ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에

   ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.

 

서브쿼리가 SQL문에서 사용이 가능한 곳

 : SELECT 절, FROM 절, WHERE 절, HAVING 절, ORDER BY 절, INSERT문의 VALUES 절, UPDATE문의 SET 절

* 단일 행 서브쿼리

 - 서브쿼리가 단일 행 비교 연산자와 함께 사용할 때는 서브쿼리의 결과 건스가 반드시 1건 이하

 - 서브쿼리의 결과 건수가 2 이상이면 SQL문은 실행시간 오류 발생 = runtime, complie time error

 

[예제] 정남일 선수가 소속된 팀의 선수들에 대한 정보를 표시하는 문제

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID 
                 FROM PLAYER 
                 WHERE PLAYER_NAME = '정남일') 
ORDER BY PLAYER_NAME;

선수명 포지션 백넘버

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

강철 DF 3

김반 MF 14

김영수 MF 30

김정래 GK 33

김창원 DF 5

김회택 TM

꼬레아 FW 16

노병준 MF 22

51개의 행이 선택되었다.

 

 

[예제] 선수들의 평균키를 알아내는 SQL문(서브쿼리 부분)과 이 결과를 이용해서

        키가 평균 이하의 선수들의 정보를 출력하는 SQL문(메인쿼리 부분)으로 구성

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 
FROM PLAYER 
WHERE HEIGHT <= (SELECT AVG(HEIGHT) 
                 FROM PLAYER) 
ORDER BY PLAYER_NAME;

 

* 다중 행 서브쿼리

[예제] 선수들 중에서 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 
FROM TEAM 
WHERE TEAM_ID = (SELECT TEAM_ID 
                 FROM PLAYER 
                 WHERE PLAYER_NAME = '정현수') 
ORDER BY TEAM_NAME; 
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었다.

서브쿼리의 결과로 2개 이상의 행이 반환되어

단일 행 비교 연산자인 '='로는 처리가 불가능하기 때문에 에러가 반환됨

따라서 다중 행 비교 연산자로 바꾸어서 SQL문을 작성

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 
FROM TEAM 
WHERE TEAM_ID IN (SELECT TEAM_ID
                  FROM PLAYER 
                  WHERE PLAYER_NAME = '정현수') 
ORDER BY TEAM_NAME;

 

* 다중 컬럼 서브쿼리

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

 - 소속팀별 키가 가장 작은 사람들의 정보는 GROUP BY를 이용하여 찾을 수 있음

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;

 

* 연관 서브쿼리

 - 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

 - 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력

SELECT T.TEAM_NAME 팀명, M.PLAYER_NAME 선수명, M.POSITION 포지션, 
       M.BACK_NO 백넘버, M.HEIGHT 키 
FROM PLAYER M, TEAM T 
WHERE M.TEAM_ID = T.TEAM_ID AND M.HEIGHT < ( SELECT AVG(S.HEIGHT) 
                                             FROM PLAYER S 
                                             WHERE S.TEAM_ID = M.TEAM_ID 
                                                   AND S.HEIGHT IS NOT NULL GROUP BY S.TEAM_ID ) 
ORDER BY 선수명;

 - EXISTS 서브쿼리를 사용하여 '20120501' 부터 '20120502' 사이에 경기가 있는 경기장을 조회

SELECT STADIUM_ID ID, STADIUM_NAME 경기장명 
FROM STADIUM A 
WHERE EXISTS (SELECT 1 
              FROM SCHEDULE X 
              WHERE X.STADIUM_ID = A.STADIUM_ID 
                    AND X.SCHE_DATE BETWEEN '20120501' AND '20120502')

 

* 그 밖의 위치에서 사용하는 서브쿼리

 - SELECT 절에 서브쿼리 사용하기 = 스칼라 서브쿼리

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

    선수들의 정보를 출력하는 SQL문(메인쿼리 부분)과 해당 선수의 소속팀별 평균키를 알아내는 SQL문(서브쿼리 부분)

    여기서 선수의 소속팀별 평균키를 알아내는 스칼라 서브쿼리는 메인쿼리의 결과 건수만큼 반복수행

SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) 
                                       FROM PLAYER X 
                                       WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키
FROM PLAYER P

    스칼라 서브쿼리 또한 단일 행 서브쿼리이기 때문에 결과가 2건 이상 반환되면 SQL문은 오류를 반환

 

 - FROM 절에서 서브쿼리 사용하기 = 인라인 뷰

  인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰

  일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)

   K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력

SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버 
FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO 
      FROM PLAYER 
      WHERE POSITION = 'MF') P, TEAM T 
WHERE P.TEAM_ID = T.TEAM_ID 
ORDER BY 선수명;

  TOP-N 쿼리를 수행하기 위해서는 정렬 작업과 정렬 결과 중에서 일부 데이터만을 추출할 수 있는 방법이 필요

   Oracle에서는 ROWNUM이라는 연산자를 통해서 결과로 추출하고자 하는 데이터 건수를 제약

Oracle SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
       FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT 
             FROM PLAYER 
             WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC) 
       WHERE ROWNUM <= 5;
       
 SQL Server SELECT TOP(5) PLAYER_NAME AS 선수명, POSITION AS 포지션, 
                   BACK_NO AS 백넘버, HEIGHT AS 키 
            FROM PLAYER 
            WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC

  인라인 뷰에서 선수의 키를 내림차순으로 정렬한 후 메인쿼리에서 ROWNUM을 사용해서 5명의 선수의 정보만을 추출

  다른 선수 중에서 키가 192인 선수가 더 존재하더라도 해당 SQL문에서는 데이터가 출력되지 않음

  이런 데이터까지 추출하고자 한다면 분석함수의 RANK관련 함수를 사용

 

 - HAVING 절에서 서브쿼리 사용하기

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

  평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 구하는 SQL문

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')

 - UPDATE 문의 SET 절에서 사용하기

  TEAM 테이블에 STADIUM_NAME을 추가(ALTER TABLE ADD COLUMN)하였다고 가정

  TEAM 테이블에 추가된 STADIUM_NAME의 값을 STADIUM 테이블을 이용하여 변경

UPDATE TEAM A SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME 
                                    FROM STADIUM X 
                                    WHERE X.STADIUM_ID = A.STADIUM_ID);

  서브쿼리의 결과가 NULL을 반환할 경우 해당 컬럼의 결과가 NULL이 될 수 있기 때문에 주의

 

 - INSERT 문의 VALUES 절에서 사용하기

  PLAYER 테이블에 '홍길동'이라는 선수를 삽입

  PLAYER_ID의 값을 현재 사용중인 PLAYER_ID에 1을 더한 값으로 넣고자 함

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

 

* 뷰

CREATE VIEW V_PLAYER_TEAM AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, 
                                    P.TEAM_ID, T.TEAM_NAME
                             FROM PLAYER P, TEAM T
                             WHERE P.TEAM_ID = T.TEAM_ID;

선수 정보와 해당 선수가 속한 팀명을 함께 추출

CREATE VIEW V_PLAYER_TEAM_FILTER AS SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME 
                                    FROM V_PLAYER_TEAM 
                                    WHERE POSITION IN ('GK', 'MF');

 V_PLAYER_TEAM_FILTER 뷰는 선수 포지션이 골키퍼(GK), 미드필더(MF)인 선수만을 추출하고자 하는 뷰

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME 
FROM V_PLAYER_TEAM 
WHERE PLAYER_NAME LIKE '황%'

V_PLAYER_TEAM 뷰에서 성이 '황'씨인 선수만을 추출하는 SQL문

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME 
FROM (SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME 
      FROM PLAYER P, TEAM T 
      WHERE P.TEAM_ID = T.TEAM_ID) 
WHERE PLAYER_NAME LIKE '황%'

뷰를 제거하기 위해서는 DROP VIEW문을 사용

DROP VIEW V_PLAYER_TEAM;
DROP VIEW V_PLAYER_TEAM_FILTER;