[Oracle SQL Developer] 기초 XII - JOIN II

1. 외부조인

---

  - 내부조인은 조인조건에 일치하는 자료만 결과로 반환한다.
  - 외부조인은 자료(자료의 종류)가 부족한 테이블에 NULL값을 추가하여 조인 결과를 반환한다.
  - 조인조건 기술에서 자료의 종류가 부족한 테이블에 '(+)' 를 추가한다.
  - 처리속도가 저하될 수 있다.
  - 외부조인조건이 복수개인 경우 모두 '(+)'를 추가해준다.
  - 한번에 하나의 테이블만 외부조인을 사용할 수 있다. 
    즉, 조인대상 테이블이 A, B, C 인경우 A를 기준으로 B테이블과 외부조인을 연결했다면 동시에 C를 기준으로 B테이블과 외부조인을 사용할 수 없다.
  - '(+)'연산자가 붙은 조건과 OR 연산자는 같이 사용할 수 없다.
  
  예) 상품테이블에서 "모든" 분류코드별로 제품수를 조회하시오. -- "모든" 이 들어가면 OUTERJOIN을 이용하여 쿼리를 작성한다.
  
  -- 내부조인인 경우    
  SELECT DISTINCT A.PROD_LGU AS 상품분류코드,
         B.LPROD_NM AS 분류명,
         COUNT(*) AS 상품수
    FROM PROD A, LPROD B
   WHERE B.LPROD_GU = A.PROD_LGU
   GROUP BY A.PROD_LGU, B.LPROD_NM
   ORDER BY 1;
   
   
  -- 외부조인인 경우    
  SELECT DISTINCT B.LPROD_GU AS 상품분류코드,
         B.LPROD_NM AS 분류명,
         COUNT(A.PROD_ID) AS 상품수 -- COUNT에 컬럼명을 줘서 상품이 등록되지 않았다면 0이 출력되게 한다.
    FROM PROD A, LPROD B
   WHERE B.LPROD_GU = A.PROD_LGU(+) -- PROD 테이블의 종류가 더 적으므로 해당되는 값에 (+) 연산자를 작성한다.
   GROUP BY B.LPROD_GU, B.LPROD_NM
   ORDER BY 1;
 
  SELECT DISTINCT PROD_LGU 분류코드
    FROM PROD;


  예) 2005년 6월 판매된 상품의 종류를 조회하시오.
  
  
  SELECT DISTINCT CART_PROD AS 상품번호,
         COUNT(*) AS 판매량
    FROM CART
   WHERE CART_NO LIKE '200505%'
   GROUP BY ROLLUP(CART_PROD)
   ORDER BY 1;
   
  SELECT COUNT(*) AS 판매량
    FROM CART
   WHERE CART_NO LIKE '200505%'; 
   
  SELECT COUNT(*) AS 제품종류
    FROM PROD;


2. ANSI형식의 외부조인(ANSI OUTER JOIN)
  2.1. 사용 형식

    SELECT 컬럼LIST
      FROM 테이블명1
      LEFT|RIGHT|FULL OUTER JOIN 테이블명2 ON (조인조건1)
      [AND 일반조건] -- 조인조건과 함께 사용되는 일반 조건이므로 해당하는 자료가 없으면 없다는 항목이 추가된다.
     [LEFT|RIGHT|FULL OUTER JOIN 테이블명3 ON (조인조건2)
      [AND 일반조건]]
               :
    [WHERE 일반조건] -- 전체조건이 부여돼서 없는 나머지 항목이 생략될 수 있다.         
        

 

  . LEFT|RIGHT  : FROM 다음에 기술된 '테이블명1'이 자료(종류)가 부족한 테이블이면 RIGHT, 이외의 테이블이면 LEFT 를 기술한다.
  . FULL : 양쪽 모두 자료가 부족한 경우 모두 NULL 처리 후 조인한다. (* 사용자제 )
  . '조인조건1' : '테이블명1' 과 '테이블명2'에 대한 조인조건을 기술한다.
  . ANSI에서는 '(+)' 연산자는 사용하지 않는다.
  
  -- ANSI OUTER JOIN 형식
  SELECT DISTINCT B.LPROD_GU AS 상품분류코드,
         B.LPROD_NM AS 분류명,
         COUNT(A.PROD_ID) AS 상품수 -- COUNT에 컬럼명을 줘서 상품이 등록되지 않았다면 0이 출력되게 한다.
    FROM PROD A
   RIGHT OUTER JOIN LPROD B ON (B.LPROD_GU = A.PROD_LGU)
   GROUP BY B.LPROD_GU, B.LPROD_NM
   ORDER BY 1;

  예) 2005년 6월에 모든 상품별 판매정보를 조회하시오.
      Alias 상품번호, 상품명, 판매수량합계, 판매금액
      
  SELECT B.PROD_ID AS 상품번호, 
         B.PROD_NAME AS 상품명, 
         SUM(NVL(A.CART_QTY,0)) AS 판매수량합계, 
         SUM(NVL((B.PROD_PRICE * A.CART_QTY),0)) AS 판매금액합계
    FROM CART A, PROD B
   WHERE CART_NO LIKE '200506%'
     AND A.CART_PROD(+) = B.PROD_ID
   GROUP BY B.PROD_ID, B.PROD_NAME
   ORDER BY 1;
  
  SELECT B.PROD_ID AS 상품번호, 
         B.PROD_NAME AS 상품명, 
         SUM(NVL(A.CART_QTY,0)) AS 판매수량합계, 
         SUM(NVL((B.PROD_PRICE * A.CART_QTY),0)) AS 판매금액합계
    FROM CART A 
   RIGHT OUTER JOIN PROD B ON(A.CART_PROD = B.PROD_ID
     AND CART_NO LIKE '200506%')
   GROUP BY B.PROD_ID, B.PROD_NAME
   ORDER BY 1;  
      
  -- SUBQUERY 사용  // =? SUBQUERY는 사용할 테이블을 조건을 맞춘 테이블을 만들어서 사용하는 개념으로 생각해야겠다.
  
  SELECT B.PROD_ID AS 상품번호, 
         B.PROD_NAME AS 상품명, 
         NVL(A.SQTY,0) AS 판매수량합계, 
         NVL(A.SAMT,0) AS 판매금액합계
    FROM PROD B, (SELECT C.CART_PROD AS NO, 
                         SUM(C.CART_QTY) AS SQTY,
                         SUM(C.CART_QTY * D.PROD_PRICE) AS SAMT
                    FROM CART C, PROD D
                   WHERE D.PROD_ID = C.CART_PROD 
                     AND C.CART_NO LIKE '200506%'
                   GROUP BY C.CART_PROD) A
   WHERE A.NO(+) = B.PROD_ID
   ORDER BY 1;  

  SELECT C.CART_PROD AS 번호, 
         SUM(C.CART_QTY) AS 합계,
         SUM(C.CART_QTY * D.PROD_PRICE) AS 금액합계
    FROM CART C, PROD D
   WHERE D.PROD_ID = C.CART_PROD 
     AND C.CART_NO LIKE '200506%'
   GROUP BY C.CART_PROD; 

  문제) 사원테이블과 부서테이블을 이용하여 모든 부서별 사원수를 출력하시오.
       Ailas는 부서번호, 부서명, 사원수
  
  SELECT B.DEPARTMENT_ID AS 부서번호, 
         B.DEPARTMENT_NAME AS 부서명, 
         COUNT(A.EMPLOYEE_ID) AS 사원수
    FROM EMPLOYEES A, DEPARTMENTS B
   WHERE A.DEPARTMENT_ID(+) = B.DEPARTMENT_ID
   GROUP BY B.DEPARTMENT_ID, B.DEPARTMENT_NAME
   ORDER BY 1;
   
  --ANSI 
  SELECT NVL(B.DEPARTMENT_ID,4) AS 부서번호, 
         NVL(B.DEPARTMENT_NAME,4) AS 부서명, 
         NVL(COUNT(A.EMPLOYEE_ID),0) AS 사원수
    FROM EMPLOYEES A
    FULL OUTER JOIN DEPARTMENTS B ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID)
   GROUP BY B.DEPARTMENT_ID, B.DEPARTMENT_NAME
   ORDER BY 1;   
   
   
   
   
   
  문제) 2005년 모든 거래처별 매입집계를 조회하시오.
       Alias는 거래처코드, 거래처명, 매입금액합계 
  -- 일반적인 외부조인으로 X
  SELECT A.BUYER_ID AS 거래처코드, 
         A.BUYER_NAME AS 거래처명, 
         SUM(B.BUY_COST * B.BUY_QTY) AS 매입금액합계 
    FROM BUYER A, BUYPROD B, PROD C
   WHERE C.PROD_BUYER = A.BUYER_ID(+)
     AND C.PROD_ID = B.BUY_PROD(+)
     AND EXTRACT(YEAR FROM B.BUY_DATE) = '2005'
   GROUP BY A.BUYER_ID, A.BUYER_NAME
   ORDER BY 1;
  
  -- ANSI
  SELECT A.BUYER_ID AS 거래처코드, 
         A.BUYER_NAME AS 거래처명, 
         SUM(B.BUY_COST * B.BUY_QTY) AS 매입금액합계 
    FROM PROD C
   RIGHT OUTER JOIN BUYER A ON (C.PROD_BUYER = A.BUYER_ID)
    LEFT OUTER JOIN BUYPROD B ON (C.PROD_ID = B.BUY_PROD)
     AND EXTRACT(YEAR FROM B.BUY_DATE) = '2005'
   --AND BUY_DATE BETWWN '20050101' AND '20051231'
   GROUP BY A.BUYER_ID, A.BUYER_NAME
   ORDER BY 1;  
  
  예) 2005년 4월 전체 회원의 구매현황을 조회하시오. -- 전체 회원이니까 MEMBER 테이블을 기준으로 한다.
      Alias는 회원번호, 회원명, 구매수량합계, 구매금액합계
      
  SELECT A.MEM_ID AS 회원번호, 
         A.MEM_NAME AS 회원명, 
         SUM(NVL(B.CART_QTY,0)) AS 구매수량합계, 
         SUM(NVL(B.CART_QTY * C.PROD_PRICE,0)) AS 구매금액합계      
    FROM MEMBER A, CART B, PROD C
   WHERE B.CART_NO LIKE '200504%' -- 일반조건
     AND C.PROD_ID = B.CART_PROD(+) -- PROD ID가 일치하는 조건
     AND B.CART_MEMBER(+) = A.MEM_ID -- MEMBER ID가 일치하는 조건
   GROUP BY A.MEM_ID, A.MEM_NAME
   ORDER BY 1;

  
  SELECT A.MEM_ID AS 회원번호, 
         A.MEM_NAME AS 회원명, 
         SUM(NVL(B.CART_QTY,0)) AS 구매수량합계, 
         SUM(NVL(B.CART_QTY * C.PROD_PRICE,0)) AS 구매금액합계      
    FROM MEMBER A
    LEFT OUTER JOIN CART B ON (B.CART_MEMBER(+) = A.MEM_ID)
     AND B.CART_NO LIKE '200504%'
    LEFT OUTER JOIN PROD C ON (C.PROD_ID = B.CART_PROD)
    /*FROM CART B
   RIGHT OUTER JOIN PROD C ON(B.CART_PROD = C.PROD_ID AND CART_NO LIKE '200504%')
   RIGHT OUTER JOIN MEMBER A ON(B.CART_MEMBER = A.MEM_ID)*/
   GROUP BY A.MEM_ID, A.MEM_NAME
   ORDER BY 1;  
   
   
   
  문) 2005년 5월 모든 상품의 입/출고 현황을 조회하시오.
      Alias는 상품번호, 상품명, 입고량, 출고량 
      
  SELECT A.PROD_ID AS 상품번호, 
         A.PROD_NAME AS 상품명, 
         SUM(B.BUY_QTY) AS 입고량, 
         SUM(C.CART_QTY) AS 출고량       
    FROM PROD A, BUYPROD B, CART C
   WHERE A.PROD_ID =  C.CART_PROD(+)
     AND A.PROD_ID = B.BUY_PROD(+)
     AND C.CART_NO LIKE '200505%'
   GROUP BY A.PROD_ID, A.PROD_NAME
   ORDER BY 1;
   
---- ANSI - 74개

  SELECT A.PROD_ID AS 상품번호, 
         A.PROD_NAME AS 상품명, 
         NVL(SUM(B.BUY_QTY),0) AS 입고량, 
         NVL(SUM(C.CART_QTY),0) AS 출고량       
    FROM PROD A--, BUYPROD B, CART C
    LEFT OUTER JOIN CART C ON (A.PROD_ID =  C.CART_PROD AND C.CART_NO LIKE '200505%') 
    LEFT OUTER JOIN BUYPROD B ON (A.PROD_ID = B.BUY_PROD AND EXTRACT(YEAR FROM B.BUY_DATE) = '2005' 
                                                         AND EXTRACT(MONTH FROM B.BUY_DATE) = '05')
                                                         -- AND BUY_DATE BETWEEN '20050501' AND '20050531' 로 작성해도된다.
   GROUP BY A.PROD_ID, A.PROD_NAME
   ORDER BY 1;
  
  SELECT NVL(SUM(B.BUY_QTY),0) AS 입고량
    FROM BUYPROD B
   WHERE EXTRACT(YEAR FROM B.BUY_DATE) = '2005'
     AND EXTRACT(MONTH FROM B.BUY_DATE) = '06';
  
  
  
  ex) 2005년 전체 상품의 판매실적을 조회하시오.
        상품번호, 상품명, 판매수량, 판매금액
        
        
  SELECT A.PROD_ID AS 상품번호, 
         A.PROD_NAME AS 상품명, 
         B.AMTP AS 판매수량, 
         B.PRODPRICE AS 판매금액
    FROM PROD A, (  SELECT A.CART_PROD AS CID,
                           SUM(A.CART_QTY) AS AMTP,
                           SUM(A.CART_QTY * B.PROD_PRICE) AS PRODPRICE
                      FROM CART A, PROD B
                     WHERE A.CART_PROD = B.PROD_ID
                       AND A.CART_NO LIKE '2005%'
                     GROUP BY A.CART_PROD) B
   WHERE A.PROD_ID = B.CID(+);                  
    
    
    
  -- 제품별 판매수량, 판매금액  
  SELECT A.CART_PROD AS CID,
         SUM(A.CART_QTY) AS AMTP,
         SUM(A.CART_QTY * B.PROD_PRICE) AS PRODPRICE
    FROM CART A, PROD B
   WHERE A.CART_PROD = B.PROD_ID
     AND A.CART_NO LIKE '2005%'
   GROUP BY A.CART_PROD;
  
  
  ex) 2005년 전체 상품의 매입실적을 조회하시오.
      상품번호, 상품명, 매입수량, 매입금액
  
  SELECT A.PROD_ID AS 상품번호, 
         A.PROD_NAME AS 상품명, 
         B.BQTY AS 매입수량, 
         B.SUMIN AS 매입금액
    FROM PROD A, (SELECT A.PROD_ID AS PID,
                         SUM(B.BUY_QTY) AS BQTY,
                         SUM(B.BUY_QTY * B.BUY_COST) AS SUMIN
                    FROM PROD A, BUYPROD B
                   WHERE A.PROD_ID = B.BUY_PROD
                     AND BUY_DATE BETWEEN '20050101' AND '20051231'
                   GROUP BY A.PROD_ID) B
   WHERE A.PROD_ID = B.PID(+); 
  
  
  
  
  -- 2005년 상품별 매입실적
  SELECT A.PROD_ID AS PID,
         SUM(B.BUY_QTY) AS BQTY,
         SUM(B.BUY_QTY * B.BUY_COST) AS SUMIN
    FROM PROD A, BUYPROD B
   WHERE A.PROD_ID = B.BUY_PROD
     AND BUY_DATE BETWEEN '20050101' AND '20051231'
   GROUP BY A.PROD_ID;