서브쿼리
서브쿼리는 하나의 SQL문 안에 포함된 또 다른 SQL문을 의미한다. 주로 알려지지 않은 기준을 이용한 검색을 위해 사용되며, 메인쿼리가 서브쿼리를 포함하는 종속적인 관계를 가진다.
- 서브쿼리와 조인의 차이
- 조인: 조인은 조인에 참여하는 모든 테이블이 대등한 관계를 갖는다. 조인에 참여하는 모든 테이블의 칼럼을 자유롭게 사용할 수 있다. 예를 들어, 여러 테이블의 데이터를 한 번에 결합하여 출력할 수 있다.
- 서브쿼리: 서브쿼리는 메인쿼리의 칼럼을 사용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다. 메인쿼리와 서브쿼리 간의 관계에서 서브쿼리는 메인쿼리에 종속된다. 만약 서브쿼리의 칼럼을 메인쿼리의 결과에 포함하려면, 조인 방식으로 변환하거나 스칼라 서브쿼리 등을 사용해야 한다.
서브쿼리의 사용 주의사항은 다음과 같다.
- 괄호로 감싸서 사용
- 서브쿼리는 반드시 괄호로 감싸서 사용해야 합니다.
- 단일 행 및 복수 행 비교 연산자
- 서브쿼리는 단일 행 비교 연산자(
=,<,<=,>,>=,<>)와 함께 사용할 수 있으며, 이 경우 서브쿼리의 결과가 반드시 1건 이하이어야 한다. - 복수 행 비교 연산자(
IN,ANY,ALL)는 서브쿼리의 결과 건수와 상관없이 사용할 수 있다.
- 서브쿼리는 단일 행 비교 연산자(
- ORDER BY 사용 불가
- 서브쿼리에서는 ORDER BY를 사용할 수 없다.
- ORDER BY는 메인쿼리의 마지막 문장에 위치해야 한다.
서브쿼리의 사용 가능 위치는 다음과 같다.
- SELECT 절
- FROM 절
- WHERE 절
- HAVING 절
- ORDER BY 절
- INSERT 문의 VALUES 절
- UPDATE 문의 SET 절
1️⃣ 단일 행 서브쿼리
단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용되며, 서브쿼리의 결과가 반드시 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️⃣ 다중 행 서브쿼리
다수(복수) 행 서브쿼리는 IN, ANY, ALL 등의 연산자와 함께 사용된다. 예를 들어, 특정 조건에 맞는 다수의 결과를 반환할 수 있다.
1. 다중 행 비교 연산자
- IN: 서브쿼리 결과 집합에 속하는 값을 찾는다.
- ALL: 서브쿼리 결과 집합의 모든 값과 비교한다.
- ANY 또는 SOME: 서브쿼리 결과 집합의 어느 하나의 값과 비교한다.
2. 예제
오류 예제
-- 단일 행 비교 연산자를 사용한 경우의 오류
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;이 쿼리는 ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었다 오류를 반환한다. 이는 서브쿼리의 결과가 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;이 쿼리는 정상적으로 실행되어 다음과 같은 결과를 반환한다:
| 연고지명 | 팀명 | 영문팀명 |
|---|---|---|
| 전남 | 드래곤즈 | CHUNNAM DRAGONS FC |
| 성남 | 일화천마 | SEONGNAM ILHWA CHUNMA FC |
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;| 팀코드 | 선수명 | 포지션 | 백넘버 | 키 |
|---|---|---|---|---|
| K01 | 마르코스 | FW | 44 | 170 |
| K01 | 박정수 | MF | 8 | 170 |
| K02 | 고창현 | MF | 8 | 170 |
| K02 | 정준 | MF | 44 | 170 |
| K03 | 김중규 | MF | 42 | 170 |
이 쿼리는 소속팀별로 키가 가장 작은 선수의 정보를 반환한다.
4️⃣ 연관 서브쿼리
연관 서브쿼리는 서브쿼리 내에 메인쿼리의 칼럼이 사용되는 서브쿼리다. 서브쿼리는 메인쿼리의 각 행에 대해 반복적으로 수행된다.
예제
예제1
평균 키보다 작은 선수들의 정보를 출력하는 쿼리문은 아래와 같다.
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 선수명;| 팀명 | 선수명 | 포지션 | 백넘버 | 키 |
|---|---|---|---|---|
| 삼성블루윙즈 | 가비 | MF | 10 | 177 |
| 삼성블루윙즈 | 강대희 | MF | 26 | 174 |
| 스틸러스 | 강용 | DF | 2 | 179 |
| 시티즌 | 강정훈 | MF | 38 | 175 |
| 드래곤즈 | 강철 | DF | 3 | 178 |
| 현대모터스 | 고관영 | MF | 32 | 180 |
| 현대모터스 | 고민기 | FW | 24 | 178 |
| 삼성블루윙즈 | 고종수 | MF | 22 | 176 |
이 쿼리는 각 팀의 평균 키보다 작은 선수들의 정보를 출력한다.
예제2
특정 기간 동안 경기가 있는 경기장을 조회하는 쿼리문은 다음과 같다.
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');| ID | 경기장명 |
|---|---|
| B01 | 인천월드컵경기장 |
| B04 | 수원월드컵경기장 |
| B05 | 서울월드컵경기장 |
| C02 | 부산아시아드경기장 |
이 쿼리는 2012년 5월 1일부터 2012년 5월 2일 사이에 경기가 있는 경기장을 조회한다. EXISTS 서브쿼리는 조건을 만족하는 1건만 찾으면 추가적인 검색을 중단하는 특징이 있다.
5️⃣ 그밖에 위치에서 사용하는 서브쿼리
1. SELECT 절에 서브쿼리 사용하기
SELECT 절에서 사용하는 서브쿼리를 스칼라 서브쿼리(Scalar Subquery)라고 합니다. 스칼라 서브쿼리는 한 행, 한 칼럼만 반환하는 서브쿼리로, 칼럼을 사용할 수 있는 대부분의 곳에서 사용할 수 있다.
예제
선수 정보와 해당 선수가 속한 팀의 평균 키를 함께 출력하는 쿼리문은 다음과 같다.
SELECT PLAYER_NAME 선수명, HEIGHT 키,
(SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키
FROM PLAYER P;| 선수명 | 키 | 팀평균키 |
|---|---|---|
| 가비 | 177 | 179.067 |
| 가이모토 | 182 | 178.854 |
| 강대희 | 174 | 179.067 |
| 강성일 | 182 | 177.485 |
| 강용 | 179 | 179.911 |
| 강정훈 | 175 | 177.485 |
| 강철 | 178 | 178.391 |
| 고관영 | 180 | 180.422 |
| ... | ... | ... |
이 예제에서 각 선수의 소속팀별 평균 키를 알아내는 서브쿼리가 메인쿼리의 각 행마다 반복 수행되어, 선수의 정보를 출력할 때 팀의 평균 키도 함께 출력된다. 스칼라 서브쿼리는 단일 행 서브쿼리이기 때문에 결과가 2건 이상 반환되면 오류가 발생한다.
2. FROM 절에서 서브쿼리 사용하기
FROM 절에서 사용되는 서브쿼리는 인라인 뷰(Inline View)라고 한다. 인라인 뷰는 실행 시에 동적으로 생성된 테이블처럼 사용할 수 있으며, 데이터베이스에 저장되지 않는다. 일반적인 뷰는 정적 뷰(Static View)라고 하고, 인라인 뷰는 동적 뷰(Dynamic View)라고 한다. 인라인 뷰의 칼럼은 메인쿼리에서 자유롭게 참조할 수 있다.
예제
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 선수명;| 팀명 | 선수명 | 백넘버 |
|---|---|---|
| 삼성블루윙즈 | 가비 | 10 |
| 삼성블루윙즈 | 강대희 | 26 |
| 시티즌 | 강정훈 | 38 |
| 현대모터스 | 고관영 | 32 |
| 삼성블루윙즈 | 고종수 | 22 |
| 삼성블루윙즈 | 고창현 | 8 |
| 시티즌 | 공오균 | 22 |
| 일화천마 | 곽치국 | 32 |
| ... | ... | ... |
인라인 뷰를 통해 포지션이 미드필더(MF)인 선수들을 추출하고, 이 결과를 TEAM 테이블과 조인하여 팀명과 선수 정보를 출력하고 있다.
TOP-N 쿼리 예제
가장 키가 큰 5명의 선수 정보를 출력하는 쿼리문은 다음과 같다.
-- 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을 사용한 예제
SELECT TOP(5) PLAYER_NAME AS 선수명, POSITION AS 포지션, BACK_NO AS 백넘버, HEIGHT AS 키
FROM PLAYER
WHERE HEIGHT IS NOT NULL
ORDER BY HEIGHT DESC;| 선수명 | 포지션 | 백넘버 | 키 |
|---|---|---|---|
| 서동명 | GK | 21 | 196 |
| 권정혁 | GK | 1 | 195 |
| 김석 | FW | 20 | 194 |
| 정경두 | GK | 41 | 194 |
| 이현 | GK | 1 | 192 |
이 예제에서는 선수의 키를 내림차순으로 정렬한 후, 가장 키가 큰 5명의 선수 정보를 추출한다.
3. 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');| 팀코드 | 팀명 | 평균키 |
|---|---|---|
| K13 | 강원FC | 173.667 |
| K15 | 대구FC | 175.333 |
| K11 | 경남FC | 176.333 |
| K14 | 제주유나이티드FC | 169.5 |
| K12 | 광주상무 | 173.5 |
| K07 | 드래곤즈 | 178.391 |
| K08 | 일화천마 | 178.854 |
| K10 | 시티즌 | 177.485 |
이 예제에서는 각 팀의 평균 키를 계산하고, 그 결과가 삼성 블루윙즈팀(K02)의 평균 키보다 작은 팀만을 필터링하여 출력한다.
4. UPDATE문의 SET 절에서 사용하기
UPDATE문의 SET 절에서 서브쿼리를 사용하는 예제다. 예를 들어, 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);이 SQL문은 TEAM 테이블의 STADIUM_ID와 일치하는 STADIUM 테이블의 STADIUM_NAME을 찾아 TEAM 테이블의 STADIUM_NAME 칼럼을 업데이트한다. 서브쿼리가 NULL을 반환할 경우 해당 칼럼은 NULL이 될 수 있으므로 주의해야 한다.
5. INSERT문의 VALUES절에서 사용하기
INSERT문의 VALUES 절에서 서브쿼리를 사용하는 예제이다. PLAYER 테이블에 ‘홍길동’이라는 선수를 삽입할 때 PLAYER_ID의 값을 현재 사용 중인 최대 PLAYER_ID 값에 1을 더한 값으로 설정하려는 경우이다.
INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');이 SQL문은 PLAYER 테이블의 현재 최대 PLAYER_ID 값에 1을 더한 값을 새로 삽입되는 ‘홍길동’ 선수의 PLAYER_ID로 사용한다.
6️⃣ 뷰(View)
뷰(View)는 실제 데이터를 가지고 있지 않고, 단지 뷰 정의(View Definition)만을 가지고 있다. 뷰는 가상 테이블(Virtual Table)로서, 질의에서 뷰가 사용되면 DBMS는 뷰 정의를 참조하여 내부적으로 질의를 재작성(Rewrite)하여 수행한다.
1. 뷰 사용의 장점
- 데이터 보안: 중요한 데이터에 대한 접근을 제한할 수 있다.
- 간결성: 복잡한 쿼리를 단순화하여 사용하기 쉽게 만든다.
- 일관성: 여러 테이블의 데이터를 통합하여 일관된 결과를 제공한다.
- 독립성: 테이블 구조가 변경되어도 뷰를 사용하는 프로그램에 영향을 최소화할 수 있다.
2. 뷰 생성 예제
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이다.
3. 뷰를 기반으로 한 또 다른 뷰 생성
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 뷰를 기반으로 생성되며, 골키퍼(GK)와 미드필더(MF) 포지션의 선수들만을 필터링한다.
4. 뷰 사용 예제
SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '황%';| PLAYER_NAME | POSITION | BACK_NO | TEAM_ID | TEAM_NAME |
|---|---|---|---|---|
| 황철민 | MF | 35 | K06 | 아이파크 |
| 황승주 | DF | 98 | K05 | 현대모터스 |
| 황연석 | FW | 16 | K08 | 일화천마 |
이 쿼리는 V_PLAYER_TEAM 뷰에서 성이 ‘황’씨인 선수들만을 추출한다.
5. 뷰 제거
뷰를 제거하려면 DROP VIEW 명령을 사용한다.
DROP VIEW V_PLAYER_TEAM;
DROP VIEW V_PLAYER_TEAM_FILTER;뷰는 인라인 뷰와 유사한 형태로 동작하여 데이터를 저장하지 않고도 데이터 조회를 가능하게 한다. 이는 DBMS가 뷰 정의를 기반으로 질의를 재작성하여 수행하기 때문이다.
