2.4 서브쿼리

서브쿼리 (Subquery)

: 하나의 SQL문안에 포함되어 있는 또 다른 SQL문

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

: 메인쿼리가 서브쿼리를 포함하는 종속적인 관계

 

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

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

 

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

 

▷ 서브쿼리 사용시 주의사항

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

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

단일행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.

- 서브쿼리에서는 ORDER BY를 사용하지 못함 (메인쿼리의 마지막 문장에 위치)

 

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

- SELECT 절

- FROM 절

- WHERE 절

- HAVING 절

- ORDER BY 절

- INSERT 문의 VALUES 절

- UPDATE문의 SET 절

 

▷ 동작하는 방식에 따른 서브쿼리 종류

- Un-Correlated 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리. 메인쿼리에 값을 제공하기 위한 목적

- Correlated 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리. 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용

 

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

- Single row 서브쿼리 : 서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리

- Multi row 서브쿼리 : 서브쿼리의 실행결과가 여러 건인 서브쿼리 (IN, ALL, ANY, SOME, EXISTS와 함꼐 쓰임)

- Multi column 서브쿼리 : 서브쿼리의 실행 결과로 여러 칼럼을 반환. 메인쿼리의 조건절어ㅔ 여러 칼럼 동시에 비교 가능. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼 위치가 동일해야 함

 

 

1. 단일 행 서브 쿼리

: 서브쿼리가 단일 행 비교 연산자 (=,<,<=,>,>=<>)와 함께 사용할 떄는 서브쿼리의 결과 건수가 반드시 한건 이하이어야 한다.

  (초과 시 오류 발생)

 

예제] SELECT PLAYER_NAME, POSITION, BACK_NO

FROM PLAYER

WHERE TEAM_ID = (SELECT TEAM_ID

    FROM PLAYER

    WHERE PLAYER_NAME='정남일')

ORDER BY PLAYER_NAME;

 

2. 다중 행 서브 쿼리

: 서브쿼리의 결과가 2건 이상 반환 될 수 있다면 반드시 다중 행 비교 연산자 (IN, ALL, 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;

 

4. 연관 서브쿼리

: 연관 서브쿼리는 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

 

예제] 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;

 

예제] SELECT STADIUM_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')

 

=> 20120501과 20120502사이에 경기가 있는 스타디움 출력

 

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

 

가. SELECT 절에 서브쿼리 사용하기 (스칼라 서브쿼리)

: 스칼라 서브쿼리는 한 행, 한 칼럼만을 반환하는 서브쿼리

  메인쿼리의 결과 건수만큼 반복수행

 

예제] SELECT PLAYER_NAME, HEIGHT (SELECT AVG(HEIGHT)

    FROM PLAYER X

    WHERE X.TEAM_ID = P.TEAM_ID)

   FROM  PLAYER P

 

나. FROM 절에서 서브쿼리 사용하기 (인라인 뷰)

: 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 일반적인 뷰를 정적 뷰(STATIC VIEW)라고 하고 인라인 뷰를 동적 뷰(DYNAMIC VIEW)라고도 한다.

서브쿼리의 칼럼은 메인쿼리에서 사용할 수 없다고 했지만 인라인 뷰는 동적으로 생성된 테이블이므로 자유롭게 참조 가능

 

예제] 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 1;

 

인라인 뷰에서는 ORDER BY 절을 사용할 수 있다. 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 한다. 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, POSITION, BACK_NO, HEIGHT

   FROM PLAYER

   WHERE HEIGHT IS NOT NULL

   ORDER BY HEIGHT DESC

=> 마지막 5번째와 동일한 키의 선수가 존재하더라도 출력되지 않음 (이런 데이터를 추출하고자 하는 경우에는 RANK 함수를 사용)

 

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

 

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

예제] 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 절에서 사용하기

예제] INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID)

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

 

 

6. 뷰(VIEW)

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

 

▷ 뷰 사용의 장점

- 독립성 : 테이블 구조가 변겨오디어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다

- 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 자주 사용하는 SQL문을 뷰로 이용하면 편리하게 사용 가능

- 보안성 : 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

 

생성예제]

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