sqld: sql 활용 - 서브쿼리 정리

서브쿼리 개요

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

서브쿼리 사용시 주의사항

1. 괄호로 감싸야함
2. 단일행/복수행 비교연산자와 함께 사용 가능. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
3. ORDER BY 사용불가. 메인쿼리 마지막 문장에 위치해야함 (Select 절에서 오직 한개)

서브쿼리가 SQL문에서 사용이 가능한 곳은 다음과 같다.

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

단일행 서브쿼리

• 서브쿼리가 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하이어야 한다.
• 서브쿼리의 결과 건수가 2건 이상을 반환하면 SQL문은 실행시간(Run Time) 오류가 발생한다.

이렇게 결과가 한건 이하여야함. 동명이인이라 2건이 반환된다면 오류 발생

SELECT PLAYER_NAME, POSITION
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;

또다른 예시는 키가 평균 이하인 선수들의 정보를 출력

선수들의 평균키를 알아내는 SQL문 → 이 결과를 이용해서 실제 SQL 메인퀄이ㅔ 사용

SELECT PLAYER_NAME, POSITION, BACK_NO
FROM PLAYER
WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER)
ORDER BY PLAYER_NAME;

다중행 서브쿼리

서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용

IN (중에 하나), ALL (모두 만족해야함), ANY(어떤 하나라도 만족) , EXISTS (결과 만족하는 존재확인)

장현수 선수가 소속된 팀 정보를 출력하는 서브쿼리?

SELECT TEAM_NAME
FROM TEAM
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME='장현수')
-- ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었다.

따라서 =를 사용하지 않고 다중 행 연산자인 IN을 사용

SELECT TEAM_NAME
FROM TEAM
WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME='장현수')

다중 칼럼 서브쿼리

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

ex) 소속팀별 키가 가장 작은사람들의 정보 출력?

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

서브쿼리의 결과값으로는 team_id로 그룹된 TEAM_ID, 키 최소값 2개의 칼럼이 나옴

서브쿼리 결과값

전체 결과값

연관 서브쿼리

서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하는 SQL문을 연관 서브쿼리를 이용해서 작성

SELECT T.TEAM_NAME M.PLAYER_NAME M.POSITION, M.BACK_NO, M.HEIGHT
FROM PLAYER AS M, TEAM AS T
WHERE M.TEAM_ID = T.TEAM_ID AND M.HEIGHT < (
	SELECT AVG(SUB.HEIGHT)
	FROM PLAYER AS SUB
	WHERE SUB.TEAM_ID = M.TEAM_ID
	AND SUB.HEIGHT IS NOT NULL
	GROUP BY SUB.TEAM_ID
)

• 보면 WHERE절 안에 있는 값(소속팀의 평균 키)가 해당 선수 (메인쿼리에서 비교)와 비교해서 더 적으면 메인쿼리 출력.
• 왜 연관 서브쿼리인지 잘 모르겠음

또다른 예시로 EXISTS 서브쿼리 (EXISTS는 항상 서브쿼리로 사용함)

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

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

SELECT STADIUM_NAME
FROM STADIUM AS A
WHERE EXISTS (
	SELECT 1
	FROM SCHEDULE AS X
	WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHE_DATE BETWEEN '20120501' AND '20120502'
)

그밖의 위치에서 사용하는 서브쿼리 (SELECT, FROM, HAVING, UPDATE, INSERT)

SELECT 절에 서브쿼리 사용하기

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

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

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

소속팀의 평균키를 알아내는 SQL (스칼라 서브쿼리)는 메인쿼리 결과만큼 반복 수행

SELECT PLAYER_NAME, HEIGHT, (
	SELECT AVG(HEIGHT)
	FROM PLAYER AS X
	WHERE X.TEAM_ID = P.TEAM_ID
	AS 팀평균키
)
FROM PLAYER AS P

• 보면 select 절에서 팀평균키 칼럼을 구하기 위해서 메인쿼리의 한 칼럼마다 서브쿼리 (평균키 구하는 쿼리)가 수행됨

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

• FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)

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

• 그래서 일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다

• 앞에서는 서브쿼리 칼럼을 메인쿼리에서 사용할 수 없다고 했지만, 인라인뷰는 동적으로 생성된 테이블이므로 (조인 방식을 사용하는것과 같으므로) 인라인뷰의 칼럼은 자유롭게 참조 가능.

• 또한 인라인뷰에서는 ORDER_BY도 사용 가능

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

• 자유롭게 P(인라인 뷰)의 칼럼들을 사용할수 있는게 보인다.

인라인 뷰에서 먼저 정렬을 수행한 이후 그 결과 중 일부 데이터를 추출하는것을 TOP-N 쿼리라고 함.

이를 위해서는 결과 중 일부 데이터만을 추출할 수 있는 방법 필요 → 오라클에서는 ROWNUM 연산자 사용

-- 모든 선수들 중에서 가장 키가 큰 5명의 선수
-- ORACLE ex
SELECT PLAYER_NAME, HEIGHT
FROM (
	SELECT PLAYER_NAME, HEIGHT
	FROM PLAYER
	WHERE HEIGHT IS NOT NULL
	ORDER BY HEIGHT DESC
)
WHERE ROWNUM <= 5;

-- SQL Server ex
SELECT TOP(5) PLAYER_NAME, HEIGHT
FROM PLAYER
WHERE HEIGHT IS NOT NULL
ORDER BY HEIGHT DESC

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, insert 에서도 사용가능...

뷰 (View)

• 테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다.
• 뷰는 단지 뷰 정의(View Definition)만을 가지고 있다.
• 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다.
• 뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 한다.
• 이미 존재하는 뷰를 참조해서도 생성 가능

VIew 생성

CREATE VIEW 문을 통해 생성 가능.

-- 해당 선수 정보와 팀명을 함께 추출하는 V_PLAYER_TEAM
CREATE VIEW V_PLAYER_TEAM AS
SELECT P.PLAYER_NAME, P.POSITION, T.TEAM_NAME
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID;

이미 존재하는 뷰를 참조해서 생성도 가능

-- V_PLAYER_TEAM을 기반으로 GK, MF만 추출
CREATE VIEW V_PLAYER_TEAM_FILTER AS
SELECT PLAYER_NAME, POSITION, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE POSITION IN ('GK', 'MF');

• 뷰를 포함하는 뷰는 잘못생성하면 성능상 문제 유발 가능.

View 사용

해당 view의 이름을 사용하면 됨

view를 사용하면 DBMS가 내부적으로 SQL문을 다시 작성함

SELECT PLAYER_NAME, POSITION, TEAM_NAME 
FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '황%'

View 삭제

DROP VIEW 사용해서 제거

DROP VIEW V_PLAYER_TEAM;
DROP VIEW V_PLAYER_TEAM_FILTER;