과목II SQL 기본 및 활용 2장 - 4절 서브쿼리

(서브쿼리의 위치)

WHERE 절 : NESTED SUBQUERY

FROM 절  : INLINE VIEW

SELECT절 외 : SCALAR SUBQUERY 

 

- 서브쿼리를 문법적으로 구분하는 가장 쉬운 방법은 SQL문이 괄호로 묶여져 있으면 서브쿼리이다. 

- 서브쿼리가 메인쿼리에 포함되는 종속적인 관계이다. 

- 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다. (JAVA의 상속과 같은 개념)

- 서브쿼리에서는 ORDER BY 사용하지 못함. 

 

서브쿼리와 조인

서브쿼리와 조인을 논리적으로 구분하는 가장 좋은 방법은 두 개의 테이블 위치를 바꾸어보는 것이다.

조인은 두 개의 테이블 위치를 바꾸어 보더라도 같은 결과가 나오며, 서브쿼리의 경우는 주종의 관계이므로 일반적으로 다른 결과가 나오게 된다. 만약 서브쿼리에서 두개의 테이블 위치를 바꾸었는데 같은 결과가 나온다면, 조인으로 바꿀 수 있다는 얘기이므로 일반적으로 서브쿼리를 집합적 개념을 사용할 수 있는 조인으로 바꾸는 것을 권고한다. 

 

서브쿼리의 종류(반환 데이터 형태) 

- Single Row(단일행 서브쿼리) : 서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리. 단일행 비교 연산자와 사용( =,<,<=,>,>=,<>)

- Multi Row(다중행 서브쿼리) : 서브쿼리의 실행결과가 여러건인 서브쿼리. 다중행 비교 연산자와 사용(IN, ALL, ANY, SOME, EXISTS) 

- Multi Column(다중칼럼 서브쿼리) : 서브쿼리의 실행결과로 여러 컬럼을 반환. 메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 

 

1) 단일행 서브쿼리

서브쿼리가 단일 행 비교 연산자(-,<,<=,>,>=,<>)와 함께 사용할 떄는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다. (2건 이상 반환시 에러 발생) 

=> 서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중행 비교연산자와 함께 사용! 

EX) SELECT PLAYER_NAME 선수명 , POSITION 포지션 , BACK_NO 백넘버
FROM PLAYER_T
WHERE TEAM_ID = (SELECT TEAM_ID

                           FROM PLAYER_T
                           WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;

-> 만약 '정남일' 이라는 이름을 가진 동명이인이 존재한다면 에러. (1건 이하만 나와야 하므로)

 

2) 다중행 서브쿼리

- 다중행 연산자 : IN, ALL, ANY, SOME, EXISTS

EX) SELECT PLAYER_NAME 선수명 , POSITION 포지션 , BACK_NO 백넘버
FROM PLAYER_T
WHERE TEAM_ID IN (SELECT TEAM_ID

                           FROM PLAYER_T
                           WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;

-> 다중행 비교 연산자를 이용하여 동명이인을 확인할 수 있음 

 

3) 다중칼럼 서브쿼리

- sql server에서 지원하지 않는다. 

- EX)

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;

 

서브쿼리의 종류(동작 방식) 

- Un-Correlated (비연관 서브쿼리)  : 서브쿼리가 메인쿼리의 칼럼을 가지고 있지 않은 형태. 메인쿼리에 값을 제공하기 위한 목적으로 사용 

- Correlated (연관 서브쿼리) : 서브쿼리가 메인쿼리의 칼럼을 사용하는 형태의 서브쿼리이다. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 사용 

(메인쿼리의 행 수 만큼 실행되는 쿼리이므로 상대적으로 실행속도 떨어짐) 

 

연관 서브쿼리 

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

 

+) 연관서브쿼리를 사용해서 UPDATE문을 작성해 특정 컬럼을 일괄변경하고 싶은 경우에 연관서브쿼리 결과가 공집합(조건이 맞지않는 경우)이라면 결과 칼럼에 NULL값으로 업데이트 되는 리스크. 

연관서브쿼리의 조인조건이 맞지 않는 경우라면? 

 

EXISTS 서브쿼리 

- EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다. 

- 조건을 만족하는 건이 여러건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다. 

  (이 말이 굉장히 헷갈렸었는데, 전체 테이블에 대한  검색을 멈춘다는 얘기가 아니라 각 행당 조건을 검사하면서 그 조건을 일치하는 건이 여러개일 경우 하나만 찾으면 검색을 멈춘다는 소리다.) 

- 결과가 한건이라도 존재하면 TRUE, 없으면 FALSE 반환한다. 

 

예제) SELECT STADIUM_ID, STADIUM_NAME
        FROM STADIUM
        WHERE EXISTS (SELECT 1
                             FROM SCHEDULE
                             WHERE SCHE_DATE BETWEEN '20120501' AND '20120521')

 

-> 서브쿼리의 데이터가 필요한 경우가 아니라면 1,'X'같은 업무적으로 의미없는 상수값을 선택

 

INLINE VIEW

- FROM 절에서 사용되는 서브쿼리를 인라인 뷰라고 한다. 

- 동적 뷰라고도 한다. 

- 집합간의 조인횟수를 줄이는 용도 

- 중첩되어 사용할 수 있으며, 일반적으로 가장 안의 인라인 뷰부터 수행이 된다. 

- 인라인 뷰에 정의된 SELECT 칼럼을 메인 쿼리에 사용할 수 있다. 

   EX) SELECT EMPNO FROM
       (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR)

- 인라인 뷰에 미정의된 칼럼은 메인쿼리에서 사용할 수 없다.

   EX) SELECT MGR FROM

       (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR)

 

VIEW

- 실제 데이터를 가지고 있지 않으며, 단지 뷰 정의만을 가지고 있다. 

- 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성

- ORACLE의 경우 USERS_VIEWS에서 해당 뷰에 사용된 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;

 

뷰의 장점

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

- 편리성 : 복잡한 질의를 단순하게 작성 

- 보안성  : 뷰 생성시 원하는 정보는 숨기고 생성할 수 있음. 

 

스칼라 서브쿼리

- 한 행, 한 칼럼만을 반환하는 서브쿼리이다. 

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

- 같은 결과를 얻을 수 있다면 가능하면 조인으로 대체하는 것이 좋음 

- 가능한 위치 : SELECT LIST 항목, 함수의 인자, WHERE절의 조건, ORDER BY 절 

예시)