2.2.4 서브쿼리

• 하나의 SQL문에 있는 또 다른 SQL문
• 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있음
• 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음
• 서브쿼리는 항상 메인쿼리 레벨로 결과 집합이 생성
• 서브쿼리에서는 ORDER BY 절을 사용하지 못하고, 메인쿼리 마지막 문장에 사용 가능
• '서브쿼리가' SQL 문에서 사용 가능한 곳: SELECT 절, FROM 절, WHERE 절, HAVING 절, ORDER BY절, INSERT 문의 VALUES 절, UPDATE 문의 SET 절
• 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 경우: 비연관 서브쿼리, 가지는 경우: 연관 서브쿼리

단일 행 서브쿼리

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

다중 행 서브쿼리

• 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)과 함께 사용
  

다중 칼럼 서브쿼리

• 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것

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;

• 같은 팀에서 여러 명이 반환됨. SQL Server에서는 지원되지않음

연관 서브쿼리

• 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

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

• EXIST 서브쿼리는 항상 연관 서브쿼리로 사용
• 아무리 조건 만족하는 건이 여러 건이더라도 1건만 찾으면 추가적인 검색을 진행하지 않는다.
  (EXIST라 그런 듯)

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 절에 서브쿼리 사용 - 스칼라 서브쿼리

• 한 행, 한 컬럼만을 반환하는 서브퀄
• 메인쿼리의 결과 건수만큼 반복수행됨

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

• FROM 절에서 사용되는 서브쿼리: 인라인 뷰
• 일반적인 뷰: 정적 뷰, 인라인 뷰와 같은 뷰: 동적 뷰 (SQL 문이 실행될 때만 임시적 생성)
• 인라인 뷰의 칼럼은 SQL문에서 자유롭게 참조 가능함
• ORDER BY 절 사용 가능함

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

ORACLE

• 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

• TOP-N 쿼리를 통해 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출

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

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

뷰

• 실제 데이터를 가지고 있지 않다.
• 가상 테이블이라고도 함.
  

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;

• 이미 존재하는 뷰를 참조해서 또 뷰를 생성할수도 있다.
• 뷰를 사용하기 위해 해당 뷰의 이름을 FROM 절에 적어 사용하면 됨
• 제거: DROP VIEWS