[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 2장. SQL 활용 - 4. 서브쿼리(Subquery)

서브쿼리(Subquery)

• 서브쿼리(Subquery)란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.
• 서브 쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다. 서브쿼리는 아래 그림과 같이 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
  
• 서브쿼리는 메인쿼리 의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
• 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리(Scalar Subquery) 등을 사용해야 한다.
  • 조인은 집합간의 곱(Product)의 관계이다.
    • 1:1 관계의 테이블이 조인하면 1(= 1 * 1) 레벨의 집합이 생성
    • 1:M 관계의 테이 블을 조인하면 M(= 1 * M) 레벨의 집합이 생성
    • M:N 관계의 테이블을 조인하 면 MN(= M * N) 레벨의 집합이 결과로서 생성된다.
    • 예를 들어, 조직(1)과 사원(M) 테이블 을 조인하면 결과는 사원 레벨(M)의 집합이 생성된다.
  • 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.
    • 예를 들어, 메인쿼리로 조직(1), 서브쿼리로 사원(M) 테이블을 사용하면 결과 집합은 조직(1) 레벨이 된다.

서브쿼리 사용시 주의사항

• 서브쿼리를 괄호로 감싸서 사용한다.
• 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다.
  • 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
• 서브쿼리에서는 ORDER BY를 사용하지 못한다.
  • ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.
• 서브쿼리가 SQL문에서 사용이 가능한 곳은 다음과 같다.
  • SELECT, FROM, WHERE, HAVING, ORDER BY 절
  • INSERT 문의 VALUES 절
  • UPDATE의 SET 절
  • DELETE 문 사용 불가

서브 쿼리 분류

• 동작하는 방식에 따른 분류
  
• 반환되는 데이터의 형태에 따른 분류
  

단일 행 서브쿼리

• 서브쿼리의 실행 결과가 반드시 1건 이하인 서브 쿼리
• 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용
• 만약, 서브쿼리의 결과 건수가 2건 이상을 반환하 면 SQL문은 실행시간(Run Time) 오류가 발생

예제 1

• '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 표시해보자.
  
• 정남일 선수의 소속팀을 알아내는 서브쿼리가 먼저 수행되어 정남일 선수의 소속팀 코드가 반환된다.
• 메인쿼리는 서브쿼리에서 반환된 결과를 이용해서 조건을 만족하는 선수들의 정보를 출력한다.
• SQL문으로 작성하면 다음과 같다.

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

예제 2

• 선수들 중에서 키가 평균 이하인 선수들의 정보를 출력해보자.
  

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

다중 행 서브쿼리

• 서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다.
• 다중행 비교 연산자는 다음과 같다.
  

예제

• '정현수'란 이름을 가진 선수가 두 명이 존재할 때, ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력하는 서브쿼리를 작성해보자.

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;

연관 서브쿼리

• 연관 서브쿼리(Correlated Subquery)는 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다.
• EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다.
  • EXISTS 서브쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색 을 진행하지 않는다.

예제 1

• 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하는 SQL문을 연관 서브쿼리를 이용해서 작성해보자.

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 선수명;

예제 2

• 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 절에 서브쿼리 사용하기

• SELECT 절에서 사용하는 서브쿼리를 스칼라 서브쿼리(Scalar Subquery)라고 한다.
• 스칼라 서브쿼리(Scalar Subquery)는 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말한다.
  • 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
• 스칼라 서브쿼리 또한 단일 행 서브쿼리이기 때문에 결과가 2건 이상 반환되면 SQL문은 오류를 반환한다.

예제

• 스칼라 서브쿼리를 사용해서 선수 정보와 해당 선수가 속한 팀의 평균 키를 함께 출력해보자.
  
• 선수들의 정보를 출력하는 SQL문(메인쿼리 부분)과 해당 선수의 소속팀별 평균키를 알아내는 SQL문(서브쿼리 부분)으로 나누어 SQL을 작성하면 된다.

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

FROM 절에서 서브쿼리 사용하기

• FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다.
• 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.
  • 따라서 일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다.
• 인라인 뷰는 테이블 명이 올 수 있는 곳에서 사용할 수 있다.
  • 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다.
  • 그렇기 때문에 인라인 뷰의 칼럼은 SQL문 자유롭게 참조할 수 있다.

예제 1

• K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력 하고자 한다.
• 인라인 뷰를 활용해서 SQL문을 만들어 보자.

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 선수명;

예제 2

• 인라인 뷰에서는 ORDER BY절을 사용할 수 있다.
  • 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 한다.
    • TOP-N 쿼리를 수행하기 위해서는 정렬 작업과 정렬 결과 중에서 일부 데이터만을 추출할 수 있는 방법이 필요하다.
    • Oracle에서는 ROWNUM이라는 연산자를 통해서 결과로 추출하고자 하는 데이터 건수를 제약할 수 있다.
• 모든 선수들 중에서 가장 키가 큰 5명의 선수를 출력해보자.
• 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

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

• 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 테이블을 이용하여 변경하고자 할 때 다음과 같이 SQL문을 작성할 수 있다.

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을 더한 값으로 넣고자 한다.
• 다음과 같이 SQL문을 SQL문을 작성할 수 있다.

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

뷰(View)

• 테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다.
  • 뷰는 단지 뷰 정의(View Definition)만을 가지고 있다.
  • 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다.
• 뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 한다.
• 뷰는 다음과 같은 장점을 갖는다.
  
• 뷰는 다음과 같이 CREATE VIEW문을 통해서 생성할 수 있다.
• 아래 뷰는 선수 정보와 해당 선수가 속한 팀명을 함께 추출하는 것이다.
  • 뷰의 명칭은 'V_PLAYER_TEAM'이다.

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;

• 뷰는 테이블뿐만 아니라 이미 존재하는 뷰를 참조해서도 생성할 수 있다.
  • 아래 V_PLAYER_TEAM_FILTER 뷰는 이미 앞에서 생성했던 V_PLAYER_TEAM 뷰를 기반으 로 해서 생성된 뷰다.

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

• 뷰를 사용하기 위해서는 해당 뷰의 이름을 이용하면 된다.
  • 뷰를 사용하는 방법은 다음과 같다.

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

• 이것은 V_PLAYER_TEAM 뷰에서 성이 '황'씨인 선수만을 추출하는 SQL문이다.
  • 뷰를 사용하면 DBMS가 내부적으로 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;