본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
4. 서브 쿼리
서브쿼리(Subquery)란 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문을 말한다. 서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다.
조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다. 조인은 집합간의 곱(Product)의 관계이다. 즉, 1:1 관계의 테이블이 조인하면 1(= 1 * 1) 레벨의 집합이 생성되고, 1:M 관계의 테이블을 조인하면 M(= 1 * M) 레벨의 집합이 생성된다. 그리고 M:N 관계의 테이블을 조인하면 MN(= M * N) 레벨의 집합이 결과로서 생성된다.
그러나 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다. 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리(Scalar Subquery) 등을 사용해야 한다. 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.
스칼라 서브쿼리는 JOIN으로 동일한 결과를 추출할 수 있다.
서브쿼리 사용 시 주의 사항
- 서브쿼리는 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용이 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
- 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 메인쿼리의 마지막 문장에 위치해야 한다.
서브쿼리 사용이 가능한 곳
- SELECT 절
- FROM 절
- WHERE 절
- HAVING 절
- ORDER BY 절
- INSERT 문의 VALUES 절
- UPDATE 문의 SET 절
동작하는 방식에 따른 서브쿼리 분류
-
Un-Correlated(비연관) 서브쿼리
- 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않는 형태의 서브쿼리
- 메인쿼리에 값을 제공하기 위한 목적으로 사용
-
Correlated(연관) 서브쿼리
- 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브쿼리
- 메인쿼리가 먼저 수행되어 읽힌 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용
서브쿼리는 메인쿼리 안에 포함된 종속적인 관계이기 때문에 논리적인 실행순서는 항상 메인쿼리에서 읽혀진 데이터에 대해 서브쿼리에서 해당 조건이 만족하지를 확인하는 방식으로 수행되어야 한다. 그러나 실제 서브쿼리의 실행순서는 상황에 따라 달라질 수 있다.
반환되는 데이터 형태에 따른 서브쿼리 분류
-
단일 행 서브쿼리
- 실행 결과가 항상 1건 이하인 서브쿼리
- 단일 행 비교 연산자인 =, <, ≤, >, ≥, <>과 함께 사용
-
다중 행 서브쿼리
- 실행 결과가 여러 건인 서브쿼리
- 다중 행 비교 연산자인 IN, ALL, ANY, SOME, EXISTS와 함께 사용
- 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리의 비교연산자로 사용할 수 없지만, 반대의 경우는 가능하다.
-
다중 컬럼 서브쿼리
- 실행 결과로 여러 컬럼을 반환하는 서브쿼리
- 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다.
1. 단일 행 서브 쿼리
서브쿼리가 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1 건 이하이어야 한다. 만약, 서브쿼리의 결과 건수가 2 건 이상을 반환하면 SQL 문은 실행시간(Run Time) 오류가 발생한다.
정남일 선수가 소속된 팀의 선수 정보를 표시하는 서브쿼리 방식의 SQL 문
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;
전체 평균 키 이하의 선수 정보를 출력하는 SQL 문
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER)
ORDER BY PLAYER_NAME;
2. 다중 행 서브쿼리
다중 행 비교 연산자
- IN (서브쿼리)
- 서브쿼리 결과에 존재하는 임의의 값과 동일한 조건을 의미
- ALL (서브쿼리)
- 서브쿼리 결과에 존재하는 모든 값을 만족하는 조건을 의미
- ANY (서브쿼리) / SOME (서브쿼리)
- 서브쿼리 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미
- EXISTS (서브쿼리)
- 서브쿼리 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미
'정현수'라는 선수가 소속된 팀 정보를 출력하는 서브쿼리 방식의 SQL 문
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;
GROUP BY 없이 HAVING 절을 사용한 서브쿼리 사례
SELECT A.회원ID, A.회원명, A.이메일
FROM 회원 A
WHERE EXISTS (SELECT 'X' -- 'X' 쓰면 가능, 다른 컬럼값 가져오는 건 실패했음
FROM 이벤트 B, 메일발송 C
WHERE B.시작일자 >= '2014.10.01'
AND B.이벤트ID = C.이벤트ID
AND A.회원ID = C.회원ID
HAVING COUNT(*) < (SELECT COUNT(*)
FROM 이벤트
WHERE 시작일자 >= '2014.10.01'));
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;
SQL 문의 실행 결과를 보면 서브쿼리의 결과값으로 소속팀코드(TEAM_ID)와 소속팀별 가장 작은 키를 의미하는 MIN(HEIGHT)라는 두 개의 칼럼을 반환했다. 메인쿼리에서는 조건절에 TEAM_ID 와 HEIGHT 칼럼을 괄호로 묶어서 서브쿼리 결과와 비교하여 원하는 결과를 얻었다. 실행 결과에서 보면 하나 팀에서 키가 제일 작은 선수 한 명씩만 반환된 것이 아니라 같은 팀에서 여러 명이 반환된 것을 확인할 수 있다. 이것은 동일 팀 내에서 조건(팀별 가장 작은 키)을 만족하는 선수가 여러 명이 존재하기 때문이다. 그러나 이 기능은 SQL Server 에서는 지원되지 않는 기능이다.
4. 연관 서브쿼리
연관 서브쿼리(Correlated Subquery)는 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다.
선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하는 SQL 문
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 선수명;
가비 선수는 삼성블루윙즈팀 소속이므로 삼성블루윙즈팀 소속의 평균키를 구하고 그 평균키와 가비 선수의 키를 비교하여 적을 경우에 선수에 대한 정보를 출력한다. 만약, 평균키 보다 선수의 키가 크거나 같으면 조건에 맞지 않기 때문에 해당 데이터는 출력되지 않는다. 이와 같은 작업을 메인쿼리에 존재하는 모든 행에 대해서 반복 수행한다.
5. 그밖의 위치에서 사용하는 서브쿼리
가. SELECT 절에 서브쿼리 사용하기
SELECT 절에서 사용하는 서브쿼리인 스칼라 서브쿼리(Scalar Subquery)에 대해서 알아본다. 스칼라 서브쿼리는 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말한다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
SELECT PLAYER_NAME 선수명,
HEIGHT 키,
ROUND((SELECT AVG(HEIGHT)
FROM PLAYER X
WHERE X.TEAM_ID = P.TEAM_ID), 2) 팀평균키
FROM PLAYER P;
선수들의 정보를 출력하는 SQL 문(메인쿼리 부분)과 해당 선수의 소속팀별 평균키를 알아내는 SQL 문(서브쿼리 부분)으로 구성된다. 여기서 선수의 소속팀별 평균키를 알아내는 스칼라 서브쿼리는 메인쿼리의 결과 건수만큼 반복수행 된다.
스칼라 서브쿼리 또한 단일 행 서브쿼리이기 때문에 결과가 2 건 이상 반환되면 SQL 문은 오류를 반환한다.
나. FROM 절에서 서브쿼리 사용하기
FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다.
인라인 뷰는 SQL 문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다. 인라인 뷰는 테이블명이 올 수 있는 곳에서 사용할 수 있다.
서브쿼리의 칼럼은 메인쿼리에서 사용할 수 없다고 했다. 그러나 인라인 뷰는 동적으로 생성된 테이블이다. 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다. 그렇기 때문에 인라인 뷰의 칼럼은 SQL 문 자유롭게 참조할 수 있다.
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 선수명;
인라인 뷰에서는 ORDER BY 절을 사용할 수 있다. 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 한다.
-- Oracle
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
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 절에서 서브쿼리 사용하기
평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 구하는 SQL 문
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.STADIUM_NAME = (SELECT X.STADIUM_NAME
FROM STADIUM X
WHERE X.STADIUM_ID = A.STADIUM_ID);
서브쿼리를 사용한 변경 작업을 할 때 서브쿼리의 결과가 NULL 을 반환할 경우 해당 컬럼의 결과가 NULL 이 될 수 있기 때문에 주의해야 한다.
WHERE 절은 UPDATE 대상이 되는 데이터의 범위를 결정하게 되는데, WHERE 절이 누락되면 모든 데이터가 UPDATE 대상이 되므로 NULL 값으로 변경될 수 있다.
마. 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)
테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다. 뷰는 단지 뷰 정의(View Definition)만을 가지고 있다. 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다. 뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 한다.
뷰 사용의 장점
- 독립성: 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
- 편리성: 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
- 보안성: 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 컬럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.
뷰는 다음과 같이 CREATE VIEW 문을 통해서 생성할 수 있다.
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;
뷰는 테이블뿐만 아니라 이미 존재하는 뷰를 참조해서도 생성할 수 있다.
CREATE VIEW V_PLAYER_TEAM_FILTER AS
SELECT PLAYER_NAME,
POSITION,
BACK_NO,
TEAM_NAME
FROM V_PLAYER_TEAM
WHERE POSITION IN ('GK', 'MF');
뷰를 사용하여 데이터를 조회할 수 있다.
SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '황%';
뷰를 제거하기 위해서는 DROP VIEW 문을 사용한다.
DROP VIEW V_PLAYER_TEAM;
DROP VIEW V_PLAYER_TEAM_FILTER;
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.6. 윈도우 함수 (0) | 2020.11.27 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.5. 그룹 함수 (0) | 2020.11.26 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.3. 계층형 질의와 셀프 조인 (0) | 2020.11.25 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.2. 집합 연산자 (0) | 2020.11.25 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.1. 표준 조인 (0) | 2020.11.24 |
