From 절의 서브쿼리 (Inline view), Top N 질의

FROM절의 서브쿼리는 무조건 독립적으로 실행되어야 한다. 

그냥 새로운 테이블을 임시로 만들어서 띄우는 거랑 비슷하다. 

그래서 WITH로도 대체할 수 있다. 

또, 보통의 서브쿼리에서는 order by 절은 사용하지 않으나, From절에 들어가는 서브쿼리에서는 사용이 가능하다. 

 

 

emp의 테이블에 부서별 급여합계를 추가해서 보고 싶어서 아래와 같은 쿼리를 작성했다. 

SELECT * 
  FROM emp e 
  JOIN (SELECT deptno, SUM(sal) AS SUM_SAL 
          FROM emp 
         GROUP BY deptno) s 
    ON e.deptno = s.deptno ;

 

이 문장은 아래 문장과도 똑같이 실행된다.

WITH으로 새로운 쿼리블록을 아예 띄워버리고 걔를 계속 참조해서 쓸 수 있다. 

WITH sum_emp AS (SELECT deptno, SUM(sal) AS SUM_SAL 
                   FROM emp 
                  GROUP BY deptno)
SELECT * 
  FROM emp e 
  JOIN sum_emp s 
    ON e.deptno = s.deptno ;

 

 

FROM 절에 들어가는 서브쿼리는 '상위 몇개까지'의 결과를 출력하라는 오더를 줄 때도 유용하다. 

 

이런 문장을 실행하면 급여가 높은 사람부터 3명을 출력할 것 같지만 그냥 데이터가 입력되어 있는 순서대로 세명을 뽑아온다. WHERE에서 먼저 3개까지 자른 다음에 ORDER BY에서 그 세개를 DESC로 정렬하는 것뿐이다. 

전체에서 가장 급여가 높은 3명을 뽑으려면, WHERE에서 3개를 제한하기 전에 FROM절에서 INLINE VIEW를 사용해서 급여 합계데로 테이블을 만들어두고 걔를 그대로 빼내오면 된다. 

 

rownum 말고도 반환할 행의 갯수를 지정하는 다른 방법들도 있다. 

 

FETCH를 사용해도 위와 같은 결과가 나온다. 

SELECT * 
  FROM EMP 
 ORDER BY SAL DESC 
FETCH FIRST 3 ROW ONLY ;

 

 

선생님이 LATERAL을 사용한 예제도 보여주셨는데 솔직히 잘 모르겠다! 

SELECT * 
  FROM DEPT D 
      ,LATERAL (SELECT * 
                  FROM EMP 
                WHERE DEPTNO = D.DEPTNO 
                ORDER BY SAL DESC 
                FETCH FIRST 1 ROW ONLY) ;


SELECT * 
  FROM DEPT D 
      ,LATERAL (SELECT * 
                  FROM EMP 
                WHERE DEPTNO = D.DEPTNO 
                ORDER BY SAL DESC 
                FETCH FIRST 1 ROW ONLY) (+) ;

 

눈치로 때려보니 부서별 급여 제일 높은 사람만 남으라는 것 같고 

 

근데 (+) 붙은거 실행하면 안붙은쪽 (DEPT)에 있는 결과들은 전부 반환한다. 

 

아래 얘네 둘도 위 둘과 각각 같은 결과를 반환한다. 

SELECT * 
  FROM DEPT D 
  CROSS APPLY (SELECT * 
                  FROM EMP 
                WHERE DEPTNO = D.DEPTNO 
                ORDER BY SAL DESC 
                FETCH FIRST 1 ROW ONLY) ;

SELECT * 
  FROM DEPT D 
  OUTER APPLY (SELECT * 
                  FROM EMP 
                WHERE DEPTNO = D.DEPTNO 
                ORDER BY SAL DESC 
                FETCH FIRST 1 ROW ONLY) ;