서브쿼리
서브쿼리는 하나의 SQL문에 포함된 또 다른 SQL 문을 말합니다. 조인은 참여하는 모든 테이블은 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있습니다.
서브 쿼리는 메인쿼리의 하위 쿼리입니다. 서브 쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 칼럼을 사용할 수 없습니다. 메인쿼리의 질의결과(SELECT-LIST)에 서브쿼리의 칼럼을 표시해야 한다면 조인방식으로 변환하거나 사용자정의 함수 또는 Scalar Subquery를 사용해야 합니다. 서브쿼리는 서브쿼리레벨과는 상관없이 항상 메인쿼리레벨로 결과집합이 생성됩니다.
SQL 작성시 서브쿼리방식을 사용해야 할 때, 잘못 판단해 조인방식을 사용하는 경우가 있습니다. 예를 들어, 쿼리의 최종 결과는 부서 레벨이고 사원 테이블에서 조건을 체크해야 할 조건이 존재한다고 가정합니다. 이런 상황에서 조인을 사용하면 결과 집합은 부서 레벨이 됩니다. 원하는 결과가 아니기 때문에 SQL 문에 DISTINCT를 추가해 결과를 다시 조직 레벨로 만드는 추가작업이 필요합니다. 이와 같은 상황에서는 조인 방식이 아니라 서브 쿼리 방식을 사용해야 합니다. 메인쿼리로 조직을 사용하고 서브쿼리로 사원 테이블을 사용하면 결과 집합은 조직 레벨이 되기 때문에 추가적인 작업 없이 원하는 결과를 도출할 수 있습니다.
서브쿼리 활용시 주의점은 다음과 같습니다.
- 서브 쿼리는 괄호로 감싸서 기술해야 합니다.
- 서브 쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능합니다.
- 단일행 비교연산자는 서브 쿼리의 결과가 반드시 1건 이하여야 하고, 복수행 비교연산자는 서브 쿼리의 결과 건수와 상관없이 동작합니다.
- 중첩 서브 쿼리 및 스칼라 서브 쿼리에서는 ORDER BY를 사용할 수 없습니다.
서브쿼리는 메인쿼리 안에 포함된 종속된 쿼리입니다. 따라서 논리적 실행 순서는 항상 메인 쿼리에서 읽힌 데이터를 서브 쿼리에서 해당 조건이 만족하는지 확인하는 방식으로 수행돼야 합니다. 그러나 실제 서브 쿼리의 실행순서는 상황에 따라 달라질 수 있습니다.
단일 행 서브쿼리
서브쿼리가 단일행 비교연산자와 함께 사용될 때 서브 쿼리의 결과 건수가 반드시 1건 이하여야 합니다. 만약 서브 쿼리의 결과 건수가 2건 이상이면 SQL문은 런타임 오류가 발생합니다 이런 종류의 오류는 컴파일 시에는 알 수 없으니 주의해야 합니다.
-- 단일행 서브쿼리
-- 정남일 선수와 같은 팀 선수들의 정보
SELECT PLAYER_NAME, POSITION, BACK_NO
FROM PLAYER
WHERE TEAM_ID = (
SELECT TEAM_ID
FROM PLAYER
WHERE PLAYER_NAME = '정남일'
)
-- 단일행 서브쿼리
-- 선수 평균 키보다 작은 선수들의 정보
SELECT PLAYER_NAME, POSITION, BACK_NO
FROM PLAYER
WHERE HEIGHT <= (
SELECT AVG(HEIGHT) FROM PLAYER
)
ORDER BY PLAYER_NAME;다중 행 서브 쿼리
서브 쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중행 비교 연산자를 사용해야 합니다. 다중 행 비교 연산자는 다음과 같습니다.
- IN : 서브 쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미.
- ALL : 서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건. 서브 쿼리의 조건절에 비교 연산자 >를 사용했다면 메인 쿼리는 서브 쿼리의 모든 결과를 만족해야하므로 서브 쿼리 결과의 최댓값보다 큰 모든 건이 조건을 만족.
- ANY : 서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건. 서브쿼리의 조건절에 비교 연산자 > 를 사용했다면 메인쿼리는 서브 쿼리의 값들 중 어떤 하나의 값보다 크면 되므로, 서브쿼리 결과의 최솟값보다 큰 모든 값이 조건을 만족한다.
- EXIST : 서브 쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미합니다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않습니다.
-- 선수들 중에서 '정현수' 선수가 소속된 팀 정보를 출력하는 서브 쿼리
-- 단일 행 하위 질의에 2개 이상의 행이 리턴
SELECT REGION_NAME, TEAM_NAME, E_TEAM_NAME
FROM TEAM
WHERE TEAM_ID = (
SELECT TEAM_ID
FROM PLAYER
WHERE PLAYER_NAME = '정현수'
)
-- 다중 행 서브 쿼리는 '=' 로는 처리가 불가능하기 때문에 에러를 반환한다.
-- 따라서 다중 행 비교 연산자로 바꾸어 SQL 문을 작성하면 다음과 같다.
SELECT REGION_NAME, TEAM_NAME, E_TEAM_NAME
FROM TEAM
WHERE TEAM_ID IN (
SELECT TEAM_ID
FROM PLAYER
WHERE PLAYER_NAME = '정현수'
)
다중 칼럼 서브 쿼리
다중 칼럼 서브쿼리는 서브 쿼리의 결과로 여러 개의 칼럼이 반환돼 메인 쿼리의 조건과 동시에 비교되는 것을 의미합니다.
-- 소속팀별 키가 가장 작은 사람들의 정보를 출력한다.
-- 소속팀에서 키가 가장 작은 사람의 정보는 GROUP BY 를 이용해 찾을 수 있다.
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
)
실행 결과를 보면 서브 쿼리의 값으로 소속팀 ID 와 소속팀별 가장 작은 키 두 개의 칼럼을 반환했습니다. 메인 쿼리에서는 조건절에 TEAM_ID와 HEIGHT 칼럼을 묶어 서브 쿼리 결과와 비교해 원하는 결과를 얻을 수 있습니다. 실행 결과를 보면 하나의 팀에서 키가 제일 작은 선수를 한 명씩만 반환한 것이 아니라 같은 팀에서 여러 명이 반환됐습니다. 이는 가장 작은 키를 만족하는 선수가 여러 명 존재하기 때문입니다.
WHERE 절에 사용하는 서브쿼리
중첩 서브 쿼리(비연관 서브쿼리)
서브 쿼리 내에 메인 쿼리 칼럼이 사용되지 않은 서브 쿼리입니다.
연관 서브 쿼리
연관서브쿼리는 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리입니다. 서브 쿼리가 WHERE절에 사용되면서 메인쿼리와 연관되어 수행되며 Related Subquery라고 부릅니다. Nested(중첩) 서브쿼리와는 다르게 메인쿼리 질의문의 결과의 각 레코드에 연관되어 수행됩니다.
-- 선수 자신이 속한 팀의 평균키보다 작은 선수들의 정보를 출력하는 SQL 문을 연관 서브 쿼리를 이용해 작성해 보면 다음과 같다.
SELECT B.TEAM_NAME, A.PLAYER_NAME, A.POSITION
,A.BACK_NO, A.HEIGHT
FROM PLAYER A, TEAM B
WHERE 1=1
-- 각각의 키가 소속팀 평균키 보다 작은 경우만 포함
AND 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 PLAYER_NAME;
EXISTS 서브 쿼리는 항상 연관 서브 쿼리로 사용됩니다. EXISTS의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않습니다.
-- 20120501 부터 20120502 사이에 경기가 있는 경기장을 조회하는 SQL 문.
SELECT A.STADIUM_ID, A.STADIUM_NAME
FROM STADIUM A
WHERE EXISTS (
SELECT 1
FROM SCHEDULE X
WHERE X.STADIUM_ID = A.STADIUM_ID
AND X.CHE_DATE BETWEEN '20120501' AND '20120502'
)그 밖의 위치에서 사용하는 서브 쿼리
SELECT 절에 서브 쿼리 사용하기(스칼라 서브쿼리)
SELECT 절에 사용하는 서브쿼리를 스칼라 서브쿼리라고 합니다. 서브쿼리의 결과로 하나의 칼럼 값 만을 반환합니다. 스칼라 서브쿼리는 SELECT 절 이외에도 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있습니다. 스칼라 서브 쿼리는 메인 쿼리의 결과 건수만큼 반복 수행됩니다. 스칼라 서브 쿼리 또한 단일 행 서브 쿼리이기 때문에 결과가 2건 이상 반환되면 SQL 문은 오류를 반환한다.
SELECT A.PLYAER_NAME, A.HEIGHT, ROUND(
(SELECT AVG(X.HEIGTH)
FROM PLAYER X
WHERE X.TEAM_ID = A.TEAM_ID
),3
)
FROM PLAYER A;FROM 절에서 서브쿼리 사용하기(인라인뷰)
FROM 절에서 사용되는 서브 쿼리를 인라인뷰 라고 합니다. 인라인뷰를 사용하면 서브 카레의 결과를 마치 테이블처럼 사용할 수 있습니다. 데이터베이스에 SELECT 문을 객체로 저장해 테이블처럼 사용하는 VIEW와 달리, 인라인 뷰는 쿼리 내에서 즉시처리됩니다
SELECT B.TEAM_NAME, A.PLAYER_NAME, A.BACK_NO
FROM
(
SELECT TEAM_ID, PLAYER_NAME, BACK_NO
FROM PLAYER
WHERE POSITION = 'MF'
) A
, TEAM B
WHERE A.TEAM_ID = B.TEAM_ID
ORDER BY 선수명;
INLINE VIEW에서는 ORDER BY 절을 사용할 수 있습니다. 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 합니다.
-- 인라인 뷰 내에서 선수들의 키를 기준으로 내림차순 정렬한 후, 메인 쿼리에서 ROWNUM을 사용해 5명의 선수 정보만을 추출한다.
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;
HAVING 절에서 서브 쿼리 사용하기
HAVING 절은 집계함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해 사용합니다.
-- 평균키가 삼성 블루윙즈 팀의 평균키보다 작은 팀에 대해 팀 이름과 팀 소속 선수들의 평균키를 구하는 SQL 문
SELECT A.TEAM_ID, B.TEAM_NAME, ROUND(AVG(A.HEIGTH),3)
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 = '삼성블루윙즈'
)
)뷰
테이블은 실제로 데이터를 갖고 있는 반면, 뷰는 실제 데이터를 갖고 있지 않습니다. 뷰는 단지 뷰에 대한 정의만을 갖고 있습니다. 질의에서 뷰가 사용되면 뷰 정의를 참조해 DBMS 내부적으로 재질의 해 질의를 수행합니다.
뷰 사용의 장점은 다음과 같습니다.
- 독립성 : 테이블 구조가 변경돼도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
- 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
- 보안성 : 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성해 감출 수 있다.
뷰는 다음과 같이 CREATE VIEW 문으로 생성할 수 있습니다.
-- 해당 뷰는 선수 정보와 해당선수가 속한 팀명을 함께 추출하는 것이다. 뷰의 명칭은 'V_PLAYER_TEAM' 이다.
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_PLAYR_TEAM_FILTER 는 이미 존재하는 V_PLAYER_TEAM 뷰를 기반으로 해서 생성된 뷰로 선수 포지션이 골키퍼, 미드필더인 선수만을 추출하고자 하는 뷰입니다.
CREATE VIEW V_PLAYR_TEAM_FILTER AS
SELECT PLAYER_NAME, POSTITION, BACK_NO, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE POSITION IN ('GK', 'MF')
-- V_PLAYR_TEAM_FILTER 에서 성이 '황'인 선수만을 추출하는 sql 문이다.
SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME
FROM V_PLAYER_TEAM
WHERE PLAYER_NAME LIKE '황%';
DBMS는 뷰를 사용한 쿼리를 내부적으로 다음과 같이 재질의 하여 재작성합니다.
SELECT PLAYER_NAME, BACK_NO, TEAM_ID, TEAM_NAME
FROM(
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
)
WHERE PLAYER_NAME LIKE '황%'
-- 뷰를 제거하기 위해서는 DROP VIEW 문을 사용한다.
DROP VIEW V_PLAYER_TEAM;
DROP VIEW V_PLAYER_TEAM_FILTER;
