서브쿼리 Subquery

WITH 서브 쿼리

    WITH tmp AS (
         SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, 
                   s.cNum, cName, qty, bPrice * qty amt
         FROM book b
         JOIN  pub p  ON  b.pNum = p.pNum
         JOIN  dsale d  ON  b.bCode = d.bCode
         JOIN  sale s  ON  d.sNum = s.sNum
         JOIN  cus c  ON  s.cNum = c.cNum
    ) 
    SELECT bCode, bName, SUM(amt)
    FROM tmp
    GROUP BY bCode, bName;

 

단일 행 서브쿼리

     SELECT empNo, sal FROM emp
     WHERE sal < (SELECT AVG(sal) FROM emp);

다중 행 서브쿼리

       SELECT bCode, bName
       FROM book
       WHERE bCode IN ( SELECT DISTINCT bCode FROM dsale );

     SELECT empNo, name, sal
     FROM emp
     WHERE sal > ALL ( 2000000, 3000000, 4000000 );

ALL은 모두 만족 시켜야 한다.

 

       SELECT bName FROM book
       WHERE EXISTS ( SELECT * FROM dsale WHERE qty >= 10 );
          -- 다음과 동일 : qty >= 10 인 레코드가 존재하므로
       SELECT bName FROM book;

 

상호 연관 서브쿼리

(성능은 안좋음)

     SELECT name, sal,
        ( SELECT COUNT( e2.sal ) + 1  FROM  emp  e2
               WHERE  e2.sal > e1.sal ) 순위
      FROM emp e1;