서브쿼리

서브쿼리

• 쿼리 안에 사용되는 쿼리
• 사용 목적
  • 상수를 대체하기 위해 서브쿼리 사용
  • 조인의 대체 연산
  • 테이블을 한 번 SCAN하는 것으로 표현이 불가능한 정보 출력
  • 앞뒤로 괄호로 묶어주어야 함
    
    
• 서브쿼리의 형태(사용 절에 따라)
  • 스칼라 서브쿼리
    • SELECT 절에 사용
    • 컬럼의 대체 표현식
    • SELECT COLUMN1, (SELECT ...)
  • 인라인 뷰
    • FROM 절에 사용
    • 테이블의 대체 표현식(조인 필요)
    • FROM TABLE1, (SELECT ...)
  • 일반 서브쿼리
    • WHERE 절에 사용
    • 상수의 대체 표현식
    • WHERE COLUMN1 = (SELECT ...)
      
      
• 서브쿼리의 형태(서브쿼리 결과에 따라)
  • 단일행 서브쿼리
    • 서브쿼리 결과가 단 하나의 행을 갖는 경우
    • =,>,< 연산자 사용 가능
  • 다중행 서브쿼리
    • 서브쿼리 결과가 두 개 이상의 행을 갖는 경우
    • =,>,< 연산자 사용 불가
    • IN, ANY, ALL 연산자 사용 가능
    • ALL 연산자
      • 나열된 조건을 모두 만족하는 연산자
      • 크다, 작다 둘 중 어느 조건과 결합하는지에 따라 서로 다른 의미
      • "> ALL(100,200)" : 100, 200 중 최댓값(200)보다 큰 값들
    • ANY 연산자
      • 나열된 조건 중 하나만 만족해도 되는 연산자
      • 크다, 작다 둘 중 어느 조건과 결합하는지에 따라 서로 다른 의미
      • "> ANY(100,200)" : 100, 200 중 최솟값(100)보다 큰 값들
  • 다중 컬럼 서브쿼리
    • 서브쿼리 결과가 두 개 이상의 컬럼을 갖는 경우
    • 단일행, 다중행 모두 가능
    • 비교하고자 하는 컬럼을(COL1,COL2)형태로 묶어서 전달
    • 대소비교 불가 -> 대소비교가 가능한 문법(인라인 뷰, 상호연관 서브쿼리)으로 대체
    • GROUP BY 연산 결과를 조건으로 사용할 경우 사용
    • 사용 예시

    SELECT NAME, DEPTNO, SAL
    FROM EMP2
    WHERE (DEPTNO, SAL) IN 
    (SELECT DEPTNO, MAX(SAL)
    FROM EMP2
    GROUP BY DEPTNO);

상호연관 서브쿼리

• 메인쿼리와 서브쿼리의 비교 조건을 서브쿼리에 사용하는 표현식

• 다중 컬럼 서브쿼리에서 사용할 수 없었던 대소비교를 조건설로 전달 가능하게 된다.

• 사용예시

  • Professor 테이블에서 각 직급별로 평균 연봉보다 높은 연봉을 받는 교수의 이름, 직급, 연봉 출력

   SELECT P1.NAME, P1.POSITION, P1.PAY
   FROM PROFESSOR P1
   WHERE PAY > (SELECT POSITION, AVG(PAY)
                FROM PROFESSOR P2
                WHERE P1.POSITION = P2.POSITION
                GROUP BY POSITION);

스칼라 서브쿼리

• SELECT 절에 사용되는 서브쿼리
• 단 한 개의 컬럼만 리턴 가능
• 각 행마다 매칭되는 서브쿼리의 결과는 단 하나의 행
• OUTER JOIN 수행 가능
• 사용예시
  • 인턴사원의 이름, SAL을 출력하되 SAL은 전체 직원의 평균 SAL로 출력

  SELECT NAME, (SELECT AVG(SAL) FROM EMP2)
  FROM EMP2
  WHERE POSITION = 'INTERN';

  • 각 직원의 이름과 부서명을 출력

  SELECT NAME, (SELECT DNAME
                FROM DEPT D
                WHERE E.DEPTNO = D.DEPTNO)
  FROM EMP2 E;  

  • 학생 테이블에서 각 학생의 이름과 지도교수 이름을 출력하되 스칼라 서브쿼리로 작성

  SELECT NAME, (SELECT NAME
                FROM PROFESSOR P
                WHERE S.PROFNO = P.PROFNO)
  FROM STUDENT S;