[ORACLE] 9일차 : 서브 쿼리

 

 

서브쿼리란?
subquery는 SELECT,INSERT,UPDATE,DELETE문이나 다른 하위 쿼리 내부에 중첩된 SELECT 쿼리
식이 허영되는 모든 위치에서 사용할 수 있으며 단독으로 실행이 가능하다.

 

subquery 

 

 

WITH 

서브쿼리를 블록화해서 사용하기

 

WITH tmp AS(
        SELECT TO_CHAR(sDate,'YYYY')년도,s.cNum,cName,SUM(bPrice*qty)금액,
        RANK()OVER(PARTITION BY TO_CHAR(sDate,'YYYY')ORDER BY SUM(bPrice*qty)DESC)순위
        FROM book b
        JOIN dsale d ON b.bCode = d.bCode
        JOIN sale s ON d.sNum = s.sNum
        JOIN cus c ON s.cNum = c.cNum
        GROUP BY TO_CHAR(sDate,'YYYY'),s.cNum,cName
        )
SELECT 년도,cNum,cName,금액 FROM tmp WHERE 순위 = 1;

 

 

단일행 서브쿼리

• 서브 쿼리 수행 결과 하나의 행과 하나의 값을 반환 받는다. 

SELECT empNO,name,sal,sal-AVG(sal)OVER()차이 
FROM emp;

SELECT empNO,name,sal,sal-(SELECT AVG(sal)FROM emp)차이 
FROM emp;
--서브 쿼리 사용

 

다중행 서브쿼리

• 서브 쿼리 수행 결과 두개 이상의 값을 반환 받는다. 

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

 

 

 

ANY(some)

비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 참인 연산자 

 

SELECT empNo,name,sal
FROM emp
WHERE sal>ANY(2000000,2500000,3000000);

 

 

EXISTS

비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참인 연산자 

SELECT * FROM book 
WHERE EXISTS(SELECT * FROM dsale WHERE qty >= 10);

 

 

UPDATE

UPDATE JOIN VIEW 이용하여 빠른 업데이트(서브쿼리 보다 훨씬 빠르다.) 테이블을 조인하여 UPDATE
 tb_a 테이블의 내용(new_addr1, new_addr2)을 tb_b에 존재하는 내용(n_addr1, n_addr2)으로 수정 조인 조건의 컬럼이 UNIQUE 속성이어야 가능하며(관계가 1:1) 그렇지 않으면 ORA-01779 오류가 발생한다.

 

        CREATE TABLE tb_a (
             num  NUMBER PRIMARY KEY
            ,addr1  VARCHAR2(255)
            ,addr2 VARCHAR2(255)
            ,new_addr1 VARCHAR2(255)
            ,new_addr2 VARCHAR2(255)
       ); --테이블 a생성
       

      CREATE TABLE tb_b (
           num  NUMBER PRIMARY KEY
          ,n_addr1 VARCHAR2(255)
          ,n_addr2 VARCHAR2(255)
      );--테이블 b생성

      INSERT INTO tb_a VALUES(1,'서울1-1', '서울1-2','도로1-1', '도로1-2');
      INSERT INTO tb_a VALUES(2,'서울2-1', '서울2-2','도로2-1', '도로2-2');
      INSERT INTO tb_a VALUES(3,'서울3-1', '서울3-2','도로3-1', '도로3-2');
      INSERT INTO tb_a VALUES(4,'서울4-1', '서울4-2','도로4-1', '도로4-2');
      INSERT INTO tb_a VALUES(5,'서울5-1', '서울5-2','도로5-1', '도로5-2');

      INSERT INTO tb_b VALUES(1,'세종1-1', '세종1-2');
      INSERT INTO tb_b VALUES(3,'세종3-1', '세종3-2');
      INSERT INTO tb_b VALUES(5,'세종5-1', '세종5-2');
      COMMIT;
      --데이터 삽입 

SELECT a.new_addr1,a.new_addr2,b.n_addr1,b.n_addr2
FROM tb_a a, tb_b b
WHERE a.num = b.num;

--UPDATE 테이블명 SET 컬럼명 = 값, 컬럼명 = 값 WHERE 조건;

UPDATE
(
    SELECT a.new_addr1,a.new_addr2,b.n_addr1,b.n_addr2
    FROM tb_a a, tb_b b
    WHERE a.num = b.num
)
SET new_addr1 = n_addr1,new_addr2 = n_addr2;