[Oracle] 서브쿼리 - 다중행/다중열/상호연관 서브쿼리 및 기타 서브쿼리

# 다중행/다중열 서브쿼리 

다중행 서브쿼리 

- SELECT 문장으로부터 하나 이상의 행을 검색하는 질의  

IN 

SELECT empno, ename, deptno, sal 
FROM   emp 
WHERE  sal IN (SELECT sal 
              FROM   emp 
              WHERE  job = 'MANAGER' );

  

ANY

- 서브쿼리에서 반환한 각각 값과 비교해 하나라도 참이면 참 (OR로 동작)

• < ANY : 최대값보다 적음
• > ANY : 최소값보다 큼
• = ANY : IN과 동일 

SELECT empno, ename, deptno, sal 
FROM   emp 
WHERE  sal = ANY (SELECT sal -- IN과 동일한 결과 
                  FROM   emp 
                  WHERE  job = 'MANAGER' );

  

ALL

- 서브쿼리에서 반환한 각각 값과 비교해 모두 참이어야 참 (AND로 동작),

• < ALL : 최대값보다 적음
• > ALL : 최소값보다 큼    

SELECT empno, ename, deptno, sal 
FROM   emp 
WHERE  sal = ALL (SELECT sal -- IN과 동일한 결과 
                  FROM   emp 
                  WHERE  job = 'MANAGER' );

    

다중열 서브쿼리 

- SELECT 문장으로부터 하나 이상의 열을 검색하는 질의  

-- FORD, BLAKE와 관리자 및 부서가 같은 사원의 정보 조회 
SELECT ename, mgr, deptno 
FROM   emp 
WHERE  (mgr, deptno) IN (SELECT mgr, deptno 
                         FROM emp 
                         WHERE ename IN ('FORD', 'BLAKE')) 
AND    ename NOT IN('FORD', 'BLAKE');

    

# 상호연관 서브쿼리 

서브쿼리의 분류 

비상관서브쿼리 

• 주로 IN과 함께 쓰임 
• 메인쿼리 상관없이 서브쿼리 혼자 독자적으로 실행 가능한 서브쿼리, 대부분 여기 해당 
• 실행순서 일반적으로 ①서브쿼리가 먼저 실행되고 ②그 결과를 사용해 메인쿼리에 대해 질의 완성 
• 서브쿼리는 메인쿼리 컬럼을 사용할 수 있으나 메인쿼리는 서브쿼리 컬럼 사용 불가 
• WHERE절에 서브쿼리가 쓰이는 경우 한 번 실행되어 명시적인 값으로 쓰임  

상관서브쿼리  

• 주로 EXIST와 함께 쓰임 
• 상위 질의에 있는 테이블의 컬럼을 참조함 
• 실행순서는 ①메인 쿼리의 행을 먼저 읽고 ②각 행 값을 관련된 데이터와 비교함 
• 서브쿼리는 메인쿼리 컬럼을 사용할 수 있으나 메인쿼리는 서브쿼리 컬럼 사용 불가  
• SELECT절에 쓰인 서브쿼리처럼 매행 서브쿼리가 실행  

 

EXISTS 연산자 

- 서브쿼리 결과 집합에 존재하는지 여부를 검사하기위한 상호연관 쿼리에 자주 사용  

- IN과 호환  

-- 비상관형태, IN 사용 
SELECT * 
FROM   dept 
WHERE  deptno IN (SELECT deptno -- IN : 조건 or 조건 or 조건 or ... 
                  FROM   emp); 
                   
SELECT * 
FROM   dept 
WHERE  deptno NOT IN (SELECT deptno -- NOT IN : !조건 and !조건 and !조건 ... 
                      FROM   emp); 

  
-- 상관형태, EXISTS 사용        
SELECT * 
FROM   dept 
WHERE  EXISTS (SELECT 1 
               FROM   emp 
               WHERE  emp.deptno = dept.deptno); 
         
SELECT * 
FROM   dept 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   emp 
                   WHERE  emp.deptno = dept.deptno);

  

상호연관 서브쿼리 예시 

-- 사원이 속한 부서의 평균 급여를 계산해서 그 평균 급여보다 크면 리턴   
-- EXISTS를 사용하지 않았으나 상관 서브쿼리로 쓰임 
SELECT empno, ename, deptno, sal 
FROM   emp main 
WHERE  sal > (SELECT AVG(sub.sal) 
              FROM   emp sub 
              -- 매 행마다 계산되므로 SELECT절에 쓰인 서브쿼리의 사례와 유사하게 성능 저하 
              WHERE  sub.deptno = main.deptno);  

-- 되도록이면 JOIN 형태로 사용할 수 있는 서브쿼리는 JOIN의 형태로 쓸 때 성능 효율적 
SELECT empno, ename, e1.deptno, sal 
FROM   emp e1 JOIN (SELECT deptno, AVG(sal) avg_sal 
                    FROM   emp 
                    GROUP BY deptno) e2 
ON     e1.deptno = e2.deptno 
AND    e1.sal    > e2.avg_sal;

 

# TOP-N 서브쿼리

- 상위, 하위 몇 개의 데이터의 값을 출력 

SELECT *
FROM (SELECT job, ROUND(AVG(sal))
      FROM   emp
      GROUP BY job
      ORDER BY ROUND(AVG(sal)) DESC)
WHERE ROWNUM <= 3;

 

# 스칼라 서브쿼리

- 하나의 행에서 하나의 컬럼만 반환하는 서브쿼리