[SQL] 서브 쿼리 (sub query)

서브 쿼리

서브 쿼리란 하나의 sql 문 안에 포함되어 있는 또 다른 sql 문을 말한다.

서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다. 

 

서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다. 

질의 결과에 서브쿼리 컬럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리 등을 사용해야 한다. 

 

서브쿼리 이용시 주의사항

1. 서브쿼리를 괄호로 감싸서 사용한다.
2. 서브쿼리는 단일행(single row) 또는 복수행(multiple row)비교 연산자와 함께 사용 가능하다. 단일행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다. 
3. 서브쿼리에서는 ORDER BY 를 사용하지 못한다. ORDER BY는 SELECT 절에서 오직 한 개만 올 수 있기 때문에 메인쿼리 마지막 문장에 위치해야 한다. (라고 쓰여있지만, 실제로 사용 가능한 것 같음....😰)

 

서브쿼리 사용이 가능한 곳

• SELCT
• FROM
• WHERE
• HAVING
• ORDER BY 
• INSERT의 VALUES 절
• UPDATE의 SET 절

 

서브쿼리의 종류는 동작하는 방식이나 반환되는 데이터의 형태에 따라 분류할 수 있다. 동작하는 방식에 따라 서브쿼리를 분류하면 두 가지로 나눌 수 있다. 

 

동작 방식에 따른 서브쿼리 분류

• Un-Correlated (비 연관) 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태. 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용된다. 
• Correlate(연관) 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용된다. 

 

반환되는 데이터 형태에 따른 서브쿼리 분류

• Single Row 서브쿼리 (단일행 서브쿼리) : 서브쿼리 실행 결과가 항상 1건 이하. 딘일행 서브쿼리는 단일 행 비교 연산자( =, <. > <=, >=, <>) 와 함께 사용된다. 
• Multi Row 서브쿼리( 다중행 서브쿼리): 서브쿼리 실행 결과가 여러 건. IN, ALL, ANY, SOME, EXISTS 와 함께 사용된다. 
• Multi Column 서브쿼리 (다중 컬럼 서브쿼리): 서브쿼리의 실행 결과로 여러 컬럼을 반환한다. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다. 

 

단일행 서브 쿼리 

=, <. > <=, >=, <>와 함께 사용할 때는 서브쿼리 결과가 반드시 1건 이하여야 한다. 만약, 서브쿼리 결과 건수가 2건 이상을 반환하면 SQL문은 실행 시간 오류가 발생한다.

 

단일 행 서브쿼리 예로 '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 표시하는 예시

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

정남일 선수의 소속팀을 알아내는 서브쿼리가 먼저 수행되어 정남일 선수의 소속팀 코드가 반환된다. 

메인쿼리는 서브쿼리에서 반환된 결과를 이용해서 조건을 만족하는 선수들의 정보를 출력한다. 

만약, 정남일 선수가 동명이인 이었다면 2건 이상의 결과가 반환되어 오류가 발생할 ㅓㅅ이다. 

 

키가 평균 이하인 선수를 출력하는 예시

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

 

다중행 서브쿼리

서브쿼리 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자 IN, ALL, ANY, SOME  과 함께 사용해야 한다. 그렇지 않으면 오류가 발생한다.

 

다중행 비교 연산자는 다음과 같다. 

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

 

'정현수' 선수가 소속된 팀을 찾는 예시 (그 동명이인이다)

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

 

 

다중 컬럼 서브쿼리

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

 

소속팀별 키가 가장 작은 사람들의 정보를 출력하는 예시

SELECT  TEAM_ID 팀코드, PLAYVER_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 ; 

서브쿼리 결과값으로 소속팀코드(TEAM_ID) 와 소속팀별로 가장 작은 키를 의미하는 MIN(HEIGHT) 라는 두 개의 컬럼을 반환했다. 

메인쿼리에서는 조건절 TEAM_ID와 HEIGHT컬럼을 괄호로 묶어서 서브쿼리 결과와 비교하여 원하는 결과를 얻었다.

실행 결과는 하나 팀에서 키가 제일 작은 선수 한 명씩만 반환되는 것이 아니라, 같은 팀에서 여러 명이 반환된다. 동일 팀 내에서 조건을 만족하는 선수가 여러명이 존재하기 때문이다. 

(sql server에서는 지원하지 않는 기능이다)

 

 

 

연관 서브쿼리

Correlated Subquery 는 서브쿼리 내에 메인쿼리 컬럼이 사용된 서브쿼리다.

선수 자신이 속한 팀의 평균 키보다 작은 선수들을 출력하는 예시

SELECT  T.TEAM_NAME 팀명, M.PLAYVER_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 사이에 경기가 있는 경기장을 조회하는 쿼리.

EXISTS는 아무리 조건을 만족하는 건이 여러 건이라도 1건만 찾으면 추가 검색을 진행하지 않는다. 

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.SCHD_DATE BETWEEN '20120501' AND '20120502' ) ; 

 

 

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

SELECT 절에서 서브쿼리 사용

select 절에서 사용하는 서브쿼리인 스칼라 서브쿼리 (scalar subquery).

스칼라 서브쿼리는 한 행, 한 컬럼 만을 반환하는 서브쿼리를 말한다. 

스칼라 서브쿼리는 컬럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다. 

 

선수의 소속팀별 평균키를 알아내는 스칼라 서브쿼리

SELECT PLAYER_NAEM, HEIGHT, ROUND( (SELECT AVG(HEIGHT)
                                    FROM   PLAYER X
                                    WHERE X.TEAM_ID = P.TEAM_ID), 3 ) 팀 평균키
FROM   PLAYER P ; 

스칼라 서브쿼리는 단일행 서브쿼리라서 결과가 2건 이상이면 오류가 생긴다.

 

FROM 절에서 서브쿼리 사용

FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View) 라고 한다. FROM 절에는 테이블명이 오도록 되어 있으므로 서브쿼리 결과를 마치 테이블인 것처럼 이용할 수 있다. 하지만 서브쿼리 결과는 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되는건 아니다. 그래서 일반적인 뷰를 정적 뷰(Static view)라고 하고 인라인 뷰를 동적 뷰(Dynamic view)라고 하기도 한다. 

인라인 뷰는 테이블명이 올 수 있는 곳에서 사용될 수 있다. 

인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다. 

 

포지션이 미드필더인 선수들의 소속팀명, 선수 정보를 출력하는 예시

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

인라인 뷰에서는 order by 절을 이용할 수 있다. 

인라인 뷰에서 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것은 TOP-N 쿼리라고 한다. TOP-N쿼리를 수행하기 위해서는 정렬 작업과 정렬 결과 중에서 일부 데이터만을 추출할 수 있는 방법이 필요하다. 

오라클에서는 ROWNUM 이라는 연산자를 통해서 결과로 추출하고자 하는 데이터 건수를 제약할 수 있다. 

-- 오라클
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, POSITION, BACK_NO, HEIGHT
FROM      PLAYER
WHERE     HEIGHT IS NOT NULL
ORDER BY HEIGHT DESC ; 

선수의 키를 내림차순(큰 순서대로) 정렬한 후 메인쿼리에서 rownum 을 사용해서 5명의 선수 정보만을 추출했다. 

모든 선수들 중에서 키가 큰 5명의 선수를 출력한 것이다. 

 

 

HAVING 절에서 서브쿼리 사용

SELECT   P.TEAM_ID, T.TEAM_ID, 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   PLAYVER
                          WHERE  TEAM_ID = 'K02') ;

 

 

UPDATE 문의 SET 절에서 서브쿼리 사용

TEAM 테이블에 STADIUM_NAME 을 추가한 상태이고,  STADIUM_NAME 값을 변경하고자 하는 예시

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

서브쿼리를 사용한 변경 작업을 할 때 서브쿼리 결과가NULL 을 반환하면 해당 컬럼의 결과가 NULL이 될 수 있기 때문에 주의하자. 

 

 

INSERT 문의 VALUES 절에서 서브쿼리 사용

player 테이블에 '홍길동' 선수를 삽입한다. 

이 때 , 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'); 

 

 

 

 

 

kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.