[SQLD 도전기] SQL 활용(4) - 서브쿼리
본문 내용은 모두 필자가 공부하며 직접 정리한 내용입니다.
Kdata 데이터자격검정의 SQL 개발자(SQLD) 가이드를 참고하였습니다.
─지난 글 보기─
[SQLD 도전기] SQL 활용(3) - 계층형 질의와 셀프 조인
본문 내용은 모두 필자가 공부하며 직접 정리한 내용입니다. Kdata 데이터자격검정의 SQL 개발자(SQLD) 가이드를 참고하였습니다. ─지난 글 보기─ [SQLD 도전기] SQL 활용(2) - 집합 연산자(SET OPERATOR)
itsmesunky.tistory.com
과목 2 SQL 기본 및 활용
제 2장 SQL 활용
제 4절 서브쿼리
ㆍ하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.
ㆍ알려지지 않은 기준을 이용한 검색을 위해 사용한다.
ㆍ서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
ㆍ조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에
조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다.
ㆍ서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
ㆍ질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리 등을 사용해야 한다.
- 서브쿼리를 사용할 때 다음 사항에 주의해야 한다.
ㆍ서브쿼리를 괄호로 감싸서 사용한다.
ㆍ서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다.
ㆍ단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
ㆍ서브쿼리에서는 ORDER BY를 사용하지 못한다.
- 서브쿼리가 SQL문에서 사용이 가능한 곳은 다음과 같다.
- SELECT 절 - FROM 절 - WHERE 절 - HAVING 절 - ORDER BY 절
- INSERT문의 VALUES 절 - UPDATE문의 SET 절
1. 단일 행 서브쿼리
ㆍ서브쿼리가 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하이어야 한다.
ㆍ서브쿼리의 결과 건수가 2건 이상을 반환하면 SQL문은 실행시간(Run Time) 오류가 발생한다.
[예제] '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 표시
2. 다중 행 서브쿼리
ㆍ서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다.
[예제] 선수들 중에서 '정현수' 라는 선수가 소속되어 있는 팀 정보를 출력하는 서브쿼리를 작성하면 다음과 같다.
[예제] 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개 이상의 행이 리턴되었다.
[예제] 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
2개의 행이 선택되었다.ㆍ'정현수' 라는 이름을 가진 선수가 두 명이 존재한다.
위처럼, 서브쿼리의 실행 결과가 2건 이상이 나오는 모든 경우에 다중 행 비교 연산자를 사용해야 한다.
3. 다중 칼럼 서브쿼리
ㆍ서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미
ㆍ이 기능은 SQL Server에서는 지원되지 않는 기능이다.
[예제] 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
19개의 행이 선택되었다.4. 연관 서브쿼리
ㆍ서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다.
ㆍEXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다.
ㆍEXISTS 서브쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.
5. 그 밖에 위치에서 사용하는 서브쿼리
가. SELECT 절에 서브쿼리 사용하기
ㆍSELECT 절에서 사용하는 서브쿼리인 스칼라 서브쿼리(Scalar Subquery)는 한 행, 한 컬럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말한다.
ㆍ스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
[예제] 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
480개의 행이 선택되었다.ㆍ스칼라 서브쿼리 또한 단일 행 서브쿼리이기 때문에 결과가 2건 이상 반환되면 SQL문을 오류를 반환한다.
나. FROM 절에서 서브쿼리 사용하기
ㆍFROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다.
ㆍ인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.
ㆍ일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다.
[예제] K-리그 선수들 중에서 포지션이 미드필더인 선수들의 소속팀명 및 선수 정보를 출력
[예제] 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
162개의 행이 선택되었다.
ㆍ인라인 뷰에서는 ORDER BY절을 사용할 수 있다.
ㆍ인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 한다.
ㆍTOP-N 쿼리를 수행하기 위해서는 정렬 작업과 정렬 결과 중에서 일부 데이터만을 추출할 수 있는 방법이 필요하다.
ㆍOracle에서는 ROWNUM이라는 연산자를 통해서 결과로 추출하고자 하는 데이터 건수를 제약할 수 있다.
[예제] 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
[실행 결과] 선수명 포지션 백넘버 키
-------- ----- --- ---
서동명 GK 21 196
권정혁 GK 1 195
김석 FW 20 194
정경두 GK 41 194
이현 GK 1 192
5개의 행이 선택되었다.
다. HAVING 절에서 서브쿼리 사용하기
ㆍ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')
[실행 결과] 팀코드 팀명 평균키
---- ----------- ------
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
8개의 행이 선택되었다.라. UPDATE문의 SET절에서 사용하기
UPDATE TEAM A
SET A.STADIUM_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');
6. 뷰(View)
ㆍ테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다.
ㆍ뷰는 단지 뷰 정의만을 가지고 있다.
ㆍ질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다.
ㆍ테이블이 수행하는 역할을 수행하기 때문에 가상 테이블이라고도 한다.
다음에는 그룹 함수(GROUP 함수)에 관한 내용을 정리하여 포스팅하겠습니다.