[SQLD] SQL 활용 2-4. 서브쿼리

서브쿼리

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

 

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

 

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

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

[조인과 차이점 : 조인은 조인에 참여하는 모든 테이블의 칼럼을 사용 가능]

 

질의 결과에 서브쿼리 칼럼을 표시하는 방법

• 조인 방식으로 변환
• 함수 사용
• 스칼라 서브쿼리 사용

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

조인 : 집합간의 곱의 관계 -> M * N : MN레벨의 집합

 

EX) 결과는 조직 레벨, 사원 테이블에서 체크해야할 조건 존재

: SQL문에 DISTINCT를 추가해 결과를 조직레벨로 만든 후, 메인쿼리로 조직을 사용하고, 서브쿼리로 사원 테이블을 사용.

-> 결과집합이 조직레벨롤 원하는 결과

 

서브쿼리 주의사항

• 괄호로 감싸서 사용
• 단일 행 또는 복수 행 비교 연산자와 함께 사용
• ORDER BY 사용 불가 -> 메인쿼리의 마지막 문장에 위치시킨다.

 

동작하는 방식에 따른 서브쿼리 분류
서브쿼리 종류	설명
비연관 서브쿼리	-서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리 -메인쿼리에 값 제공 목적
연관 서브쿼리	-서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리 -메인쿼리가 먼저 수행되어 데이터가 서브쿼리에 조건이 맞는지 확인하는 목적
반환되는 데이터의 형태에 따른 서브쿼리 분류
단일 행 서브쿼리	-서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리 -단일 행 비교연산자 [= , < , <= , > , >= , <>]
다중 행 서브쿼리	-서브쿼리의 실행결과가 여러 건인 서브쿼리 -다중 행 비교연산자 [IN, ALL, ANY, SOME, EXIESTS]
다중 컬럼 서브쿼리	-서브쿼리의 실행 결과로 여러 칼럼 반환 -메인쿼리 조건절에 여러 칼럼 동시 비교 가능 -서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일

 

1. 단일 행 서브쿼리

 

(1) 정남일 선수가 소속된 팀의 선수들에 대한 정보를 표시

서브쿼리 : 정남일 선수의 소속팀을 알아내는 SQL문 메인쿼리 : 서브쿼리 결과를 이용해 해당 팀에 소속된 선수들의 정보를 출력하는 SQL문 SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일') ORDER BY PLAYER_NAME;

정남일 선수의 소속팀을 알아내는 서브쿼리가 먼저 수행 -> 정남일 선수의 소속팀 코드 반환
메인쿼리가 서브쿼리에서 반환된 결과를 이용 -> 조건을 만족하는 선수들의 정보를 출력
단, 동명이인이 있는 경우 오류 발생

 

(2) 선수들 중에서 키가 평균 이하인 선수들의 정보를 출력

그룹함수를 사용한 서브쿼리 SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER) ORDER BY PLAYER_NAME;

서브쿼리 : 선수들의 평균키를 알아내는 SQL문
메인쿼리 : 서브쿼리를 이용해서 키가 평균 이하의 선수들의 정보를 출력하는 SQL문

 

2. 다중 행 서브쿼리

다중 행 연산자	설명
IN	서브쿼리 결과에 존재하는 임의 값과 동일한 조건 [Multiple OR 조건]
ALL	서브쿼리 결과에 존재하는 모든 값을 만족하는 조건
ANY	서브쿼리 결과에 존재하는 어느 하나의 값 [SOME 조건]
EXISTS	서브쿼리 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건 [1건만 찾으면 된다.]

 

(3) 선수들 중 '정현수'라는 선수가 소속되어 있는 팀 정보를 출력

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수') ORDER BY TEAM_NAME; 2개 이상의 행이 반환 -> 에러 발생

SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수') ORDER BY TEAM_NAME;

 

3. 다중 칼럼 서브쿼리

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

 

(4) 소속팀별 키가 가장 작은 사람들의 정보를 출력

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. 연관 서브쿼리

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

 

(5) 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력

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건만 찾으면 추가적인 검색을 진행하지 않는다.

 

(6) '20120501' 부터 '20120502' 사이에 경기가 있는 경기장을 조회

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

스칼라 서브쿼리

: 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리로 칼럼을 쓸 수 있는 대부분의 곳에서 사용 가능

 

(7) 선수 정보와 해당 선수가 속한 팀의 평균 키를 함께 출력

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

메인쿼리 : 선수들의 정보를 출력하는 SQL문
서브쿼리 : 해당 선수의 소속팀별 평균키를 알아내는 SQL문
스칼라 서브쿼리 : 선수의 소속팀별 평균키를 알아내는는 메인쿼리의 결과 건수 만큼 반복수행
[단, 스칼라 서브쿼리도 단일 행 서브쿼리로, 결과가 2건 이상 반환시 오류 발생]

 

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

서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용

-> 즉, 인라인 뷰 는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰로 테이블명이 올수 있는 곳에서 사용

-> 따라서 인라인 뷰는 서브쿼리와 달리 SQL문에서 자유롭게 참조할 수 있다. [조인 방식과 같다.]

[서브쿼리의 칼럼은 메인쿼리에서 사용 불가]

 

(8) K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력

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

선수들 중에서 포지션이 미드필더(MF) 선수들을 인라인 뷰를 통해서 추출 하고 인라인 뷰의 결과와 TEAM 테이블과 조인해서 팀명(TEAM_NAME)을 출력
인라인 뷰에서는 ORDER BY절을 사용가능

TOP-N 쿼리 : 인라인 뷰에 먼저 정렬을 수행하고 정 렬된 결과 중에서 일부 데이터를 추출
-> 수행하기 위해서는 정렬 작업과 정렬 결과 중에서 일부 데이터만을 추출할 수 있는 방법이 필요
[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;

인라인 뷰에서 선수의 키를 내림차순으로 정렬(가장 키가 큰 선수부터 출력) 한 후 메인쿼리에서 ROWNUM을 사용해서 5명의 선수의 정보만을 추출

즉, 모든 선수들 중에서 가장 키가 큰 5명의 선수를 출력
[만약, 다른 선수 중에서 키가 192인 선수가 더 존재하더라도 해당 SQL문에서는 데이터가 출력되지 않는다.
이런 데이터 까지 추출하고자 한다면 분석함수의 RANK관련 함수를 사용]

 

다. HAVING 절에서 서브쿼리 사용

: 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용

 

(9) 평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키 출력

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

(10) STADIUM_NAME 을 추가(ALTER TABLE ADD COLUMN)하였다고 가정하고, TEAM 테이블에 추가된 STADIUM_NAME의 값을 STADIUM 테이블을 이용하여 변경할 경우

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

(11) PLAYER_ID의 값을 현재 사용중인 PLAYER_ID에 1을 더한 값으로 PLAYER 테이블에 '홍길동'이라는 선수를 삽입

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID) VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');

 

6. 뷰

: 뷰는 실제 데이터를 갖지 않으면서 뷰 정의(View Definition)만을 가지고 있다.

질의에서 뷰가 사용되면 뷰 정의를 참조해 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다.

-> 따라서, 뷰는 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)

 

장점

장 점	설 명
독립성	테이블 구조 변경되어도 뷰를 사용하는 응용 프로그램은 변경이 필요하지 않다.
편리성	복잡한 질의를 뷰로 생성해 단순하게 작성 가능. 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용해 편리하게 사용
보안성	직원의 급여정보와 같이 숨기고 싶은 정보가 존재하는 경우, 뷰 생성시 해당 칼럼을 제외하고 생성해 정보를 감출 수 있다.

생성

(12) 선수 정보와 해당 선수가 속한 팀명을 함께 추출

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;

뷰의 명칭은 'V_PLAYER_TEAM'

 

이미 존재하는 뷰를 참조해 생성

(13) 앞에서 생성했던 V_PLAYER_TEAM 뷰를 기반으로 선수 포지션이 골키퍼(GK), 미드필더(MF)인 선수만을 추출하고자 하는 뷰를 생성

CREATE VIEW V_PLAYER_TEAM_FILTER AS SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME FROM V_PLAYER_TEAM WHERE POSITION IN ('GK', 'MF');

V_PLAYER_TEAM_FILTER 뷰 : 이미 앞에서 생성했던 V_PLAYER_TEAM 뷰를 기반으로 해서 생성된 뷰

 

사용

(14) V_PLAYER_TEAM 뷰에서 성이 '황'씨인 선수만을 추출

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME FROM V_PLAYER_TEAM WHERE PLAYER_NAME LIKE '황%'

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME FROM (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) WHERE PLAYER_NAME LIKE '황%'

뷰를 사용하는 경우 DBMS가 내부적으로 재작성하는 방법
인라인 뷰와 유사한 모습으로 뷰는 데이터를 저장하지 않고도 데이터 조회 가능

 

삭제

DROP VIEW V_PLAYER_TEAM; DROP VIEW V_PLAYER_TEAM_FILTER;