서브쿼리

서현서현·2022년 2월 27일
0

DB, SQL

목록 보기
16/27
post-thumbnail

💐 SUBQUERY

  • SQL 구문안에 기술된 또 다른 SQL문
  • 서브쿼리는 ‘()’안에 기술해야함(단, INSERT INTO문에 사용되는 SUBQUERY는 예외)
  • 연산자와 사용될경우 연산자 오른쪽에 기술해야함
  • 알려지지 않은 조건에 근거한 검색명령이나 JOIN의 감소 등의 목적으로 사용
  • 서브쿼리를 포함하고 있는 쿼리를 메인쿼리라 하고 메인쿼리수행전에 서브쿼리로부터 한번 수행됨
  • 서브쿼리의 분류
    • 연관성없는 서브쿼리 VS 연관성 있는 서브쿼리
    • 일반 서브쿼리(SELECT절에 옴), inline view subquery(FROM절에 옴), nested subquery(WHERE절에 옴)
    • 단일행 서브쿼리, 다중행 서브쿼리

(EX1) 사원테이블에서 사원들의 평균급여보다 더 많은 급여를 받는 사원정보 조회

Alias는 사원번호, 사원명, 부서번호, 급여

(메인쿼리 : 사원정보 조회)
SELECT EMPLOYEE_ID AS 사원번호, 
				EMP_NAME AS 사원명, 
				DEPARTMENT_ID AS 부서번호, 
				SALARY AS급여
FROM HR.EMPLOYEES
WHERE SALARY > (평균급여)
ORDER BY 3;
(서브쿼리:평균급여)
SELECT AVG(SALARY)
FROM HR.EMPLOYEES
(결합)
SELECT EMPLOYEE_ID AS 사원번호, 
				EMP_NAME AS 사원명, 
				DEPARTMENT_ID AS 부서번호, 
				SALARY AS급여
FROM HR.EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
                    FROM HR.EMPLOYEES)
ORDER BY 4 DESC;
(inline-view subquery)
SELECT A.EMPLOYEE_ID AS 사원번호, 
        A.EMP_NAME AS 사원명, 
        A.DEPARTMENT_ID AS 부서번호, 
        A.SALARY AS 급여
FROM HR.EMPLOYEES A, (SELECT AVG(SALARY) AS ASAL
                        FROM HR.EMPLOYEES) B
WHERE A.SALARY > B.ASAL --실행조건
ORDER BY 4 DESC;

ㅇinline-view 서브쿼리는 독립 실행 되어져야 한다. (서브쿼리만 선택해서 실행해도 실행되어져야)

SELECT A.EMPLOYEE_ID AS 사원번호, 
        A.EMP_NAME AS 사원명, 
        A.DEPARTMENT_ID AS 부서번호, 
        A.SALARY AS 급여,
        --평균급여를 구하고 싶다면? (51번 실행됨)
        (SELECT ROUND(AVG(SALARY))
            FROM HR.EMPLOYEES) AS 평균급여
FROM HR.EMPLOYEES A, (SELECT AVG(SALARY) AS ASAL
                        FROM HR.EMPLOYEES) B
WHERE A.SALARY > B.ASAL --실행조건
ORDER BY 4 DESC;

(EX2) 회원테이블에서 여성회원의 평균마일리지보다 더 많은 마일리지를 보유한 회원의 회원번호,회원명,마일리지를 조회하시오

(메인쿼리 : 회원의 회원번호, 회원명, 마일리지를 조회)

(서브쿼리 : 여성회원의 평균마일리지)

SELECT MEM_ID AS 회원번호, 
        MEM_NAME AS 회원명, 
        MEM_MILEAGE AS 마일리지
   FROM MEMBER
  WHERE MEM_MILEAGE>(
            --서브쿼리 : 여성회원의 평균마일리지
              SELECT AVG(MEM_MILEAGE)
                FROM MEMBER
               WHERE SUBSTR(MEM_REGNO2,1,1) IN('2','4'));

(EX3) 사원테이블에서 부서별 평균급여보다 많은 급여를 받는 사원이 있는 부서(부서번호,부서명)를 조회하시오

(메인쿼리 : 부서의 부서번호,부서명을 조회)

SELECT A.DEPARTMENT_ID AS 부서번호,
        A.DEPARTMENT_NAME AS 부서명
FROM HR.DEPARTMENTS A
-- WHERE EXISTS 서브쿼리 --1
WHERE A.DEPARTMENT_ID =(서브쿼리) --2
-- (서브쿼리 : 부서별 평균급여보다 많은 급여를 받는 사원의 부서)
-- 서브쿼리에 포함된 메인쿼리 : 사원의 부서번호
-- 서브쿼리 : 부서별 평균급여
SELECT B.DEPARTMENT_ID
FROM HR.EMPLOYEES B
WHERE B.SALARY>(서브쿼리)
(부서별 평균급여)
SELECT AVG(SALARY)
FROM HR.EMPLOYEES C
WHERE CDEPARTMENT_ID = B.DEPARTMENT_ID
(결합)
SELECT DISTINCT B.DEPARTMENT_ID
FROM HR.EMPLOYEES B
WHERE B.SALARY>(SELECT AVG(SALARY)
                FROM HR.EMPLOYEES C
                WHERE C.DEPARTMENT_ID = B.DEPARTMENT_ID)
            ORDER BY 1;

(결합)
SELECT A.DEPARTMENT_ID AS 부서번호,
        A.DEPARTMENT_NAME AS 부서명
FROM HR.DEPARTMENTS A
WHERE A.DEPARTMENT_ID IN(SELECT B.DEPARTMENT_ID
                            FROM HR.EMPLOYEES B
                            WHERE B.SALARY>(SELECT AVG(SALARY)
                                             FROM HR.EMPLOYEES C
                                             WHERE C.DEPARTMENT_ID = B.DEPARTMENT_ID));
                                             -- JOIN으로 연결된 서브쿼리. 연관된 서브쿼리, 다중행서브쿼리
 SELECT DEPARTMENT_ID, 
        COUNT(*)
   FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;
                                             
                                             
                                             
(EXISTS 연산자 사용)                                                                                    
SELECT A.DEPARTMENT_ID AS 부서번호,
        A.DEPARTMENT_NAME AS 부서명
FROM HR.DEPARTMENTS A
WHERE EXISTS(SELECT 1
               FROM HR.EMPLOYEES B
              WHERE B.SALARY>(SELECT AVG(SALARY)
                                 FROM HR.EMPLOYEES C
                                WHERE C.DEPARTMENT_ID = B.DEPARTMENT_ID)
                AND A.DEPARTMENT_ID = B.DEPARTMENT_ID);

(EX4) 장바구니 테이블에서 2005년 회원별 최고 구매수량을 기록한 회원정보를 조회하시오.

Alias는 회원번호, 회원명, 구매수량

(메인쿼리: 회원정보 조회)

		SELECT 회원번호,회원명,구매수량
      FROM MEMBER A,(서브쿼리) B
     WHERE A.MEM_ID = B.회원번호

(서브쿼리: 2005년 회원별 최고 구매수량)

		SELECT MAX(SUM(CART_QTY))
    FROM CART
    WHERE CART_NO LIKE '2005%'
--흠 근데 회원번호랑 맞추질 못해... 일단 해보자
		SELECT CART_MEMBER,
            MAX(SUM(CART_QTY))
    FROM CART
    WHERE CART_NO LIKE '2005%'

-- 에러. 집계함수는 집계함수를 포함하지 못함

    SELECT CART_MEMBER,
            SUM(CART_QTY)
    FROM CART
    WHERE CART_NO LIKE '2005%'
    GROUP BY CART_MEMBER
    ORDER BY 2 DESC;

-- 이상태의 테이블에서 첫줄이 젤 많이 구매한 데이터니까 첫줄만 필요

    SELECT CART_MEMBER,
            SUM(CART_QTY)
    FROM CART
    WHERE CART_NO LIKE '2005%'
    AND ROWNUM = 1
    GROUP BY CART_MEMBER
    ORDER BY 2 DESC;

    --ROWNUM부터 실행돼버림. 그래서 첫줄뽑아서 집계해버리니 의미가 없다..
    -- 또 쿼리가 필요한것! ROWNUM을 바깥으로 뺴낼거야
    
    SELECT C.CART_MEMBER A CMID,
            C.CSUM AS CCSUM
    FROM (SELECT CART_MEMBER,
                    SUM(CART_QTY) AS CSUM
            FROM CART
            WHERE CART_NO LIKE '2005%'
            GROUP BY CART_MEMBER
            ORDER BY 2 DESC) C
    WHERE ROWNUM = 1;
(결합)
    SELECT A.MEM_ID AS 회원번호,
            A.MEM_NAME AS 회원명,
            B.CCSUM AS 구매수량
    FROM MEMBER A,(SELECT C.CART_MEMBER AS CMID,
                            C.CSUM AS CCSUM
                    FROM (SELECT CART_MEMBER,
                                  SUM(CART_QTY) AS CSUM
                            FROM CART
                            WHERE CART_NO LIKE '2005%'
                            GROUP BY CART_MEMBER
                            ORDER BY 2 DESC) C
                    WHERE ROWNUM = 1) B
    WHERE A.MEM_ID = B.CMID;

(재고수불테이블을 생성하시오) → REMAIN 생성하고 시작!

🐣 INSERT문과 SUBQUERY

  • 특정테이블에 삽입할 자료를 서브쿼리를 통하여 취득
  • INSERT문에 사용되는 서브쿼리는 '( )'를 사용하지 않음
  • 서브쿼리를 사용하는 INSERT문에서느 VALUES절을 사용하지 않음
(사용형식)
INSERT INTO 테이블명[(컬럼명[,컬럼명,...])]
서브쿼리;

(EX1) 재고수불테이블(REMAIN)에 다음 자료를 입력하시오.

  • 입력할자료 : 년도와 제품코드 => PROD 테이블의 모든 제품코드를 재고수불 테이블에 저장
INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID)
SELECT '2005',PROD_ID FROM PROD;
COMMIT;
SELECT * FROM REMAIN;

🐣 UPDATE문과 SUBQUERY

(사용형식)
UPDATE 테이블명 [별칭]
    SET [(컬럼명1[,컬러명2,...])]=(서브쿼리)
[WHERE 조건];
  • SET절에 하나 이상의 컬럼명을 기술할때는 컬럼명들을 '( )'로 묶어주어야 함
  • SET절에 하나이상의 컬럼명을 기술하면 = 다음의 '서브쿼리' SELECT절은 반드시 '(컬럼명1[,컬러명2,...])'과 같은 개수,순서로 컬럼이 기술되어야함
  • ‘WHERE 조건’이 생략되면 ‘테이블’의 모든 행이 UPDATE됨(주의)
SET 컬럼명1=(서브쿼리1),
		컬럼명2=(서브쿼리2),
					.
					.
		컬럼명n=(서브쿼리n)

(EX1) 상품테이블에서 각 상품의 적정재고(PROD_PROPERSTOCK)를 재고수불테이블의 기초재고(REMAIN_J_00)로 자료를 갱신하시오

UPDATE REMAIN A
        SET (REMAIN_J_00,REMAIN_J_99,REMAIN_DATE)=
             (SELECT B.PROD_PROPERSTOCK,B.PROD_PROPERSTOCK,TO_DATE('20050101')
                FROM PROD B
                WHERE B.PROD_ID = A.PROD_ID );
        -- REMAIN테이블의 모든 자료가 업데이트 되어져야 하니 WHERE절 생략 
        
        SELECT* FROM REMAIN;
				COMMIT;

(EX2) 2005년 1월 상품별 매입수량을 조사하여 재고수불테이블을 갱신하시오.

(메인쿼리 : 재고수불테이블을 갱신)
        
        UPDATE REMAIN A
           SET (A.REMAIN_I,A.REMAIN_J_99,A.REMAIN_DATE)=
               (서브쿼리)
         WHERE 조건;
(서브쿼리: 2005년 1월 상품별 매입수량을 조사)

         SELECT A.REMAIN_I+B.BSUM,A.REMAIN_J_99+B.BSUM,TO_DATE('20050131')
           FROM (SELECT BUY_PROD AS BID,
                        SUM(BUY_QTY)AS BSUM
                  FROM BUYPROD
                 WHERE BUYDATE BETWEEN TO DATE('20050101')AND TO DATE('20050121')
                 GROUP BY BUY_PROD) B
           WHERE A.PROD_ID = B.BID;
(결합)
				UPDATE REMAIN A
           SET (A.REMAIN_I,A.REMAIN_J_99,A.REMAIN_DATE)=
                (SELECT A.REMAIN_I+B.BSUM,A.REMAIN_J_99+B.BSUM,TO_DATE('20050131')
                  FROM (SELECT BUY_PROD AS BID,
                        SUM(BUY_QTY)AS BSUM
                  FROM BUYPROD
                 WHERE BUY_DATE BETWEEN TO_DATE('20050101')AND TO_DATE('20050131')
                 GROUP BY BUY_PROD) B
                 WHERE A.PROD_ID = B.BID);

1월달의 제품별 매입수량이 계산되었어. REMAIN테이블에있는 제품코드에서 B.BID와일치시켜, 그래서 찾으면
서브쿼리안에있는 자료를 찾아 B.BID는 30200001이 첫번째 코드고 BUM도 구해짐
업뎃하고 데이터 테이블을 보면 NULL값이 엉망으로 들어가는데 NULL이 연산으로 들어가면 그건 볼것도 없이 NULL이 됨...
35개 자료는 NULL이 연산으로 들어가므로 테이블에 엉망진창 NULL이 나온것

WHERE절이 없어서 엉망진창 계산이 만들어진건데
업데이트 해야할 조건, 업데이트 하지 말아야 할 조건을 WHERE절로 가려줘야됨

ROLLBACK;

UPDATE REMAIN A
     SET (A.REMAIN_I, A.REMAIN_J_99, A.REMAIN_DATE) =  --3가지 컬럼명 같이 움직임
         (SELECT A.REMAIN_I+B.BSUM, A.REMAIN_J_99+B.BSUM, TO_DATE('20050131')
            FROM (SELECT BUY_PROD AS BID,
                         SUM(BUY_QTY) AS BSUM
                    FROM BUYPROD
                   WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND 
                         TO_DATE('20050131')
                   GROUP BY BUY_PROD) B
           WHERE A.PROD_ID = B.BID) --괄호 안 WHERE절: 상품을 찾는 조건
                                    --괄호 밖 WHERE절: 가려내는 조건             
   WHERE A.PROD_ID IN(SELECT BUY_PROD
                        FROM BUYPROD
                       WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050131'));

입고가 발생된 39개만 REMAIN_DATE가 31일로 바뀌고, 나머지는 5/1 그대로이다

(EX3) 사원테이블에서 근무지가 미국(’United Status of America’)이 아닌 사원중 근무지가 미국인 가원의 평균급여보다 급여가 많은 사원을 조회하시오

Alias는 사원번호, 사원명, 부서명, 직책명, 급여

SELECT TA.EMPLOYEE_ID AS 사원번호,
         TA.EMP_NAME AS 사원명,
         TB.DEPARTMENT_NAME AS 부서명,
         TC.JOB_TITLE AS 직책명,
         TA.SALARY AS 급여
    FROM HR.EMPLOYEES TA, HR.DEPARTMENTS TB, HR.JOBS TC,
         (SELECT AVG(SALARY) AS ASAL
            FROM HR.LOCATIONS D,HR.COUNTRIES E,HR.EMPLOYEES A, HR.DEPARTMENTS B
           WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
             AND B.LOCATION_ID=D.LOCATION_ID
             AND D.COUNTRY_ID=E.COUNTRY_ID
             AND E.COUNTRY_NAME='United States of America') TD
   WHERE TA.DEPARTMENT_ID=TB.DEPARTMENT_ID
     AND TA.JOB_ID=TC.JOB_ID
     AND TA.SALARY>TD.ASAL
     AND TA.DEPARTMENT_ID NOT IN
         (SELECT A.DEPARTMENT_ID AS DID
            FROM HR.LOCATIONS D,HR.COUNTRIES E,HR.EMPLOYEES A, HR.DEPARTMENTS B
           WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
             AND B.LOCATION_ID=D.LOCATION_ID
             AND D.COUNTRY_ID=E.COUNTRY_ID
             AND E.COUNTRY_NAME='United States of America');

(EX4) 2005년 모든 상품별 매입/매출집계를 조회하시오 (SUBQUERY 사용)

Alias는 상품코드, 상품명, 매입금액합계, 매출금액합계

(메인쿼리 : 상품코드, 상품명, 매입금액합계, 매출금액합계 출력)
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
B.매입금액합계 AS 매입금액합계,
C. 매출금액합계 AS 매출금액합계
FROM PROD A,
(서브쿼리 : 2005년도 매입금액집계) B,
(서브쿼리 : 2005년도 매출금액집계) C
WHERE A.PROD =B.상품코드(+)
AND A.PROD_ID = C.상품코드(+)
ORDER BY 1;
(서브쿼리 : 2005년도 매입금액집계, 2005년도 매출금액집계)

--2005년도 매입금액집계
 SELECT BUY_PROD,
        SUM(BUY_QTY*BUY_COST)
   FROM BUYPROD
  WHERE EXTRACT(YEAR FROM BUY_DATE)=2005
  GROUP BY BUY_PROD;

--2005년 상품별 매출금액집계
 SELECT TA.CART_PROD,
        SUM(TA.CART_QTY*TB.PROD_PRICE)
   FROM CART TA, PROD TB
  WHERE TA.CART_PROD = TB.PROD_ID
    AND TA.CART_NO LIKE '2005%'
GROUP BY TA.CART_PROD;
- 결합
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
B.BSUM AS 매입금액합계,
C.CSUM AS 매출금액합계
FROM PROD A,
(SELECT BUY_PROD AS BID,
SUM(BUY_QTY*BUY_COST) AS BSUM
FROM BUYPROD
WHERE EXTRACT(YEAR FROM BUY_DATE)=2005
GROUP BY BUY_PROD) B,
(SELECT TA.CART_PROD AS CID,
SUM(TA.CART_QTY*TB.PROD_PRICE) AS CSUM
FROM CART TA, PROD TB
WHERE TA.CART_PROD = TB.PROD_ID
AND TA.CART_NO LIKE '2005%'
GROUP BY TA.CART_PROD) C
WHERE A.PROD_ID = B.BID(+)
AND A.PROD_ID = C.CID(+)
ORDER BY 1;
profile
서현서현
이전 포스트

테이블 JOIN

다음 포스트

집합연산자

0개의 댓글