TOP
class="layout-aside-left paging-number">
본문 바로가기
자격증/SQLD

SQLD 2과목 SQL 활용 - 서브쿼리

by 기록자_Recordian 2024. 1. 15.
728x90
반응형

 

<목차>

 

※ 자료출처: 데이터 자격검정 협회 

 

1. 서브쿼리

서브쿼리(Subquery)
하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문

 

  • 서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용
  • 메인쿼리가 서브쿼리를 포함하는 종속적인 관계

메인쿼리와 서브쿼리 (출처: 데이터 자격검정 협회)

조인과 서브쿼리
  • 조인은 조인에 참여하는 모든 테이블이 대등한 관계 > 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용 가능.
  • 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼 사용 불가
  • 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리 등 사용해야 함.
  • 조인은 집합간의 곱의 관계 > 1:1 관계의 테이블이 조인하면 1(=1*1) 레벨의 집합 생성 / 1:M 관계의 테이블을 조인하면 M(=1*M) 레벨의 집합 생성 / M:N 관계의 테이블을 조인하면 MN(=M*N) 레벨의 집합이 결과로써 생성
  • 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합 생성.
서브쿼리 사용 시 주의점
  • 서브쿼리를 괄호로 감싸서 사용
  • 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능.
  • 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없음.
  • 서브쿼리에서는 ORDER BY 사용 불가. ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 함.
  • 서브쿼리가 SQL 문에서 사용이 가능한 곳
- SELECT 절 - FROM 절 - WHERE 절 - HAVING 절 - ORDER BY 절

- INSERT 문의 VALUES 절 UPDATE문의 SET 절
서브쿼리의 분류
  • 동작하는 방식에 따라
서브쿼리 종류 설명
Un-Correlated (비연관) 서브쿼리 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리.
메인쿼리에 값 (서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용
Correlated (연관) 서브쿼리 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리.
일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용

 

  • 반환되는 데이터의 형태에 따라
서브쿼리 종류 설명
Single Row 서브쿼리
(단일 행 서브쿼리)		 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리 의미.
(서브쿼리의 결과 건수가 2건 이상을 반환하면 SQL문은 실행시간 - Run Time 오류 발생)
단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용.
단일 행 비교 연산자: =, <, <=, ., >=, <> 
Multi Row 서브쿼리
(다중 행 서브쿼리)		 서브쿼리의 실행 결과가 여러 건인 서브쿼리 의미.
다중 행 서브쿼리는 다중 행 비교 연산자와 함께 사용.
다중 행 비교 연산자: IN, ALL, ANY, SOME, EXISTS
Multi Column 서브쿼리
(다중 칼럼 서브쿼리)		 서브쿼리의 실행 결과로 여러 칼럼 반환.
메인쿼리의 조건절에 여러 칼럼을 동시에 비교 가능.
서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 함.

 

[단일 행 서브쿼리]

단일 행 서브쿼리의 예제 (출처: 데이터 자격검정 협회)

 

[다중 행 서브쿼리]

다중 행 연산자 설명
IN (서브쿼리) 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건 으미.
(Multiple OR 조건)
비교연산자
ALL (서브쿼리)		 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건 의미.
비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 모든 결과 값을 만족해야 하므로,
서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족
비교연산자
ANY (서브쿼리)		 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건 의미.
비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도
만족하면 되므로, 서브쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족
(SOME은 ANY와 동일)
EXISTS (서브쿼리) 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건 의미.
조건을 만족하는 건이 여러 건이라도 1건만 찾으면 더 이상 검색하지 않음.

 

예제) 선수들 중에서 '정현수'라는 선수가 소속되어 있는 팀 정보를 출력하는 서브쿼리 작성

[에제]
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개 이상의 행이 리턴되었다.

※ 위의 SQL 문은 서브쿼리의 결과 2개 이상의 행이 반환되어 단일 행 비교 연산자인 "="로는

처리가 불가능하기 때문에 에러 반환.

▼ (다중 행 비교 연산자로 바꾸어 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;
[실행결과]
연고지명 팀명 영문팀명
------- ----------- --------------------------------------------------
전남 드래곤즈 CHUNNAM DRAGONS
FC 성남 일화천마 SEONGNAM ILHWA CHUNMA FC
2개의 행이 선택되었다.

 

[다중 칼럼 서브쿼리]

 

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

[예제]
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개의 행이 선택되었다.

※ SQL Server에서는 미지원

 

[연관 서브쿼리]

 

  • 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리
  • EXISTS 서브쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이라도 조건을 만족하는 1건만 찾으면 추가적인 검색 진행 하지 않음.

예제) EXISTS 서브쿼리를 사용하여 '20120501'부터 '20120502' 사이에 경기가 있는 경기장을 조회하는 SQL문 작성

[예제]
SELECT STADIUM_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개의 행이 선택되었다.

 

 

[그밖에 위치에서 사용하는 서브쿼리]

 

  • SELECT 절에 서브쿼리 사용하기 : 스칼라 서브쿼리(Scalar Subquery)

- 한 행, 한 칼럼 (1 Row 1 Column) 만을 반환하는 서브쿼리

- 단일 행 서브쿼리이기 때문에 결과가 2건 이상 반환되면 SQL 문은 오류 반환

스칼라 서브쿼리 (출처: 데이터 자격검정 협회)

  • FROM 절에서 서브쿼리 사용하기 : 인라인 뷰 (Inline View)

- 인라인뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않음. (동적 뷰, Dynamic View)

- 테이블 명이 올 수 있는 곳에서 사용 가능.

(서브쿼리의 칼럼은 메인쿼리에서 사용할 수 없다고 했으나, 인라인 뷰는 동적으로 생성된 테이블이라 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같음.)

 

예제) 인라인 뷰를 활용하여 K-리그 선수들 중에서 포지션이 미드필더 (MF)인 선수들의 소속팀명 및 선수 정보 출력 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 선수명;

 

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

예제) TEAM 테이블에 STADIUM_NAME을 추가 (ALTER TABLE ADD COLUMN) 하였다고 가정할 때,

TEAM 테이블에 추가된 STADIUM_NAME의 값을 STADIUM 테이블을 이용하여 변경하고자 할 때 SQL 문 작성

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

 

  • INSERT 문의 VALUES 절에서 사용하기

예제) PLAYER 테이블에 '홍길동'이라는 선수를 삽입하고자 한다. 이때 PLAYER_ID의 값을 현재 사용 중인 PLAYER_ID에 1을 더한 값으로 넣고자 할 때 SQL 문 작성

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1)
FROM PLAYER), '홍길동', 'K06');
뷰 (VIEW)
뷰는 실제로 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행 (가상 테이블)

 

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

 

  • 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;

 

  • VIEW를 제거하기 위해서는 DROP VIEW 문을 사용
DROP VIEW V_PLAYER_TEAM;
DROP VIEW V_PLAYER_TEAM_FILTER;

 

[도움이 되는 글]

 

 

SQLD 2과목 SQL 활용 - 서브쿼리 (보강)

[예전에 올렸던 글] SQLD 2과목 SQL 활용 - 서브쿼리 서브쿼리 집합연산자 그룹 함수 윈도우 함수 TOP N 쿼리 계층형 질의와 셀프 조인 PIVOT 절과 UNPIVOT 절 정규 표현식 ※ 자료출처: 데이터 자격검정

puppy-foot-it.tistory.com

이전글

SQLD 2과목 SQL 기본 및 활용 - 표준 조인

 

다음글

집합연산자

728x90
반응형

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

SQLD 2과목 SQL 활용 - 그룹 함수  (1) 2024.01.23
SQLD 2과목 SQL 활용 - 집합연산자  (1) 2024.01.16
SQLD 2과목 SQL 기본 및 활용 - 표준 조인  (0) 2024.01.14
SQLD 2과목 SQL 기본 및 활용 - 조인  (1) 2024.01.13
SQLD 2과목 SQL 기본 및 활용 - GROUP BY, HAVING 절  (1) 2024.01.12

관련글

티스토리툴바