혼자 공부하는 공간

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #4 본문

자격증/SQLD

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #4

god_z 2020. 8. 30. 16:28

<목차>

<< 서브쿼리 >>

* 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 말한다.

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

메인쿼리와 서브쿼리

 

A. JOIN과의 비교

* JOIN은 JOIN에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 JOIN에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다.

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

---> SQL문 결과에 서브쿼리 칼럼을 출력해야 한다면 JOIN 방식으로 변환하거나 함수, 스칼라 서브쿼리 등을 사용해야 한다.

 

* JOIN은 집합 간의 곱의 관계이기 때문에 N:M 관계의 테이블을 JOIN하면 N*M 레벨의 집합이 생성된다.

* 서브쿼리는 서브쿼리의 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.

 

 

B. 서브쿼리 사용 시 주의사항

* 서브쿼리를 괄호로 감싸서 사용한다.

* 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용이 가능하다.

---> 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.

* 서브쿼리에서는 ORDER BY 절을 사용할 수 없다.

---> ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 메인쿼리의 마지막 문장에 위치한다.

 

 

C. 서브쿼리를 사용할 수 있는 곳

* SELECT 절

* FROM 절

* WHERE 절

* HAVING 절

* ORDER BY 절

* INSERT 문의 VALUES 절

* UPDATE 문의 SET 절

 

 

D. 서브쿼리의 종류

* 동작하는 방식으로 분류

동작하는 방식으로 분류

* 반환되는 데이터의 형태로 분류

반환되는 데이터의 형태로 분류

 

1. 단일 행 서브쿼리

* 서브쿼리가 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다.

---> 2건 이상을 반환하면 런타임 오류 발생

 

* 예시 - 문제

   : 정남일 선수가 소속되어 있는 팀의 선수들에 대한 정보를 서브쿼리를 사용하여 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, P.BACK_NO 백넘버
FROM PLAYER P
WHERE TEAM_ID = (
                SELECT P2.TEAM_ID
                FROM PLAYER P2
                WHERE P2.PLAYER_NAME = '정남일'
                )
ORDER BY P.PLAYER_NAME;
 
/*
선수명  포지션 백넘버 
------- ----- ----- 
강철     DF    3 
김반     MF    14 
김영수   MF    30 
김정래   GK    33 
김창원   DF    5 
김회택   TM 
꼬레아   FW    16 
노병준   MF    22 
......
51개의 행이 선택되었다.
*/
 cs

---> 정남일 선수의 TEAM_ID을 알아내는 서브쿼리가 먼저 수행된 후, 결과 TEAM_ID와 같은 선수들의 정보를 출력하는 메인쿼리가 나중에 수행된다.

 

* 예시 - 문제

   : 선수들 중에서 키가 평균 이하인 선수들의 정보를 서브쿼리를 사용하여 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, P.BACK_NO 백넘버
FROM PLAYER P
WHERE P.HEIGHT <= (
                SELECT AVG(P2.HEIGHT)
                FROM PLAYER P2
                )
ORDER BY P.PLAYER_NAME;
 
/*
선수명  포지션  백넘버 
------- ------ ------ 
가비     MF     10 
강대희   MF     26 
강용     DF     2 
강정훈   MF     38 
강철     DF     3 
고규억   DF     29 
고민기   FW     24 
고종수   MF     22 
.....
228개의 행이 선택되었다.
*/
 cs

 

2. 다중 행 서브쿼리

* 서브쿼리의 결과가 2건 이상 반환된다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)과 함께 사용해야 한다.

다중 행 비교 연산자

 

* 예시 - 문제

   : 정현수 선수가 소속되어 있는 팀의 정보를 출력하는 서브쿼리를 작성하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
SELECT T.REGION_NAME 연고지명, T.TEAM_NAME 팀명, T.E_TEAM_NAME 영문팀명 
FROM TEAM T
WHERE TEAM_ID = (
                SELECT P.TEAM_ID 
                FROM PLAYER P
                WHERE P.PLAYER_NAME = '정현수'
                ) 
ORDER BY T.TEAM_NAME;
 
/*
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었다.
*/
 cs

---> '정현수' 라는 이름을 가진 선수가 2명 이상이 있기 때문에 서브쿼리의 결과로 2건 이상의 행이 반환되어 단일 행 비교 연산자인 '='로는 처리가 불가능하여 에러가 발생.

---> 다중 행 비교 연산자를 통해 아래처럼 SQL문을 바꿔주어야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT T.REGION_NAME 연고지명, T.TEAM_NAME 팀명, T.E_TEAM_NAME 영문팀명 
FROM TEAM T
WHERE TEAM_ID IN (
                SELECT P.TEAM_ID 
                FROM PLAYER P
                WHERE P.PLAYER_NAME = '정현수'
                ) 
ORDER BY T.TEAM_NAME;
 
/*
연고지명 팀명  영문팀명 
------- ----- ----------------------- 
전남 드래곤즈 CHUNNAM DRAGONS 
FC 성남 일화천마 SEONGNAM ILHWA CHUNMA FC 
2개의 행이 선택되었다.
*/
 cs

---> 단일 행 비교 연산자인 '=' 대신에 다중 행 비교 연산자인 'IN' 을 사용해 원하는 결과를 얻었다.

 

3. 다중 칼럼 서브쿼리

* 다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.

 

* 예시 - 문제

   : 소속팀별 키가 가장 작은 사람들의 정보를 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT P.TEAM_ID 팀코드, P.PLAYER_NAME 선수명, P.POSITION 포지션, P.BACK_NO 백넘버, P.HEIGHT 키 
FROM PLAYER P
WHERE (P.TEAM_ID, P.HEIGHT) 
IN (
    SELECT P2.TEAM_ID, MIN(P2.HEIGHT) 
    FROM PLAYER P2
    GROUP BY P2.TEAM_ID
    ) 
ORDER BY P.TEAM_ID, P.PLAYER_NAME;
 
/*
팀코드 선수명   포지션  백넘버 키 
----- -------- ------ ------ ----
K01   마르코스  FW     44     170 
K01   박정수    MF     8      170 
K02   고창현    MF     8      170 
K02   정준      MF     44     170 
K03   김중규    MF     42     170 
.....
19개의 행이 선택되었다.
*/
 cs

---> 여러 칼럼, 여러 행을 반환하는 서브쿼리이다. (팀별 최소키를 가진 선수의 팀코드(칼럼1), 최소키(칼럼2))

 

---> 메인쿼리 및 서브쿼리 모두 PLAYER 테이블을 참고한다. 서브쿼리는 메인쿼리의 칼럼을 사용할 수 있기 때문에 서브쿼리에서 사용하는 칼럼이 서브쿼리의 것인지, 메인쿼리의 것인지 헷갈릴 수 있다. 그렇기 때문에 ALIAS를 통해 누구의 칼럼인지 명확히 표기해주는 것이 좋다.

 

---> 만약 표기를 하지 않았더라도 서브쿼리의 칼럼은 서브쿼리에서 먼저 찾은 후 메인쿼리에서 찾기 때문에 결과는 동일하다.

 

4. 연관 서브쿼리

* 서브쿼리 내에 메인쿼리의 칼럼이 사용된 서브쿼리를 의미한다.

 

* 예시 - 문제

   : 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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 
.....
224의 행이 선택되었다.
*/
 cs

---> 메인쿼리에서 선수 A가 Z팀일 때, 서브쿼리에서 Z팀 선수의 평균키를 구해야 한다.

 

---> 메인쿼리의 팀과 같은 팀의 선수에 접근해야 하므로 참조하는 테이블은 PLAYER 테이블.

      (FROM PLAYER S)

 

---> 서브쿼리의 PLAYER 테이블의 팀 코드와 메인쿼리의 PLAYER 테이블의 팀 코드와 같아야 한다.

      (WHERE S.TEAM_ID = M.TEAM_ID)

 

---> 서브쿼리에서 평균키는 팀별 평균키이기 때문에 소그룹을 팀 코드를 기준으로 나눈다.

      (GROUP BY S.TEAM_ID)

 

---> 그룹 함수는 NULL 값을 제외하고 계산하기 때문에 S.HEIGHT IS NOT NULL은 생략해도 무관하다.

 

 

* EXISTS 서브쿼리 : 항상 연관 서브쿼리로 사용된다. 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.

 

* 예시 (EXISTS)

   : '20120501' 부터 '20120502' 사이에 경기가 있는 경기장의 정보를 출력하라.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 부산아시아드경기장 
4개의 행이 선택되었다.
*/
 cs

---> EXISTS 절의 결과는 TRUE / FALSE이다.

---> 모든 경기장의 경기장 코드와 스케쥴의 경기장 코드가 같고, 스케쥴의 날짜가 20120501과 20120502 사이인 데이터가 한 건이라도 있으면 조회를 진행하지 않고 TRUE를 반환하고 해당하는 경기장 ID와 NAME을 출력한다.

 

5. 그 밖의 위치에서 사용하는 서브쿼리

5-1. SELECT 절에 서브쿼리 사용하기

* 스칼라 서브쿼리(Scalar Subquery) 라고 한다.

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

* 선수 정보와 해당 선수가 속한 팀의 평균 키를 함께 출력하는 예제로 설명하면 아래와 같다.

스칼라 서브쿼리

---> 한 선수의 정보를 출력할 때마다 소속팀별 평균키를 알아내는 서브쿼리문을 반복 수행한다.

* SQL문으로 작성하면 아래와 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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개의 행이 선택되었다.
*/
 cs

 

* 스칼라 서브쿼리 또한 단일 행 서브쿼리이므로 결과가 2건 이상이 반환되면 에러가 발생한다.

 

5-2. FROM 절에서 서브쿼리 사용하기

* 인라인 뷰(Inline View)라고 한다.

* SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 DB에 해당 정보가 저장되지 않는다.

* 인라인 뷰를 사용하면 메인쿼리에서도 서브쿼리의 칼럼을 사용할 수 있다. (JOIN방식을 사용하는 것)

 

* 예시 - 문제

   : K-리그 선수들 중에서 포지션이 MF인 선수들의 소속팀명 및 선수 정보를 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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개의 행이 선택되었다.
*/
 cs

---> 포지션이 MF인 선수들을 인라인 뷰를 통해 추출한다.

---> 인라인 뷰의 결과와 TEAM 테이블과 EQUI JOIN해서 TEAM_NAME을 출력한다.

 

 

* 예시 - 문제

   : 키가 큰 순서대로 선수 5명을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
 
/*
선수명   포지션 백넘버  키 
-------- ----- ------ --- 
서동명   GK     21     196 
권정혁   GK     1      195 
김석     FW     20     194 
정경두   GK     41     194 
이현     GK     1      192 
5개의 행이 선택되었다.
*/
 cs

---> 내림차순 정렬 시, 키(HEIGHT)가 NULL이라면 NULL 값이 가장 큰 값이 되기 떄문에 (Oracle 기준) NULL 값은 제외한다.

 

5-3. HAVING 절에서 서브쿼리 사용하기

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

 

* 예시 - 문제

   : 평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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개의 행이 선택되었다.
*/
 cs

 

5-4. UPDATE문의 SET 절에서 사용하기

* TEAM 테이블에 STADIUM_NAME을 추가했다고 가정. (ALTER TEAM ADD COLUMN)

 

* 예시 : TEAM 테이블의 STADIUM_NAME 값을 STADIUM 테이블을 이용하여 변경하고자 한다.

1
UPDATE TEAM A SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X WHERE X.STADIUM_ID = A.STADIUM_ID);
 cs

 

5-5. INSERT문의 VALUES 절에서 사용하기

* 예시 : PLAYER 테이블에 '홍길동' 이라는 선수를 삽입할 것이다. 이 때 PLAYER_ID의 값을 현재 사용 중인 PLAYER_ID에 1을 더한 값으로 넣고자 한다.

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

 

6. 뷰(View)

* 테이블은 데이터를 실제로 가지고 있지만, 뷰는 실제로 데이터를 가지고 있지 않다.

* 뷰는 단지 정의만을 가지고 있다.

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

* 가상 테이블이라고도 한다.

* 뷰 사용 시 장점

뷰 사용의 장점

 

A. 뷰의 생성

1
2
3
4
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;
 cs

---> 뷰의 이름은 V_PLAYER_TEAM

---> 선수이름, 포지션, 백넘버, 팀 코드, 팀 이름의 정보로 구성되어 있다.

 

* 이미 생성된 뷰를 참조해서도 새로운 뷰를 생성할 수 있다.

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

---> 뷰 이름은 V_PLAYER_TEAM_FILTER

---> 위의 V_PLAYER_TEAM 뷰를 참조한다.

---> 포지션이 MF또는 GK인 선수의 선수이름, 포지션, 백넘버, 팀 코드, 팀 이름의 정보로 구성되어 있다.

 

 

B. 뷰 사용

1
2
3
SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME 
FROM V_PLAYER_TEAM 
WHERE PLAYER_NAME LIKE '황%'
 cs

---> V_PLAYER_TEAM의 뷰에서 선수 이름이 '황'으로 시작하는 선수만을 추출하는 SQL문.

 

* DBMS가 내부적으로 SQL문을 재작성한 SQL문

1
2
3
4
5
6
7
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 '황%'
 cs

---> FROM 절에 서브쿼리를 사용하는 인라인 뷰의 형태와 유사하다.

 

 

C. 뷰의 제거

1
2
DROP VIEW V_PLAYER_TEAM; 
DROP VIEW V_PLAYER_TEAM_FILTER;
 cs

 

 

 

 

 

출처

www.dbguide.net/db.db?cmd=view&boardUid=148203&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

 

데이터 전문가 지식포털 DBGuide.net

표준 조인 집합 연산자 계층형 질의와 셀프 조인 서브쿼리 그룹 함수 윈도우 함수 DCL 절차형 SQL 서브쿼리(Subquery)란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다. 서브쿼리는 알려지지

www.dbguide.net

 

저작자표시

'자격증 > SQLD' 카테고리의 다른 글

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #6  (0) 2020.08.31
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #5  (0) 2020.08.31
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #3  (0) 2020.08.28
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #2  (0) 2020.08.27
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #1  (0) 2020.08.27
'자격증/SQLD' Related Articles more
Comments