2장_4절. 서브쿼리(Subquery)
#서브쿼리란
-서브쿼리란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문
-서브쿼리는 메인쿼리에 포함되는 종속적인 관계
#서브쿼리와 조인
-조인과 서브쿼리를 논리적으로 구분하는 가장 좋은 방법은 두 개의 테이블 위치를 바꾸어 보는것
-조인은 두개의 테이블 위치를 바꾸어도 같은결과 / 서브쿼리는 주종관계이므로 다른결과
-조인은 집합간의 곱의 관계(M*N레벨 집합생성) / 서브쿼리는 항상 메인쿼리 레벨로 결과집합 생성
#질의결과에 서브쿼리 칼럼을 표시
-서브쿼리는 메인쿼리의 칼럼을 모두사용가능 / 메인쿼리는 서브쿼리의 칼럼을 사용할수 없음
(1)조인방식으로 변환
(2)함수 사용
(3)스칼라 서브쿼리 사용
#서브쿼리가 SQL문에서 사용가능한 곳
-SELECT절, FROM절, WHERE절, HAVING절, ORDER BY절
-INSERT문의 VALUES절, UPDATE문의 SET절
#동작하는 방식에 따른 서브쿼리 종류
(1)Un-Correlated(비연관) 서브쿼리
-서브쿼리가 메인쿼리의 칼럼을 가지고 있지 않음
-메인쿼리에 값을 제공하기 위한 목적
(2)Correlated(연관) 서브쿼리
-서브쿼리가 메인쿼리의 칼럼을 가지고 있음
#반환 데이터 형태에 따른 서브쿼리 종류
(1)Single Row(단일행) 서브쿼리 : 실행결과가 1건 이하(단일 비교연산자 : =,<,<=,>,>=,<>)
(2)Multi Row(다중행) 서브쿼리 : 실행결과가 여러건(다중 비교연산자 : IN,ALL,ANY,SOME, EXISTS)
(3)Multi Column(다중칼럼) 서브쿼리 : 실행결과가 여러칼럼을 반환
-메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다.
-메인쿼리와 서브쿼리의 비교하고자 하는 칼럼수, 칼럼위치가 동일해야 한다.
1.단일 행 서브쿼리
-단일행 비교연산자와 함께 사용하는 서브쿼리
-결과가 2건 이상이면 SQL은 실행시간 오류를 발생한다.
EX)'정남일'선수가 소속된 팀의 선수들에 대한 정보를 표시하라
SQL> SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO
2 FROM PLAYER
3 WHERE TEAM_ID = (
4 SELECT TEAM_ID
5 FROM PLAYER
6 WHERE PLAYER_NAME = '정남일') <-단일행 연산자는 '='
7 ORDER BY PLAYER_NAME;
-결과가 2건이상이면 SQL은 오류를 발생시킨다 =>'정남일'이 2명이상이면 오류난다.
#그룹함수를 사용하면 결과값이 1건이므로 단일행 서브쿼리로 사용가능하다.
EX)선수들 중 키가 평균이하인 선수들의 정보를 출력하라
SQL> SELECT PLAYER_NAME, POSITION, HEIGHT
2 FROM PLAYER
3 WHERE HEIGHT <=(
4 SELECT AVG(HEIGHT) <-그룹함수 AVG는 결과값이 1건이다.
5 FROM PLAYER)
6 ORDER BY HEIGHT;
2.다중 행 서브쿼리
-다중행 비교연산자와 함께 사용하는 서브쿼리
-서브쿼리의 결과가 반드시 2건이상이어야 오류가 나지않는다.
#다중 행 비교 연산자 종류
(1)IN : 서브쿼리의 결과가 존재하는 임의의 값과 동일한 조건일때 메인쿼리를 출력(Multiple OR조건)
EX)선수들 중 '정현수'라는 선수가 소속되어 있는 팀 정보를 출력하라.(정현수가 2명이상임)
SQL> SELECT REGION_NAME 연고지, TEAM_NAME 팀명
2 FROM TEAM
3 WHERE TEAM_ID IN (
4 SELECT TEAM_ID
5 FROM PLAYER
6 WHERE PLAYER_NAME = '정현수')
7 ORDER BY TEAM_NAME;
(2)비교연산자 ALL : 서브쿼리의 결과가 존재하는 모든값을 만족하는 조건일때 메인쿼리를 출력
(3)비교연산자 ANY : 서브쿼리의 결과중 어느 하나의 값이라도 만족하는 조건일때 메인쿼리를 출력
(4)EXISTS : 서브쿼리의 결과를 만족하는 값이 하나라도 존재하면 메인쿼리를 출력
3. 다중 칼럼 서브쿼리
-서브쿼리의 결과로 여러개의 칼럼이 반환될때 사용
-메인쿼리의 조건과 동시에 비교됨
EX)소속별 키가 가장 작은 사람들의 정보를 출력하라
SQL> SELECT TEAM_ID, PLAYER_NAME, HEIGHT
2 FROM PLAYER
3 WHERE (TEAM_ID,HEIGHT) IN (
4 SELECT TEAM_ID, MIN(HEIGHT)
5 FROM PLAYER
6 GROUP BY TEAM_ID)
7 ORDER BY TEAM_ID, HEIGHT;
4. 연관 서브쿼리(Correlated Subquery)
-서브쿼리 내에 메인쿼리의 칼럼이 사용된 서브쿼리
EX)선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력하라
SQL> SELECT M.TEAM_ID, M.PLAYER_NAME, M.HEIGHT
2 FROM PLAYER M
3 WHERE HEIGHT <(
4 SELECT AVG(HEIGHT)
5 FROM PLAYER S
6 WHERE M.TEAM_ID = S.TEAM_ID <-M.TEAM_ID는 메인쿼리의 칼럼이다
7 GROUP BY S.TEAM_ID)
8 ORDER BY M.TEAM_ID;
#EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다.
EX)'20120501'부터 '20120502'사이에 경기가 있는 경기장을 조회하라.
SQL> SELECT M.STADIUM_ID, M.STADIUM_NAME
2 FROM STADIUM M
3 WHERE EXISTS (
4 SELECT S.STADIUM_ID<-서브쿼리의 WHERE절을 만족하는 STADIUM_ID가 있으면 메인쿼리 결과를 출력한다.)
5 FROM SCHEDULE S
6 WHERE M.STADIUM_ID = S.STADIUM_ID
7 AND S.SCHE_DATE BETWEEN '20120501' AND '20120502');
5. 그 밖에 위치에서 사용하는 서브쿼리
가.SELECT절에서 서브쿼리사용 = 스칼라(Scarlar) 서브쿼리
-한행, 한 칼럼만을 반환하는 서브쿼리
-칼럼을 쓸수있는 대부분의 곳에서 사용할 수 있다.
ex)선수정보와 해당선수가 속한 팀의 평균키를 함께 출력하라.
SQL> SELECT M.PLAYER_NAME, M.HEIGHT,
2 ROUND((<-SELECT절에서 서브쿼리 사용
3 SELECT AVG(HEIGHT)
4 FROM PLAYER S
5 WHERE M.TEAM_ID = S.TEAM_ID
6 GROUP BY S.TEAM_ID),3) 팀평균키
7 FROM PLAYER M
8 ORDER BY PLAYER_NAME;
나. FROM절에서 서브쿼리 사용하기 = 인라인 뷰(Inline View)
-FROM절에서는 테이블명이 와야한다
-FROM절에 서브쿼리를 사용한다는 것은, 서브쿼리의 결과가 마치 동적으로 생성된 테이블 처럼 보인다는 뜻
-인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이므로, DB에 저장되지 않는다.
-인라인 뷰는 테이블명이 올 수 있는 곳에서 사용할 수 있다.
EX)포지션이 미드필더인 선수들의 소속팀명, 선수정보를 인라인뷰를 사용하여 출력하라
SQL> SELECT TEAM_NAME, PLAYER_NAME, BACK_NO
2 FROM (
3 SELECT TEAM_ID, PLAYER_NAME, BACK_NO
4 FROM PLAYER
5 WHERE POSITION = 'MF') INLINE_VIEW, TEAM T
6 WHERE INLINE_VIEW.TEAM_ID = T.TEAM_ID;<-INNER JOIN하기
또는
SQL> SELECT TEAM_NAME, PLAYER_NAME, BACK_NO
2 FROM(<-인라인뷰와 TEAM 테이블을 INNER조인한다.
3 SELECT TEAM_ID, PLAYER_NAME, BACK_NO<-인라인 뷰를 동적생성한다.
4 FROM PLAYER
5 WHERE POSITION = 'MF') INLINE_VIEW INNER JOIN TEAM T
6 ON INLINE_VIEW.TEAM_ID = T.TEAM_ID;
다. HAVING절에서 서브쿼리 사용하기
-HAVING절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 추가적인 조건을 주기위해 사용
-추가적인 조건에 서브쿼리를 사용할 수 있다.
ex)평균키가 삼성의 평균키보다 작은팀의 이름과 해당팀의 평균키를 출력하라.
SQL> SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키
2 FROM PLAYER P, TEAM T
3 WHERE P.TEAM_ID = T.TEAM_ID
4 GROUP BY P.TEAM_ID, T.TEAM_NAME
5 HAVING AVG(P.HEIGHT) <
6 (SELECT AVG(HEIGHT)
7 FROM PLAYER
8 WHERE TEAM_ID = 'K02')<-삼성평균키
9 ORDER BY P.TEAM_ID, AVG(P.HEIGHT);
라. UPDATE문의 SET절에서 사용하기
-칼럼을 추가할때 서브쿼리를 사용할 수 있다.
EX)TEAM테이블에 STADIUM_NAME칼럼을 서브쿼리를 이용해 추가하라.
SQL> UPDATE TEAM A
2 SET A.E_TEAM_NAME = (
3 SELECT X.STADIUM_NAME
4 FROM STADIUM X
5 WHERE X.STADIUM_ID = A.STADIUM_ID);
15 rows updated.
마. INSERT문의 VALUES절에서 사용하기
-칼럼값을 삽일할때 서브쿼리를 사용할 수 있다
EX)PLAYER테이블에 '홍길동'선수를 서브쿼리를 이용해 삽입해라, 이때 PLAYER_ID값을 현재 사용중인 값+1로 바꾼다.
SQL> INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
2 VALUES((
3 SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1)
4 FROM PLAYER), '홍길동', 'K06');
1 row created.
6. 뷰(VIWE)
-테이블은 실제로 데이터를 가지지만, 뷰는 실제 데이터를 가지지 않는다.
-뷰는 단지 뷰의 정의(View Definition)만을 가지고 있다.
-질의에서 뷰가 사용되면, 뷰 정의를 참조해서 DBMS내부적으로 질의를 재작성하여 질의를 수행한다.
-뷰는 데이터는 가지지 않지만, 테이블처럼 수행되기 때문에 가상테이블(Virtual Table)이라고도 한다.
(1)뷰의장점
1)독립성 : 테이블 구조변경이 뷰의 변경에 영향을 미치지 않는다.
2)편리성 : 복잡한 질의를 뷰로 단순하게 작성할 수 있다.
3)보안성 : 급여정보와같이 숨기고 싶은 정보가 있을경우 뷰에서 해당칼럼을 삭제함으로써 정보를 감출 수 있다.
(2)뷰의생성
SQL> create view v_player_team as
2 select p.player_name, p.position, p.back_no, p.team_id, t.team_name
3 from player p, team t
4 where p.team_id = t.team_id;
create view v_player_team as
*
ERROR at line 1:
ORA-01031: insufficient privileges->뷰생성 권한을 허락해야한다.
(3)뷰는 뷰를 통해서도 생성가능하다.
SQL> create view v_player_team_filter as
2 select player_name, position, back_no, team_name
3 from v_player_team
4 where position in('GK','MF');
(4)뷰를 사용하는 경우에는 DBMS가 내부적으로 SQL문을 재작성한다.
SQL> select player_name
2 from v_player_team
3 where player_name like '황%';
이거를->DBMS가 아래 쿼리문으로 재작성
SQL> select player_name
2 from (
3 select p.player_name, p.position, p.back_no, p.team_id, t.team_name
4 from player p, team t
5 where p.team_id = t.team_id)
6 where player_name like '황%';
##인라인 뷰(Inline View)와 유사하게 작동한다.
(5)뷰를 제거하기 위해서는 Drop view문을 사용한다.
SQL> DROP VIEW V_PLAYER_TEAM;
댓글
댓글 쓰기