2.2.4 제4절 서브쿼리

•  서브쿼리(Subquery) 란
  • 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.
  • 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
  • 서브쿼리는 메인쿼리 안에 포함된 종속적인 관계이기 때문에 논리적인 실행순서는 항상 메인쿼리에서 읽혀진 데이터에 대해 서브쿼리에서 해당 조건이 만족하는지를 확인하는 방식으로 수행되어야 한다.
• 서브쿼리 사용 시 주의점
  1. 서브쿼리를 괄호로 감싸서 사용한다.
  2. 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다.
     • 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
  3. 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 마짐가 문장에 위치해야 한다.
• 서브쿼리가 사용가능한 영역
  • 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. 단일 행 서브 쿼리

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

 

2. 다중 행 서브 쿼리

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

다중 행 비교연산자

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

 

 

3. 다중 컬럼 서브 쿼리

• 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미
• SQL-Server에서는 지원되지 않는 기능

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 )

 

 

 

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

 

 

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

가. SELECT절에 서브쿼리 사용하기

• 스칼라(Scalar Subquery) 서브쿼리
  • 한 행, 한 컬럼만을 반환하는 서브쿼리를 말함
    • 단일 행 서브쿼리
  • 두 건 이상일 시 에러 발생
• 메인쿼리의 결과 건수 만큼 반복 수행

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

 

 

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

• 인라인 뷰(Inline View)
  • 마치 동적으로 생성된 테이블인 것처럼 사용 할 수 있다.
    • 일반적인 뷰를 정적 뷰(Static View)라 한다.
    • 인라인 뷰를 동적 뷰(Static View)라 한다.

 

다. Having 절에서 서브쿼리 사용하기

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

UPDATE TEAM A 
SET A.STADIUM_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)

• 뷰는 실제 데이터를 가지고 있지 않다.
• 뷰의 장점

뷰의 장점	설명
독립성	테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
편리성	복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
보안성	직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 컬럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.