[SQLD] 24. 서브쿼리

제 4절 서브쿼리

  서브쿼리(Subquery)란 하나의 SQL문 안에 포함되어 있는 또 다른 SQL 문을 말한다. 서브쿼리는 다음과 같이 메인쿼리가 서브쿼리를 포함하는 종속적인 관계에 놓여있다. 

- 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있음

- 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음

- 서브쿼리 칼럼을 표시해야 한다면 스칼라 서브쿼리, 함수 등을 사용

- 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성

- 서브쿼리는 괄호로 감싸서 사용

- 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용 가능

- 서브쿼리에서는 ORDER BY 절을 사용하지 못하고, 메인쿼리 마지막 문장에 사용 가능

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

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

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

  서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 경우의 서브쿼리를 비연관 서브쿼리라 하고, 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리를 연관 서브쿼리라고 칭한다. 

  서브쿼리는 메인쿼리 안에 포함된 종속적인 관계이기 때문에 논리적인 실행순서는 항상 메인쿼리에서 읽혀진 데이터에 대해 서브쿼리에서 해당 조건이 만족하는 지를 확인하는 방식으로 수행되어야한다. 그러나 실제 서브쿼리의 실행순서는 상황에 따라 달라질 수 있다. 

  반환되는 데이터의 형태에 따라 서브쿼리는 다음과 같이 분류된다. 

1. 단일 행 서브쿼리

  서브쿼리가 단일 행 비교연산자(=, <, >, <=, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건이하여야한다. 만약 2건 이상을 반환한다면 런타임오류가 발생하게 된다. 다음은 단일 행 서브쿼리의 예제들이다. 

  만일, 정남일 선수가 동명이인이 있었다면 오류가 발생했을 것이다. 또한 테이블 전체에 하나의 그룹함수를 적용할 떄는 그 결과값이 1건이 생성되기 때문에 단일 행 서브쿼리로서 사용가능하다. 그 예제는 아래와 같다. 

2. 다중 행 서브쿼리

  서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함꼐 사용해야한다. 그렇지 않으면 SQL문은 오류를 반환하게 된다. 다중 행 비교 연산자는 다음과 같다. 

  합집합을 뜻하는 IN과 OR, 모든 값을 만족하는 조건인 ALL, 어느 하나의 값이라 만족하는 조건을 의미하는 ANY, 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건인 EXISTS, 또한 ANY는 SOME과 같다. 

3. 다중 칼럼 서브쿼리

  다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다. 소속팀별 키가 가장 작은 사람들의 정보를 출력하는 문제를 가지고 다중 칼럼 서브쿼리를 알아보도록한다. 

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;

  WHERE(TEAM_ID, HEIGHT) IN ... 라고 작성한 SQL문처럼 다중 칼럼에 대한 서브쿼리를 수행 할 때 위와 같은 방식으로 SQL문을 작성할 수 있다. 이와 같은 쿼리문의 결과는 하나 팀에서 키가 제일 작은 선수 한 명씩만 반환된 것이 아니라 같은 팀에서 여러 명이 반환된 것을 확인 할 수 있는데, 이는 SQL Server에서는 지원되지 않는 기능이다. 

4. 연관 서브쿼리(Correlated Subquery)

  연관 서브쿼리는 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다. 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하는 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 선수명;

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

ID            경기장명

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

B01        인천월드컵경기장

B04        수원월드컵경기장

B05        서울월드컵경기장

C02        부산아시아드경기장

4개의 행이 선택되었다.

위와 같은 결과를 얻을 수 있는데, EXISTS 서브쿼리에서 보면 20120501 ~ 20120502 날짜 사이에 경기 스케줄이 있는 경기장명을 단 1개만 존재하는지 검색한 다음 그에 따른 그 경기장과 아이디를 출력한다. 메인쿼리의 결과 건수만큼 반복수행되게 된다.

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

가. SELECT 절에 서브쿼리 사용 - 스칼라 서브쿼리

SELECT 절에 사용하는 스칼라 서브쿼리(Scalar Subquery)는 한 행, 한 칼럼 만을 반환하는 서브쿼리를 말한다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다. 쓰임은 다음과 같다.

스칼라 서브쿼리는 메인쿼리의 결과 건 수만큼 반복수행된다. 

나. FROM 절에서 서브쿼리 사용 - 인라인 뷰

  FROM 절에서 사용되는 서브쿼리는 인라인 뷰(Inline View)라고 한다. FROM절에는 테이블명이 반드시 오게 되어있는데, FROM 절에 위치한 서브쿼리의 결과는 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다. 인라인 뷰는 SQL 문이 실행될 떄만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 테이블의 역할을 할 수 있으므로 FROM 절에 위치할 수 있는 것이다. 

  그래서 일반적인 뷰를 정적 뷰(Static View)라고 하고, 인라인 뷰와 같은 뷰를 동적 뷰(Dynamic View)라고도 한다.

  서브쿼리의 칼럼은 메인쿼리에서 사용할 수 없다고 했다. 그러나 인라인 뷰는 동적으로 생성된 테이블이다. 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다. 그렇기 때문에 인라인 뷰의 칼럼은 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 선수명;

  인라인 뷰에서는 ORDER BY절을 사용할 수 있다. 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 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 AS 선수명, POSITION AS 포지션, BACK_NO AS 백넘버, HEIGHT  AS 키

FROM PLAYER

WHERE HEIGHT IS NOT NULL

ORDER BY HEIGHT DESC;

위 SQL문의 결과는 5명의 선수 정보만을 추출하게 된다. 

다. 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');

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

UPDATE TEAM A

SET A.E_TEAM_NAME = (SELECT X.STADIUM_NAME

FROM STADIUM X

WHERE X.STADIUM_ID = A.STADIUM_ID);

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

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)

VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER),

'홍길동', 'K06');

6. 뷰(View)

  테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다. 뷰는 단지 뷰 정의 만을 가지고 있다. 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성하여 질의를 수행한다. 뷰는 실제 데이터를 가지고 있지 않지만, 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 한다. 

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 줄에 이미 존재하는 뷰를 참조해서 또 뷰를 생성할 수도 있다. 그럴 떈 FROM 절에 테이블 명 대신 뷰 명을 적어주면 된다. 

뷰를 사용하기 위해서 해당 뷰의 이름을 FROM 절에 적어 사용해주면 된다. 또한 뷰를 제거하기 위해서는 DROP VIEW 뷰명; 이라는 SQL 구문을 작성해주면 된다.