문과생이 공대에서 살아남기

하나의 SQL문안에 포함되어 있는 또 다른 SQL문

알려지지 않은 기준을 이용해 검색하기 위해 사용

서브쿼리는 메인쿼리의 칼럼을 모두 사용 가능

메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음 (필요하다면 JOIN으로 변경하거나 스칼라 서브쿼리를 사용해야함)

서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과로 생성되어 1:M의 관계에서도 결과는 1이 됨

주의사항

1. 서브쿼리를 괄호로 감싸서 사용해야함
2. 단일행 또는 복수행 비교 연산자와 함께 사용가능

단일 행 비교연산자는 서브쿼리 결과가 반드시 1건 이하여야 하고

복수 행 비교연산자는 결과 건수와 상관없음

3. 서브쿼리는 ORDER BY를 사용하지 못함, 메인쿼리의 마지막 문장에 위치해야함
4. 사용 가능한 곳 - SELELC, FROM, WHERE, HAVING, ORDER BY, INSERT의 VALUES절, UADATE의 SET절

동작하는 방식에 따른 서브쿼리 분류

• UN-CORRELATED (비연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브쿼리

메인쿼리에 값(서브쿼리가 실행된결과)을 제공하기 위한 목적으로 사용

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

      메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리 조건에 맞는지 확인하고자 할 때 주로 사용

반환되는 데이터의 형태에 따른 서브 쿼리 분류

1. 단일 행 서브쿼리

단일 행 비교 연산자 ( =, >, >=, <, <=, <>) 와 함께 사용할 때는 서브쿼리 결과가 반드시 1건 이하여야 함

2건 이상을 반환하면 SQL문은 실행시간 오류가 발생, 컴파일할 때 알수 없는 오류

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

 FROM PLAYER

 WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER)

 ORDER BY PLAYER_NAME;

2. 다중 행 서브쿼리

결과가 2건 이상이면 다중 행 비교 연산자 ( IN, ALL, ANY, SOME)을 사용해야 함

다중행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용가능

3. 다중 칼럼 서브쿼리

여러 개의 칼럼이 반환된어 메인쿼리의 조건과 동일하게 비교되는 것을 의미, SQL SERVER에서는 지원되지 않음

예제)  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;

4. 연관 서브 쿼리

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

EXISTS 서브쿼리 : 만족하는 1건만 찾음 추가검색 진행하지 않음

예제) SELECT STADIUM_ID 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 절

스칼라 서브쿼리란 ? 한 행, 한 컬럼만을 반환하는 서브쿼리, 단일 행 서브쿼리 (2건이상이면 오류)

예제) SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키

 FROM PLAYER P;

나. FROM 절

인라인뷰(INLINE VIEW), 동적뷰 (DYNAMIC VIEW)

서브쿼리 결과가 동적으로 생성된 테이블인 것 처럼 사용 가능, 데이터 베이스에는 해당 정보가 저장되지 않음

조인방식을 사용하는 것과 같아서 인라인 뷰의 칼럼은 SQL에 자유롭게 참조 가능

ORDER BY 사용가능,

TOP-N 쿼리 : 인사인 뷰에서 먼저 정렬을 수행하고 결과 중에 일부 데이터만 추출하는 것, ORACLE은 ROWNUM으로 사용가능

예제) 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;

• 데이터까지 전부 추출하고싶으면 RANK 함수 사용

다. HAVING 절

그룹핑 결과에 대한 부가적인 조건 주기 위해서

라. UPDATE 문의 SET 절

새로운 테이블에 이전 테이블의 값을 옮기고 싶을 때

NULL값을 반환할 경우 칼럼 결과가 NULL이 될수 있음으로 주의해야함

마. INSERT 문의 VALUES 절

새로운 테이블에 이전 테이블의 값, 또는 수정값을 옮길 때

6. 뷰(VIEW)

가상 테이블(VIRTUAL TABLE) 실제 데이터를 가지고 있지 않음, 뷰 정의(VIEW DEFINITION)을 가지고 있음

뷰 정의를 참조해서 질의를 재 작성하여 질의를 수행

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

실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 가진 DBMS도 있음

장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 프로그램 변경 없음
2. 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작업할 수 있음, 해당 SQL을 자주 사용 할때 뷰를 이용하면 편리하게 사용
3. 보안성 : 감추고 싶은 정보를 뷰를 생성할때 제외하고 생성 가능

예제) 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;

제거하기 : DROP VIEW V_PLAYER_TEAM;

        DROP VIEW V_PLAYER_TEAM_FILTER;