2-2-1. 서브쿼리

 

---

정말 오랜만에... SQL 전문가 가이드 포스팅을 하네요! 😅

2과목 2장의 첫절, 서브쿼리 입니다!

---

반응형

과목 II. SQL 기본과 활용
  - 제2장 SQL 활용
    > 제1절 서브쿼리

 

서브쿼리(subquery)란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.

조인은 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다.

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

질의 결과에 서브 쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하여 표현하거나, 함수, 스칼라 서브쿼리(Scalar Subquery)등을 사용해야 한다.

 

조인은 집합간의 곱 관계이다. 쉽게 말해 1:1관계의 테이블이 조인하면 1*1=1 레벨의 집합이 생성되고, 1:M 관계의 테이블을 조인하면 1*M=M레벨의 집합이 결과로 생성된다. 그리고 M:N관계의 테이블을 조인하면 M*N=MN레벨의 집합이 결과로 생성되는 것이다.

그러나 서브쿼리는 서브쿼리 레벨과 상관없이 항상 메인 쿼리 레벨로 결과 집합이 생성된다.예를 들어 메인쿼리로 조직(1), 서브쿼리로 사원(M) 테이블을 사용하면 결과 집합은 조직(1)레벨이 된다.

 

서브쿼리를 사용할 때 다음 사항에 주의해야 한다.1. 서브쿼리는 괄호로 감싸서 기술한다.2. 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브 쿼리의 결과가 반드시 1건 이하이어야 하고, 복수 행 비교 연산자는 서브 쿼리의 결과 건수와 상관 없다.3. 중첩 서브쿼리 및 스칼라 서브쿼리에서는 ORDER BY 를 사용할 수 없다.

 

서브쿼리는 다음과 같이 동작하는 방식이나 반환되는 데이터 형태에 따라 분류할 수 있다.

 

동작하는 방식에 따른 서브 쿼리 분류

서브 쿼리 종류	설명
Un-Correlated(비연관) 서브쿼리	서브 쿼리가 메인 쿼리 칼럼을 갖고 있지 않는 형태의 서브 쿼리다. 메인 쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용된다.
Correlated(연관) 서브쿼리	서브쿼리가 메인 쿼리 칼럼을 갖고 있는 형태의 서브 쿼리다. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브 쿼링서 조건이 맞는지 확인할 때 주로 사용된다.

 

반환되는 데이터의 형태에 따른 서브 쿼리 분류

서브 쿼리 종류	설명
Single Row 서브 쿼리 (단일 행 서브 쿼리)	서브 쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미한다. 단일 행 서브쿼리는 단일 행 비교 연산자(=,<,<=,>,>=,<> 등) 와 함께 사용된다.
Multi Row 서브 쿼리 (다중 행 서브 쿼리)	서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다. 다중 행 서브쿼리는 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 사용된다.
Multi Column 서브 쿼리 (다중 칼럼 서브 쿼리)	서브 쿼리의 실행 결과로 여러 칼럼을 반환 한다. 메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

 

1. 단일 행 서브 쿼리

서브 쿼리가 단일 행 비교 연산자(=,<,<=,>,>=,<> 등)와 함께 사용될 때는 서브 쿼리의 결과 건수가 반드시 1건 이하여야 한다. 만약 결과 건수가 2건 이상 반환하면 SQL문은 Run Time 오류가 발생한다. 이런 종류의 오류는 컴파일 할때는 알 수 없는 오류이다.

 

단일 행 서브 쿼리의 예시로 '무릴로'선수가 소속된 팀의 선수들에 대한 정보를 표시하는 문제를 서브쿼리 방식의 SQL문으로 작성하면 다음과 같다.

SELECT PLAYER_NAME AS 선수명, POSITION AS 표지션, BACK_NO AS 백넘버
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID
				FROM PLAYER
                WHERE PLAYER_NAME = '무릴로')
ORDER BY PLAYER_NAME;

결과는 다음과 같다

선수명	포지션	백넘버
라스	FW	9
김현	FW	7
박주호	DF	6
...	...	...

무릴로 선수의 소속팀을 알아내는 서브 쿼리가 먼저 수행되어 무릴로 선수의 소속팀ID가 반환된다. 메인쿼리는 서브쿼리에서 반환된 결과를 이용해서 조건을 만족하는 선수들의 정보를 출력한다. 만약 무릴로 선수와 같은 이름을 가진 동명이인이 있었다면, 2건 이상의 결과가 반환돼 SQL문에 오류가 발생 할 것이다. 

테이블 전체에 대해 집계함수를 적용할 때는 그 결과 값이 단 1건만 생성되기 때문에 단일 행 서브 쿼리로서 사용할 수 있다. 

 

2. 다중 행 서브 쿼리

서브 쿼리의 결과가 2건 이상 반환될 수 있다면, 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다. 그렇지 않으면 SQL문은 오류를 반환한다.

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

 

위에서 설명했듯 서브쿼리의 결과가 2건 이상일 경우에는 단일 행 비교 연산자가 아닌 다중 행 비교연산자로 처리해야 한다.

SELECT REGION_NAME AS 연고지명, TEAM_NAME AS 팀명
FROM TEAM
WHERE TEAM_ID IN (SELECT TEAM_ID
					FROM PLAYER
                    WHERE PLAYER_NAME = '김민혁')
ORDER BY TEAM_NAME;

연고지명	팀명
성남	성남FC
전남	전남드래곤즈

실행 결과를 보면 '김민혁' 이라는 이름을 가진 선수가 두명이 존재한다. 소속팀은 각각 전남 드래곤즈 팀과 성남FC 팀이다.

동명이인의 경우 이욍도 서브 쿼리의 실행 결과가 2건 이상이 나오는 모든 경우에 다중 행 비교 연산자를 사용해야 한다.

 

3. 다중 칼럼 서브 쿼리

다중 칼럼 서브 쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환 돼 메인 쿼리의 조건과 동시에 비교되는 것을 의미한다. (SQL Server에서는 지원되지 않는다)

소속팀별 키가 가장 작은 사람들의 정보를 출력하는 문제를 갖고 다중 칼럼 서브쿼리를 알아보도록 한다.

소속팀별 키가 가장 작은 사람들의 정보는 GROUP BY를 이용해 찾을 수 있다.

SELECT TEAM_ID AS 팀코드, PLAYER_NAME AS 선수명, POSITION AS 포지션, HEIGHT AS 키
FROM PLAYER
WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN (HEIGHT) 
							FROM PLAYER 
                            GROUP BY TEAM_ID)
ORDER BY TEAM_ID, PLAYER_NAME;

서브 쿼리의 결과 값으로 소속팀ID(TEAM_ID)와 소속팀별 가장 작은 키를 의미하는 MIN(HEIGHT)라는 두개의 컬럼을 반환했다. 메인쿼리에서는 조건절에 TEAM_ID와 HEIGHT 칼럼을 괄호로 묶어 서브 쿼리 결과와 비교해 원하는 결과를 얻었다. 실행 결과를 보면 하나 팀에서 키가 제일 작은 선수 한 명힜만 반환된 것이 아니라 같은 팀에서 여러 명이 반환된 것을 확인할 수 있다. 이것은 동일 팀 내에서 조건을 만족하는 선수가 여러 명 존재하기 때문이다.

 

4. 연관 서브쿼리

연관 서브 쿼리(Correlated Subquery)는 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리다. 선수 자신이 속한 팀의 평균키보다 큰 선수들의 정보를 출력하는 SQL 문을 연관 서브 쿼리를 이용하여 작성하면 다음과 같다.

SELECT B.TEAM_NAME AS 팀명, A.PLAYER_NAME AS 선수명, A.BACK_NO AS 백넘버, A.HEIGHT AS 키
FROM PLAYER A, TEAM B
WHERE A.HEIGHT > (SELECT AVG(X.HEIGHT)
					FROM PLAYER X
                    WHERE X.TEAM_ID = A.TEAM_ID
                    GROUP BY X.TEAM_ID)
   AND B.TEAM_ID = A.TEAM_ID
ORDER BY 선수명;

팀명	선수명	백넘버	키
수원FC	라스	9	197
수원FC	김현	7	190
FC서울	오스마르	5	192
...	...	...	...

예를 들어 김현 선수는 수원FC 소속이므로 수원FC 팀 소속 선수들의 평균키를 구하고, 김현 선수의 키가 그 평균키보다 크므로 해당 선수의 정보를 출력한다. 이와 같은 작업을 메인 쿼리에 존재하는 모든 행에 대해서 반복 수행한다.

 

EXISTS 서브 쿼리는 항상 연관 서브 쿼리로 사용된다. 또한 EXISTS 서브 쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다는 것이다.

다음은 EXISTS 서브쿼리를 사용해 '20221001'부터 20221002' 사이에 경기가 있는 경기장을 조회하는 SQL문이다.

SELECT A.STADIUM_ID AS ID, A.STADIUM_NAME AS 경기장명
FROM STADIUM A
WHERE EXISTS (SELECT 1
				FROM SCHEDULE X
                WHERE X.STADIUM_ID = A.STADIUM_ID
                	AND X.SCHE_DATE BETWEEN '20221001' AND '20221002');

ID	경기장명
K02	서울월드컵경기장
K03	인천축구전용경기장
K06	전주월드컵경기장
K09	제주월드컵경기장
K01	수원종합운동장

 

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

SELECT 절에 서브쿼리 사용하기

SELECT절에서 사용하는 스칼라 서브쿼리(Scalar Subquery)에 대해 알아본다. 스칼라 서브쿼리는 한행, 한칼럼만을 반환하는 서브쿼리를 말한다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.

선수 정보와 해당 선수가 속한 팀의 평균키를 함께 출력하는 예제로 스칼라 서브 쿼리를 설명하면 다음과 같다.

SELECT A.PLAYER_NAME AS 선수명, A.HEIGHT AS 키
	, ROUND ((SELECT AVG (X.HEIGHT)
    			FROM PLAYER X
                WHERE X.TEAM_)ID = A.TEAM_ID, 3) AS 팀평균키
FROM PLAYER A;

선수들의 정보를 출력하는 메인쿼리 부분과 선수의 소속팀별 평균키를 알아내는 서브쿼리 부분으로 구성된다. 여기서 선수의 소속팀별 평균키를 알아내는 스칼라 서브 쿼리는 메인 쿼리의 결과 건수만큼 반복수행된다.

선수명	키	팀평균키
라스	197	182.58
이승우	173	182.58
김현	190	182.58
...	...	...

스칼라 서브쿼리 또한 단일행 서브쿼리이기 때문에 결과가 2건이상 반환되면 SQL문은 오류를 반환한다.

 

FROM 절에서 서브쿼리 사용하기

FROM절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다. 인라인 뷰를 사용하면 서브 쿼리의 결과를 마치 테이블처럼 사용할 수 있다. 데이터베이스에 SELECT 문을 객체로서 저장해 테이블처럼 사용하는 뷰와 달리, 인라인 뷰는 쿼리 내에서 즉시 처리된다.

K리그 선수들 중에 포지션이 미드필더(MF)인 선수들의 소속팀 명 및 선수 정보를 출력하고자 할때 인라인뷰를 활용하면 다음과 같다.

SELECT B.TEAM_NAME AS 팀명, A.PLAYER_NAME AS 선수명, A.BACK_NO AS 백넘버
FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO
		FROM PLAYER
        WHERE POSITION = 'MF') A
WHERE B.TEAM_ID = A.TEAM_ID
ORDER BY 선수명;

팀명	선수명	백넘버
수원FC	무릴로	10
수원FC	이기혁	23
인천유나이티드	아길라르	10
인천유나이티드	이동수	16
...	...	...

선수들 중에서 포지션이 미드필더인 선수들을 인라인 뷰를 통해 추출하고 인라인 뷰의 결과와 TEAM 테이블과 조인해서 팀명을 출력하고 있다.

 

HAVING 절에서 서브쿼리 사용하기

HAVING 절은 집계함수와 함께 사용될 때 그룹핑 된 결과에 대해 부가적인 조건을 주기 위해 사용한다. 평균키가 삼성 블루윙즈 팀의 평균키보다 작은 팀에 대해 팀 이름과 팀 소속 선수들의 평균키를 구하는 SQL문을 작성하면 다음과 같다.

SELECT A.TEAM_ID AS 팀코드, B.TEAM_NAME AS 팀명
	, ROUND (AVG (A.HEIGHT), 3) AS 평균키
FROM PLAYER A, TEAM B
WHERE B.TEAM_ID = A.TEAM_ID
GROUP BY A.TEAM_ID, B.TEAM_NAME
HAVING AVG (A.HEIGHT) < (SELECT AVG (X.HEIGHT)
							FROM PLAYER X
                            WHERE X.TEAM_ID IN (SELECT TEAM_ID
                            					FROM TEAM
                                                WHERE TEAM_NAME = '수원FC'));

 

6. 뷰

테이블은 실제로 데ㅔ이터를 갖고 있는 반면, 뷰(VIEW)는 실제 데이터를 갖고 있지 않다. 뷰는 단지 뷰 정의(View Definition)만을 갖고 있다. 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성 해 질의를 수행한다.

뷰는 다음과 같은 장점을 갖는다.

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

뷰는 다음과 같이 CREATE VIEW 문으로 생성할 수 있다.

CREATE VIEW V_PLAYER_TEAM AS 
SELECT A.PLAYER_NAME, A.POSITION, A.BACK_NO, B.TEAM_ID, B.TEAM_NAME
FROM PLAYER A, TEAM B
WHERE B.TEAM_ID = A.TEAM_ID;

해당 뷰는 선수 정보와 해당 선수가 속한 팀 명을 함께 추출하는 것이다. 뷰의 명칭은 'V_PLAYER_TEAM'이다.

뷰는 테이블 뿐만 아니라 이미 존재하는 뷰를 참조해서도 생성할 수 있다.

CREATE VIEW V_PLAYER_TEAM_FILTER
SELECT PLAYER_NAME, POSITION, BACK_NO
FROM V_PLAYER_TEAM
WHERE POSION IN ('GK', 'MF');

V_PLAYER_TEAM_FILTER 뷰는 이미 앞에서 생성한 V_PLAYER_TEAM뷰를 기반으로 해서 생성된 뷰로, 선수 포지션이 골키퍼(GK), 미드필더(MF)인 선수만을 추출하고자 하는 뷰다.

뷰를 사용하는 방법은 다음과 같다.

SELECT PLAYER_NAME, POSION, BACK_NO, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '김%';

PLAYER_NAME	POSITION	BACK_NO	TEAM_NAME
김건웅	MF	14	수원FC
김현	FW	7	수원FC
김동준	GK	1	제주유나이티드FC
...	...	...	...

V_PLAYER_TEAM뷰에서 성이 '김'인 선수만을 추출하는 SQL문이다. 

뷰를 사용하는 경우에 DBMS가 내부적으로 SQL문을 다음과 같이 재작성 한다.

SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME
FROM (SELECT A.PLAYER_NAME, A.POSITION, A.BACK_NO, B.TEAM_NAME
		FROM PLAYER A, TEAM B
        WHERE B.TEAM_NAME = A.TEAM_NAME)
WHERE PLAYER_NAME LIKE '김%';

앞에서 설명했던 인라인 뷰와 유사한 모습임을 알 수 있다.

 

뷰를 제거하기 위해서는 DROP VIEW문을 사용한다.

DROP V_PLAYER_TEAM;

---

정말 오랜만에 SQL 전문가 가이드 포스팅을 올립니다!

오랜만에 하려니까 내용이 너무 많네요 ㅠㅠ

서브쿼리의 기본 개념을 익히시고, 예제를 통해 이해하셨으면 좋겠습니다 :)

---

본 포스팅은 '한국데이터산업진흥원' 에서 발행한 'SQL 전문가 가이드' 를 참고/인용 하였음을 밝힙니다.