서브쿼리(Sub Query)

서브쿼리 : 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말함

특징

• 메인쿼리는 출력결과에 서브쿼리의 칼럼표시는 불가능
  
  아래 2가지 방법)
  조인 방식으로 변환
  스칼라 서브쿼리

주의할 점

• 서브쿼리는 괄호로 감쌈

• 복수, 단일 행 모두 가능, 단일행의 경우 연산자가 사용 가능하며 결과가 반드시 1건 이하여야함

• 서브쿼리는 ORDER BY를 사용하지 못함, SELECT 절에서 오직 한번만 사용 가능

서브쿼리의 종류

• 비연관(un-correlated) : 서브쿼리가 메인쿼리의 값을 갖지 않는 형태

• 연관(crrelated) : 서브쿼리가 메인쿼리의 칼럼을 가지고 있는 형태

반환되는 형태

• 단일 행(single row) : 실행 결과가 항상 1건 이하,  단일 행 비교 연산자 사용 가능

• 다중 행(multi row) : 다중 행 비교 연산자 사용 가능, IN, ALL, ANY, EXISTS

• 다중 열(multi column) : 메인쿼리의 조건절에 여러 칼럼을 동시 비교 가능

1. 단일 행 서브쿼리

• 실행 결과가 항상 1건 이하

• 사용 가능한 단일 행 비교 연산자 : =, <, >, <=, >=, <>

• 다 건이라면 SQL 실행 중 오류 발생

2. 다중 행 서브쿼리

• 2건 이상 반환

• 다중 행 비교 연산자 사용
  IN : multiple or condition, 임의의 값과 동일한 조건
  비교연산자 ALL : 서브쿼리의 결과 값에 모두 만족해야 하는 조건
  비교연산자 ANY : 서브쿼리에 결과 값에 어느 하나라도 만족하는 조건
  EXISTS : 서브쿼리에 결과를 만족하는 값이 존재하는지 확인

3. 다중 열(컬럼) 서브쿼리

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

• 예시)
  SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
  FROM PALYER
  WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)
  FROM PLAYER
  GROUP BY TEAM_ID)
  ORDER BY TEAM_ID, PLAYER_NAME;
  
  

4. 연관 서브쿼리

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

• 예제)
  SELECT T.TEAM_NAME, M.PLAYER_NAME, M.POSITION, M.MACK_NO,
  M.HEIGHT
  FROM PALYER 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 M.PLAYER_NAME
  
  

• 예제2:EXISTS)
  SELECT STADIUM_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’

5. 그 밖에 위치에서 사용하는 서브쿼리

SELECT 절에 서브쿼리 사용하기

SCALAR SUBQUERY : REUTRN 1ROW 1COLUMN

SELECT PLAYER_NAMD, HEIGHT, ROUCH ( (SELECT AVG(HEIGHT)

  FROM PLAYER X

  WHERE X.TEAM_ID = P.TEAM_ID),3)

FROM PLAYER P

FROM 절에서 서브쿼리 사용 : INLINE VIEW

• 인라인 뷰랑 칭함

• inline view에서는 order by를 사용가능

• 일부 데이터를 추출하느 것을 TOP-N쿼리라고 부름

• oracle에서는 ROWNUM을 통해 가능

• 중복데이터까지 출력할려면 RANK관련 함수를 사용

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

TEAM_ID=’K02’)

UPDATE문의 SET절에서 사용하기

예제) STANIUM 칼럼이 TAEM TABLE에 역정규화 되어 추가되었을 때 데이터 업데이트 방법

UPDATE TEAM A

SET A.E_TEAM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X

WHERE X.STADIUM_ID = A.STADIUM_ID)

INSERT 문의 VALUES절에서 사용하기

INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
VALUES ((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), ‘홍길동’, ‘K06’)

6. 뷰

특징

• 뷰는 뷰정의만을 가지고 있고 물리적은 데이터는 가지고 있지 않음

• 사실상 쿼리 ALIAS

• 가상테이블이라고도 함

• 뷰가 뷰를 참조할 수 있음

장점

• 독립성 : 테이블이 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 됨

• 편리성 : 복잡한 질의를 단순하게 작성 가능

• 보안성 : 공개하고 싶은 정보만 공개가 가능함

사용

CREAT VIEW V_PLAYER_TEAM AS

SELECT P.PLAYER, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID