Oracle(2022.02.22)-외부조인

외부조인
 -조인에 참여하는 테이블 중 자료의 종류가 많은 쪽을 기준으로 자료의 종류가
  부족한 테이블에 많은 쪽의 행만큼 NULL값의 열을 갖는 행을 추가하여 조인 수행
 -조인조건 기술시 부족한 행을 갖는 테이블쪽에 외부조인 연산자 '(+)' 를 추가
 -조인조건이 여러 개있는 경우 외부조인을 수행해야할 모든 조건에 '(+)'를 기술해야함
 -동시에 한테이블이 두개 이상 확장될 수 없다. 즉, A,B,C 테이블에 대해 외부조인을
  수행할 경우 A를 기준으로 B를 확장하여 조인을 수행하고, 동시에 C를 기준으로 
  B를 확장하여 조인을 수행할 수 없음(ex.A=B(+) AND C=B(+)는 불가능)
 -일반조건과 외부조인 조건이 동시에 적용되면 내부조인으로 변환됨=>ANSI 외부조인이나
  서브쿼리를 사용해야함
  

<일반 외부조인 사용형식>

   SELECT 컬럼list
     FROM 테이블 list
    WHERE 컬럼명(+)=컬렴명
     [AND 컬럼명=컬럼명(+)]

<ANSI 외부조인형식>

 SELECT 컬럼list
      FROM 테이블명1
     LEFT|RIGHT|FULL OUTER JOIN 테이블명2 ON(조인조건1 [AND 일반조건1]
    [LEFT|RIGHT|FULL OUTER JOIN 테이블명3 ON(조인조건2 [AND 일반조건2]]
                        :
    [WHERE 일반조건];

    .'LEFT' :'테이블명1'의 자료의 종류가 '테이블명2'의 자료 종류보다 많을 때
    .'RIGHT':'테이블명1'의 자료의 종류가 '테이블명2'의 지료 종류보다 적을 때
    .'FULL' :'테이블명1'의 자료의 종류가 '테이블명2'의 자료 종류, 양쪽 모두 부족할 때 --FULL:양쪽모두 부족할경우(일         반 외부조인은 지원X,ANSI O)
    .ON절 (조인조건절)에 사용되는 '일반조건1'은 해당 조인에 관련된 테이블에 국한된 조건
     이어야 함
    .WHERE 조건절이 사용되면 외부조인을 수행한 결과에 조건을 적용한 결과가 반환됨

 

사용예)상품테이블에서 모든(ex전부..외부조인이구나) 분류별 상품의 수를 조회하시오.
    (PROD에 사용된 분류의 수) 분류를 기준으로 했을때는 LPROD>PROD, 행의개수기준 LPROD<PROD
    SELECT DISTINCT PROD_LGU
       FROM PROD;
    
    SELECT A.LPROD_GU AS 분류코드,
           COUNT(*) AS "상품의 수"
        FROM LPROD A, PROD B
       WHERE A.LPROD_GU=B.PROD_LGU(+)
       GROUP BY A.LPROD_GU
       ORDER BY 1;

상품테이블 분류코드

 

  ----->NULL부분은 0으로  COUNT 함수가 외부조인으로 사용될 경우 *를 쓰지않고 정확하게 기입
       SELECT A.LPROD_GU AS 분류코드,
           COUNT(B.PROD_ID) AS "상품의 수"
        FROM LPROD A, PROD B
       WHERE A.LPROD_GU=B.PROD_LGU(+)
       GROUP BY A.LPROD_GU
       ORDER BY 1;

외부조인한 결과

(ANSI FORMAT)     
     SELECT A.LPROD_GU AS 분류코드,
           COUNT(B.PROD_ID) AS "상품의 수"
        FROM LPROD A
        LEFT OUTER JOIN PROD B ON(A.LPROD_GU=B.PROD_LGU)
        GROUP BY A.LPROD_GU
        ORDER BY 1;

사용예) 2005년 2월 모든(외부조인) 제품별(GROUP BY) 매입현황을 조회하시오.
        Alias 상품코드, 상품명, 매입수량, 매입금액
        SELECT B.PROD_ID AS 상품코드,
               B.PROD_NAME AS 상품명,
               SUM(A.BUY_QTY) AS 매입수량, 
               SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
          FROM BUYPROD A,PROD B
          WHERE A.BUY_PROD(+)=B.PROD_ID
            AND A.BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY(TO_DATE('20050201')) -->일반조건과 외부조인 조건이 같이 사용되면 내부조인이 된다. 외부조인결과를 얻을 수 없음.
           GROUP BY B.PROD_ID,B.PROD_NAME
           ORDER BY 1;

->74개가 나와야하는데 29개만 나옴

 SELECT B.PROD_ID AS 상품코드,
               B.PROD_NAME AS 상품명,
               SUM(A.BUY_QTY) AS 매입수량, 
               SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
          FROM BUYPROD A,PROD B
          WHERE A.BUY_PROD(+)=B.PROD_ID
           -- AND A.BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY(TO_DATE('20050201')) 
           GROUP BY B.PROD_ID,B.PROD_NAME
           ORDER BY 1;

AND를 지우고 하면 74개가 나옴 즉 일반조건과 외부조인조건이 같이 쓰일 수없으므로 ANSI사용

해결방법

(ANSI FORMAT)
사용예) 2005년 2월 모든(외부조인) 제품별(GROUP BY) 매입현황을 조회하시오.
        Alias 상품코드, 상품명, 매입수량, 매입금액
    SELECT B.PROD_ID AS 상품코드,
               B.PROD_NAME AS 상품명,
               NVL(SUM(A.BUY_QTY),0) AS 매입수량, 
               NVL(SUM(A.BUY_QTY*B.PROD_COST),0) AS 매입금액
          FROM BUYPROD A
         RIGHT OUTER JOIN PROD B ON(A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050201')
               AND LAST_DAY(TO_DATE('20050201')))
         GROUP BY  B.PROD_ID,B.PROD_NAME
         ORDER BY 1;

위에 데이터 생략

BUY_PROD는 상품코드 중복이라 개수가 더 많은거고 PROD_ID가 더 많은 것임 그래서 RIGHT

ANSI여도 WHERE절을 쓰면 안된다.

 (ANSI FORMAT-WHERE절을 쓰면 내부조인이 돼버려서 그래서 사용안됨 )   
      SELECT B.PROD_ID AS 상품코드,
               B.PROD_NAME AS 상품명,
               NVL(SUM(A.BUY_QTY),0) AS 매입수량, 
               NVL(SUM(A.BUY_QTY*B.PROD_COST),0) AS 매입금액
          FROM BUYPROD A
         RIGHT OUTER JOIN PROD B ON(A.BUY_PROD=B.PROD_ID)
         WHERE A.BUY_DATE BETWEEN TO_DATE('20050201')AND LAST_DAY(TO_DATE('20050201'))
         GROUP BY  B.PROD_ID,B.PROD_NAME
         ORDER BY 1;

위에 데이터 생략

또 29개가 나옴..

이번엔 서브쿼리 사용문

(서브쿼리 사용)
사용예) 2005년 2월 모든(외부조인) 제품별(GROUP BY) 매입현황을 조회하시오.
        Alias 상품코드, 상품명, 매입수량, 매입금액
   SELECT D.PROD_ID AS 상품코드,
          D.PROD_NAME AS 상품명,
          NVL(C.QSUM,0) AS 매입수량,
          NVL(C.ASUM,0) AS 매입금액
     FROM( SELECT B.PROD_ID AS BID,
                  SUM(A.BUY_QTY) AS QSUM,
                  SUM(A.BUY_QTY*B.PROD_COST) AS ASUM
                FROM BUYPROD A, PROD B
               WHERE A.BUY_PROD=B.PROD_ID
                 AND A.BUY_DATE BETWEEN TO_DATE('20050201')AND LAST_DAY(TO_DATE('20050201'))
                GROUP BY B.PROD_ID,B.PROD_NAME)C,
               PROD D
     WHERE C.BID(+)=D.PROD_ID
     ORDER BY 1;

서브쿼리만 결과
SELECT B.PROD_ID AS BID,
                  SUM(A.BUY_QTY) AS QSUM,
                  SUM(A.BUY_QTY*B.PROD_COST) AS ASUM
                FROM BUYPROD A, PROD B
               WHERE A.BUY_PROD=B.PROD_ID
                 AND A.BUY_DATE BETWEEN TO_DATE('20050201')AND LAST_DAY(TO_DATE('20050201'))
                GROUP BY B.PROD_ID,B.PROD_NAME

서브쿼리 결과

위에 데이터 생략

---

2022.02.23이어서

사용예) 2005년 6월 모든 회원별 구매집계를 조회하시오. (2005년 6월이라는 일반조건도 사용됨)
       Alias는 회원번호, 회원명, 구매금액합계이다. MEMBER(24명,구매활동 할수도 안할수도 CART보다 종류가 더 많다.자료가 많은쪽을 출력해야 NULL값이 많이안나올수 있음)
      SELECT B.MEM_ID AS 회원번호,
             B.MEM_NAME AS 회원명,
             SUM(A.CART_QTY*C.PROD_PRICE) AS 구매금액합계
        FROM CART A, MEMBER B, PROD C  
       WHERE A.CART_MEMBER(+)=B.MEM_ID  -->MEMBER테이블기준으로 CART가 확장 
         AND A.CART_PROD(+)=C.PROD_ID   --> PROD기준으로 CART기준으로 확장 CART가 74줄이 됨 확장 허용이 안됨 그래서 오류가남 ANSI,서브쿼리 사용하기
         AND A.CART_NO LIKE '200506%'   
      GROUP BY B.MEM_ID,B.MEM_NAME
      ORDER BY 1;

->오류남 그래서 다른문으로 사용하자!

이미 cart 는 74개로 확장이 됐는데 prod 기준으로 또 해야하니까 도대체 어디에 기준을 맞출지 오류
 <ANSI 외부조인사용>

     

사용예) 2005년 6월 모든 회원별 구매집계를 조회하시오. (2005년 6월이라는 일반조건도 사용됨)
       Alias는 회원번호, 회원명, 구매금액합계이다.
SELECT B.MEM_ID AS 회원번호,
             B.MEM_NAME AS 회원명,
             NVL(SUM(A.CART_QTY*C.PROD_PRICE),0) AS 구매금액합계
        FROM CART A
        RIGHT OUTER JOIN MEMBER B ON(A.CART_MEMBER=B.MEM_ID ) -->CART는 구매한 사람만 있고 MEMBER가 더 큼
         LEFT OUTER JOIN PROD C ON(A.CART_PROD=C.PROD_ID) AND 
         -->이미 CART는 EMEBER와 조인이 되어 MEMEBR에 맞춰져 있고 CART,MEMEBR이것을 PROD하고 조인을 하는데 멤버별이기준이고 PROD멤버기준이 없으니까 PROD에 그에맞는 멤버가 생성될것임
               A.CART_NO LIKE '200506%'
         GROUP BY B.MEM_ID,B.MEM_NAME
         ORDER BY 1;

애초에 prod 는 멤버와 관련된 테이블 없어서 0으로 시작하는데 그 위에 조건들과 비교하다 보니 새로생겨야 함으로 당연히 위에 조건들 보다 작으니까 left가 와요 

<서브쿼리 사용>

   SELECT B.MEM_ID AS 회원번호,
             B.MEM_NAME AS 회원명,
             NVL(D.CSUM,0) AS 구매금액합계
        FROM MEMBER B,
             (SELECT A.CART_MEMBER AS CID,
                     SUM(A.CART_QTY*C.PROD_PRICE) AS CSUM
                 FROM CART A, PROD C
                 WHERE A.CART_PROD=C.PROD_ID
                   AND A.CART_NO LIKE '200506%'
                GROUP BY A.CART_MEMBER) D
        WHERE B.MEM_ID=D.CID(+) -->MEM:24 CID:6
        ORDER BY 1;

사용예)2005년 6월 모든 제품별 매입매출 현황을 조회하시오.
       Alias는 상품코드, 상품명, 매입수량, 매입금액, 매출수량, 매출금액
       SELECT A.PROD_ID AS 상품코드,
              A.PROD_NAME AS 상품명,
              SUM(B.BUY_QTY) AS 매입수량, 
              SUM(A.PROD_COST*B.BUY_QTY) AS 매입금액, 
              SUM(C.CART_QTY) AS 매출수량,
              SUM(C.CART_QTY*A.PROD_PRICE) AS 매출금액
         FROM PROD A
         LEFT OUTER JOIN BUYPROD B ON(A.PROD_ID=B.BUY_PROD AND
               B.BUY_DATE BETWEEN TO_DATE('20050601') AND LAST_DAY(TO_DATE('20050601')))--매입
         LEFT OUTER JOIN CART C ON(A.PROD_ID=C.CART_PROD AND 
              C.CART_NO LIKE '200506%')--매출
         GROUP BY A.PROD_ID,A.PROD_NAME
         ORDER BY 1;

데이터 일부..

<검증>

검증: 2005년 6월 매출집계
    SELECT CART_PROD,
            SUM(CART_QTY),
            SUM(CART_QTY*PROD_PRICE)
        FROM CART,PROD
       WHERE CART_PROD=PROD_ID
         AND CART_NO LIKE '200506%'
       GROUP BY CART_PROD
       ORDER BY 1;
 검증: 2005년 6월 매입집계
     SELECT BUY_PROD,
            SUM(BUY_QTY),
            SUM(BUY_QTY*PROD_COST)
        FROM BUYPROD,PROD
       WHERE BUY_PROD=PROD_ID
         AND BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630')
       GROUP BY BUY_PROD
       ORDER BY 1;

매출집계

매입집계