[SQL] 서브쿼리(Subquery)

1. 서브쿼리(SubQuery)

1) 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문

2) 알려지지 않은 기준을 이용한 검색을 위해 사용

 

 

2. 메인쿼리의 질의 결과에 서브쿼리의 컬럼을 표시하기 위한 방법

- 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없음

 1) JOIN 이용

2) 함수 이용

3) 스칼라 서브쿼리 이용

 

 

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

1) 서브쿼리를 괄호로 감싸서 사용해야 함 

2) 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없음.

3) 서브쿼리에서는 ORDER BY를 사용하지 못함. (ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문)

 

 

4. 서브쿼리가 SQL문에서 사용이 가능한 곳

- SELECT 절 - FROM 절 - WHERE 절 - HAVING 절 - ORDER BY 절 - INSERT문의 VALUES 절 - UPDATE문의 SET 절

 

 

WHERE절

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

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

 

[연관 서브쿼리 예제] 선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하는 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 선수명;

 

 

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

종류	설명
단일 행 서브쿼리 (Single Row)	- 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리(2건 이상 반환 시 런타임 에러 발생) - 단일 행 비교 연산자 <, <=, =, >, >=, <> 와 함께 사용함
다중 행 서브쿼리 (Multi Row)	- 서브쿼리의 실행 결과가 여러 건인 서브 쿼리  - 다중 행 비교 연산자 IN, ALL, ANY, SOME, EXISTS와 사용함
다중 컬럼 서브쿼리 (Multi Column)	- 서브쿼리의 실행 결과로 여러 컬럼 반환 - 메인쿼리의 조건절에 여러 컬럼 동시 비교 가능(비교하고자하는 컬럼 개수와 컬럼 위치가 동일해야함) - SQL Server에서는 지원되지 않음

 

- 다중 행 비교 연산자

종류	설명
IN	서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건 의미(Multiple OR 조건)
비교연산자 ALL	서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건 의미 ex) 비교 연산자로 ">"를 사용했다면, 서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족
비교연산자 ANY/SOME	서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건 의미 ex) 비교 연산자로 ">"를 사용했다면, 서브쿼리 결과의 최소값보다 큰 모든 건이 조건 만족
EXISTS	- 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건 의미 - 조건을 만족하는 건이 여러 건이라도 1건만 찾으면 더이상 검색하지 않음 - EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용됨

 

[다중 행 비교연산자 IN 예제] 선수들 중에서 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력하는 서브쿼리 작성

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;

 

[다중 행 비교연산자 EXISTS 예제] EXISTS 서브쿼리를 사용하여 '20120501' 부터 '20120502' 사이에 경기가 있는 경기장 조회

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')

 

 

[다중 컬럼 서브쿼리 예제] 소속팀별 키가 가장 작은 사람들의 정보를 출력 GROUP BY를 이용하여 SQL문 작성할 것

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;

 

서브쿼리의 결과값으로 소속팀코드(TEAM_ID)와 소속팀별 가 장 작은 키를 의미하는 MIN(HEIGHT)라는 두 개의 컬럼을 반환

메인쿼리에서는 조건 절에 TEAM_ID와 HEIGHT 칼럼을 괄호로 묶어서 서브쿼리 결과와 비교하여 원하는 결과를 얻음

 

 

 

SELECT절

SELECT 절에서 사용하는 서브쿼리인 스칼라 서브쿼리(Scalar Subquery)

- 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말함 => 단일행 서브쿼리

- 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있음

 

 

 

FROM 절

FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View) 또는 동적 뷰(Dynamic View)라고 함

- 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있음

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

- 인라인 뷰 를 사용하는 것은 조인 방식을 사용하는 것과 같음, 그렇기 때문에 인라인 뷰의 칼럼은 SQL문 자유롭게 참조 가능

- 인라인 뷰에서는 ORDER BY절을 사용할 수 있다. 인라인 뷰에 먼저 정렬을 수행하고 정 렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 한다. TOP-N 쿼리를 수행하기 위해서는 정렬 작업과 정렬 결과 중에서 일부 데이터만을 추출할 수 있는 방법이 필요하다. Oracle에서는 ROWNUM이라는 연산자를 통해서 결과로 추출하고자 하는 데이터 건수를 제약할 수 있다.

[예제] K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력

 

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 선수명;

 

[예제] 인라인 뷰에서 선수의 키를 내림차순으로 정렬(가장 키가 큰 선수부터 출력) 한 후 메인쿼리에서 ROWNUM을 사용해서 5명의 선수의 정보만을 추출

 

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 절

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 칼럼이 없다. 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');