외부조인(OUTER JOIN)

• 내부조인은 조인조건을 만족하지 않는 자료는 무시됨
• 외부조인은 자료의 종류가 부족한 테이블에 NULL 행을 추가 삽입한 후 조인 연산 수행
• 조인 조건에서 부족한 쪽의 컬럼명 뒤에 외부조인 연산자 '(+)'를 추가
• 조인조건이 하나이상인 경우 모두 외부조인 연산자를 사용해야 함
• 동시에 한 테이블이 두개이상 외부조인으로 연결될 수 없음.즉 A, B, C 테이블이 있고 A를 기준으로 B테이블이 확장되고,
  동시에 c테이블을 기준으로 B테이블이 확장될 수 없다.
  WHERE A=B(+)
  AND C=D(+) 는 허용되지 않음

(일반외부조인 사용형식)
SELECT 컬럼list
FROM 테이블명1 [별칭1], 테이블명2 [별칭2], [,테이블명3 [별칭3],...]
WHERE 별칭1.컬럼명1(+)=별칭2.컬럼명2 -- =(+)도됨
:

(ANSI 외부조인 사용형식)
SELECT 컬럼list
FROM 테이블명1 [별칭1]
FULL|RIGHT|LEFT OUTER JOIN 테이블명2 [별칭2] ON(조인조건 [AND 일반조건])
:
[WHERE 일반조건];

-- FULL|RIGHT|LEFT
- FULL: 양쪽 모두 부족한 경우
- RIGHT: FROM절에 기술된 '테이블명1' 보다 OUTER JOIN절의 '테이블명2'의 자료가 더 많은 경우
- LEFT: FROM절에 기술된 '테이블명1' 보다 OUTER JOIN절의 '테이블명2'의 자료가 보다 더 많은 경우
**일반 외부조인사용시 조인조건과 일반조건이 동시적용되면 정확한 결과가 반환되지 않음-(해결책 => 서브쿼리 사용 or ANSI 외부조인 사용)

사용예) 모든 분류별 상품의 수를 조회하시오 --"모든" 외부조인

      SELECT COUNT(DISTINCT PROD_LGU) FROM PROD; 
 --상품테이블에서 분류코드는 6개 LPROD 분류코드는 9개
 --분류코드는 A,B 두테이블둘다 있음. SELECT절에서 쓸때 무조건 많은 쪽 테이블걸 써야함.
       
       SELECT A.LPROD_GU AS 분류코드, 
              A.LPROD_NM AS 분류명, 
              COUNT(B.PROD_ID) AS "상품의 수 " 
              -- 외부조인할 때 COUNT쓸때 *쓰면안됨. NULL값을 같는 행도 한줄로 취급되어짐. 
             --  때문에 Null값이 없는 기본키 PROD 컬럼명하나 써줌
         FROM LPROD A, PROD B
        WHERE A.LPROD_GU=B.PROD_LGU(+)
        GROUP BY A.LPROD_GU, A.LPROD_NM
        ORDER BY 1;

(ANSI FORMAT)

      SELECT A.LPROD_GU AS 분류코드, 
             A.LPROD_NM 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, A.LPROD_NM
       ORDER BY 1;

사용예) 2005년 4월 모든 상품별 매입집계를 조회하시오 --"모든"이니까 외부조인
Alias는 상품코드, 상품명, 매입수량(집계), 매입금액

--26개밖에 안나옴
       SELECT DISTINCT BUY_PROD
         FROM BUYPROD
        WHERE BUY_DATE BETWEEN TO_DATE('20050401') 
        AND TO_DATE('20050430') -- 4월에 매입된 상품 26개

   SELECT B.PROD_ID AS 상품코드,  
   -- 상품코드는 A,B 테이블 둘다 있음 더많은 PROD 테이블의 상품코드를 써야함.
          B.PROD_NAME AS 상품명, 
          SUM(A.BUY_QTY) AS 매입수량, 
          SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
          -- PROD의 매입단가를 쓰는게 좋음 
     FROM BUYPROD A, PROD B
    WHERE A.BUY_PROD(+)=B.PROD_ID --외부조인
      AND A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
    GROUP BY B.PROD_ID, B.PROD_NAME
    ORDER BY 1 ;

(ANSI) --결과 74행

       SELECT B.PROD_ID AS 상품코드,  
- 상품코드는 A,B 테이블 둘다 있음 더많은 PROD 테이블의 상품코드를 써야함.
                 B.PROD_NAME AS 상품명, 
               NVL(SUM(A.BUY_QTY),0) AS 매입수량, 
               NVL(SUM(A.BUY_QTY*B.PROD_COST),0) AS 매입금액
               -- PROD의 매입단가를 쓰는게 좋음 
         FROM BUYPROD A
         RIGHT OUTER JOIN PROD B ON (A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430'))
         GROUP BY B.PROD_ID, B.PROD_NAME
         ORDER BY 1 ;

(서브쿼리) --결과 74행

      SELECT B.PROD_ID AS 상품코드, 

 -- 상품코드는 A,B 테이블 둘다 있음 더많은 PROD 테이블의 상품코드를 써야함.
                 B.PROD_NAME AS 상품명, 
               NVL(D.CAMT,0) AS 매입수량, 
               NVL(D.SAMT,0) AS 매입금액 
               -- PROD의 매입단가를 쓰는게 좋음 
         FROM PROD B, 
        (--2005년 4월 상품별 매입집계=내부조인. 기간(일반조건)을 처리해줌
                 SELECT A.BUY_PROD AS BID,
                        SUM(A.BUY_QTY) AS CAMT,
                        SUM(A.BUY_QTY*C.PROD_COST) AS SAMT
                   FROM BUYPROD A, PROD C
                  WHERE A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
                    AND A.BUY_PROD=C.PROD_ID 
                  GROUP BY A.BUY_PROD)D
        WHERE B.PROD_ID=D.BID(+)
        ORDER BY 1;

사용예) 2005년 4월 모든 상품별 매출집계를 조회하시오
상품코드, 상품명, 매출수량(집계), 매출금액

  --2005년 4월 모든 상품별 매출
   
   -- AND A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
        GROUP BY B.PROD_ID, B.PROD_NAME
        ORDER BY 1 ;

(일반조인)

 SELECT A.PROD_ID AS 상품코드, 
           A.PROD_NAME AS 상품명, 
           SUM(B.CART_QTY) AS 매출수량, 
           SUM(A.PROD_PRICE*B.CART_QTY) AS 매출금액
      FROM PROD A, CART B
     WHERE B.CART_PROD(+)=A.PROD_ID
       AND A.PROD_ID=B.CART_PROD
       AND B.CART_NO LIKE '200504%'
  GROUP BY A.PROD_ID, A.PROD_NAME
  ORDER BY 1;

(ANSI)

 SELECT A.PROD_ID AS 상품코드, 
         A.PROD_NAME AS 상품명, 
         NVL(SUM(B.CART_QTY),0) AS 매출수량, 
         NVL(SUM(A.PROD_PRICE*B.CART_QTY),0) AS 매출금액
  FROM PROD A
  LEFT OUTER JOIN CART B ON(A.PROD_ID=B.CART_PROD 
  AND B.CART_NO LIKE '200504%')
  GROUP BY A.PROD_ID, A.PROD_NAME
  ORDER BY 1;

(서브쿼리)

    SELECT A.PROD_ID AS  상품코드, 
           A.PROD_NAME AS 상품명, 
           NVL(D.BB,0) AS 매출수량, 
           NVL(D.CC,0) AS 매출금액
      FROM PROD A, ( --2005년 4월 일반조건
                    SELECT B.PROD_ID AS AA , 
                           SUM(C.CART_QTY) AS BB , 
                           SUM(B.PROD_PRICE*C.CART_QTY) AS CC
                      FROM PROD B, CART C
                     WHERE C.CART_NO LIKE '200504%'
                       AND B.PROD_ID=C.CART_PROD
                     GROUP BY B.PROD_ID)D
     WHERE A.PROD_ID=D.AA(+)
     ORDER BY 1;

사용예) 2005년 4월 모든 상품별 매입/ 매출집계를 조회하시오
Alias는 상품코드, 상품명, 매입수량, 매출수량, 매입금액, 매출금액

       SELECT A.PROD_ID AS 상품코드, 
              A.PROD_NAME AS 상품명, 
              NVL(SUM(B.BUY_QTY),0) AS 매입수량, 
              NVL(SUM(C.CART_QTY),0) AS 매출수량, 
              NVL(SUM(B.BUY_QTY*A.PROD_COST), 0) AS 매입금액, 
              NVL(SUM(C.CART_QTY*A.PROD_PRICE),0) AS 매출금액
         FROM PROD A
         LEFT OUTER JOIN BUYPROD B ON(A.PROD_ID=B.BUY_PROD 
                                      AND B.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430'))
         LEFT OUTER JOIN CART C ON(C.CART_PROD=A.PROD_ID 
                                   AND C. CART_NO LIKE '200504%') 
         GROUP BY A.PROD_ID, A.PROD_NAME
         ORDER BY 1;

사용예) hr계정의 테이블을 이용하여 "모든" 부서별 인원수 및 평균근무년수, 평균급여를 조회하시오
-- 모든 ->외부조인, 부서별->그룹절, 인원수-> COUNT 괄호안에 기본키
Alias는 부서코드, 부서명, 인원수, 평균근무년수, 평균급여이다.

   SELECT B.DEPARTMENT_ID AS 부서코드, --EMP, DEPT에 둘다 있음. EMP 쓰면 널값 16개 DEPT 쓰면 1개의 널값 양쪽이 다 부족
          B.DEPARTMENT_NAME AS 부서명, 
          COUNT(A.EMPLOYEE_ID) AS 인원수, 
          NVL(ROUND(AVG(EXTRACT(YEAR FROM SYSDATE) -EXTRACT(YEAR FROM A.HIRE_DATE))),0) AS 평균근무년수, 
          NVL(ROUND(AVG(A.SALARY)),0) AS 평균급여
     FROM HR.EMP A
     FULL OUTER JOIN HR.DEPT B ON(A.DEPARTMENT_ID= B.DEPARTMENT_ID) -- 양쪽이 다 부족해서 FULL OUTER JOIN 
     GROUP BY  B.DEPARTMENT_ID, B.DEPARTMENT_NAME
     ORDER BY 1;

사용예) 2005년 6월 모든 회원별 구매현황을 조회하시오--"모든" 외부조인.
Alias는 회원번호, 회원명, 구매수량, 구매금액이다.

     SELECT A.MEM_ID AS 회원번호, 
          A.MEM_NAME AS 회원명, 
          NVL(SUM(B.CART_QTY),0) AS 구매수량, 
          NVL(SUM(B.CART_QTY*C.PROD_PRICE),0) AS 구매금액
     FROM MEMBER A  --"회원별"-MEMBER테이블이 젤 많이 정보 가짐
      LEFT OUTER JOIN CART B ON (A.MEM_ID = B.CART_MEMBER AND B.CART_NO LIKE '200506%')
      LEFT OUTER JOIN PROD C ON (C.PROD_ID=B.CART_PROD) -- A,B조인결과와 조인
       GROUP BY A.MEM_ID, A.MEM_NAME
       ORDER BY 1;