SQLD 4-4 ] 서브쿼리
서브쿼리
- SQL 문 안에 SQL 문.
서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다
서브쿼리를 쓸때 주의점
1. 서브쿼리를 괄호호 감싸서 사용한다
2. 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.
단일 행 비교연산자는 서브쿼리의 결과가 반드시 1건 이하 이여야하고
복수 행 비교연산자는 서브쿼리의 결과 건수와 상관 없다.
3. 서브쿼리에서는 ORDER BY 를 사용하지 못한다.
ORDER BY 는 SELECT 절에서 오직 한 개만 올 수 있어서.. 메인 SELECT 의 마지막에 장식해야한다.
서브쿼리가 가능한 곳
- SELECT 절
- FROM 절
- WHERE 절
- HAVING 절
- ORDER BY 절
- INSERT 문의 VALUES 절
- UPDATE 문의 SET 절
쿼리문 안의 쿼리문인데.. 이렇게나 많은 곳에서 쓸수 있다니
단일 행 서브쿼리
- 서브쿼리가 단일 행 비교연산자(=, <, <=, >, >=, <>) 와 함께 사용할때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다.
이렇게, 서브쿼리 의 결과가 한건 이하여야 한다.
만약.. 정남일 선수가 동명이인이라 2건 이상의 결과가 반환되었다면.. 오류가 발생 될 것이다
또, 그룹 함수를 용해서 결과값이 1건이 생성되서 사용이 가능하다.
다중 행 서브쿼리
- 서브쿼리의 결과가 2건 이상 반환될 수 있다면.. 반드시 다중 행 비교 연산자와 함께 쓰자!!
다중행 : IN (중에 하나) / ALL (모두 만족) / ANY (어떤 하나라도 만족) / EXISTS (결과 만족 존재 확인)
select REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명
from TEAM
where TEAM_ID = (select TEAM_ID
from PLAYER
where PLAYER_NAME ='정현수')
order by TEAM_NAME;
ERROR : 단일 행 하위 질의에 2개 이상의 행이 리턴되었다!
서브쿼리에서 정현수 라는 PLAYER_NAME 이 2건 이 있었다...
그래서 = 로는 매치가 안된다. 다중 행 비교 연산자 로 바꾸어서 작성해야 한다.
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;
다중 칼럼 서브쿼리
- 서브쿼리의 결과가 여러 개의 칼럼이 반환 되서. 다중 칼럼 서브쿼리 라고 한다.
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, 키최소값 의 2개의 칼럼(TEAM_ID 로 그룹된) 을 반환했다.
서브쿼리 결과만 보면..
이거일텐데 최종 결과는 K01 이 하나가 아니라 여러개 있네?
왜냐하면 동일 팀 내에서 제일 작은 키 (170) 을 만족하는 선수가 저렇게 몇명 더 있기 때문이다.
연관 서브쿼리
- 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리..
선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력
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 선수명;
조금 헷갈린다.
SELECT 절에서 서브쿼리 사용하기
select 절에서 사용하는 서브쿼리는 '스칼라 서브쿼리'
한 행, 한 칼럼 만을 반환하는 서브쿼리다.
SELECT PLAYER_NAME 선수명, HEIGHT 키,
(SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키
FROM PLAYER P;
저 괄호는 팀의 평균키를 알아내는 것이다.
WHERE 에 보면 X.TEAM_ID = P.TEAM_ID 을 했으니 한 행 을 보면
AVG(HEIGHT) 를 하면 그 TEAM_ID 의 정보도 같이 따라 오니 팀별 평균키를 알 수 있다.
(계속 생각해봐야됨 좀 헷갈림..)
FROM 절에서 서브쿼리 사용하기
- FROM 절에서 사용되는 서브쿼리는 '인라인 뷰' 라고 한다.
임시적으로 생성되는 테이블. 동적 뷰 (Dynamic View) 라고도 한다
K-리그 선수들 중에서 포지션이 미드필더 인 선수들의 정보 출력
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 선수명;음. 조금 쉽다.
또 이 인라인 뷰 (from 절에서 쓰는거) 에서는 ORDER BY 절을 사용할 수 있다.
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;
ROWNUM. 데이터 출력하는거 제한 하는것.
HAVING 절에서 서브쿼리 사용하기
- HAVING 절은 그룹함수와 함께 사용될 때 결과에 대해 부가적인 조건을 주기 위해서 사용.
선수들의 전체 평균키 가 K02 팀의 평균키 보다 작은 팀의 정보 출력
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 절에서 서브쿼리 사용하기
update TEAM A
set A.E_TEAM_NAME = (select X.STADIUM_NAME from STADIUM X where X.STADIUM_ID = A.STADIUM_ID);TEAM 테이블에는 STADIUM_NAME 칼럼이 없다.
TEAM 테이블에 STADIUM_NAME 칼럼을 추가 했다고 가정하고 그 안에 내용들을 저렇게 select 로 불러와서 수정.
INSERT 절의 VALUES 에서 서브쿼리 사용하기
- PLAYER 테이블에 '홍길동' 이라는 선수를 삽입하고자 하는데, PLAYER_ID 의 값을 현재 사용중인 PLAYER_ID 에 +1 값으로 할 때.
insert into PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
values ( (select TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1 from PLAYER), '홍길동', 'K06');PLAYER_ID 부분에
보면. MAX 로 PLAYER_ID 의 지금 최대값 + 1 한걸 넣었네.
뷰 (View)
- 테이블은 실제로 데이터를 가지고 있는 반면.. 뷰 는 실제 데이터를 가지고 있지 않다!
아까 FROM 에서 서브쿼리 사용한것.. 인라인 뷰 처럼. 실제 테이블 은 아닌데 테이블 처럼 썼던 것.
질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성 해서 질의를 수행한다.
가상 테이블이라고도 한다.
독립성, 편리성, 보안성
VIEW 생성
create view V_PLAYER_TEAM as
select P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME
from PLAYER P, TEAM T
where P.TEAM_ID = T.TEAM_ID;
형태를 보니. create view ~ as ~ ;
이군.
또, 이미 존재하는 뷰에서도 갖다 쓸 수 있다.
create view V_PLAYER_TEAM as
select PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME
from V_PLAYER_FILTER
where POSITION in ('GK','MF');
뷰를 사용하는 방법은, 그냥 테이블 처럼 쓰면 된다.
select PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME
from V_PLAYER_TEAM
where PLAYER_NAME like '황%';
삭제하는 방법은
drop view V_PLAYER_TEAM;