[Oracle SQL Developer] 기초 XI - JOIN 1

테이블 조인 (TABLE JOIN)

---

1. TABLE JOIN? 
  - 관계형데이터 베이스의 핵심 기능이다.
  - 수행 조건은 테이블 사이에 공통된 컬럼(외래키)가 존재할 경우 수행할 수 있다.
  - 분산 저장된 테이블간의 컬럼을 가져오거나 검색할 수 있다.
  - ANSI형식과 일반 JOIN 형식이 다르다.
  
  1.1. 일반 JOIN 형식
    1) Cartesian Product
      - 모든 가능한 행들의 조합을 결과값으로 반환한다.
        A 테이블(1000행 15열) * B테이블(500행 10열) 의 결과는 500000행, 25열이 된다.
      - 조인 조건이 없거나 잘못된 조인 조건이 부여될시 발생한다.
      
    예) 장바구니 테이블과 상품테이블을 CARTESIAN PRDUCT를 발생시켜보자.
    
    
    SELECT COUNT(*) "행의 갯수"
      FROM CART A, PROD B;
      
      
    2) EQUI JOIN(= INNER JOIN) 
      - 내부조인(SIMPLE, INNER JOIN) 이라고도 한다. 대부분의 JOIN 형식이다.
      - WHERE 조건에 = (동등) 연산자를 사용하기 때문에 EQUI JOIN이라고 한다.
      - JOIN 조건이 일치하는 자료만 반환된다.
      - JOIN 조건에 맞지 않으면 무시된다. ( =? 교집합 )
      
    예) 사원테이블에서 MANAGER_ID가 100인 사원정보를 조회하시오.
        Alias는 사원번호, 사원명, 부서명, 급여이다.
    
    SELECT A.EMPLOYEE_ID 사원번호,
           A.EMP_NAME 사원명, 
           B.DEPARTMENT_NAME 부서명,
           A.SALARY 급여
      FROM EMPLOYEES A, DEPARTMENTS B
     WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
       AND A.MANAGER_ID = 100;
  
    예) 사원테이블에서 사원명, 부서번호, 부서명을 조회하시오.
    
    SELECT COUNT(*) FROM EMPLOYEES;
    
    SELECT A.EMP_NAME 사원명, 
           A.DEPARTMENT_ID 부서번호, 
           B.DEPARTMENT_NAME 부서명
      FROM EMPLOYEES A, DEPARTMENTS B
     WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);
     -- (+) 외부 조인은 부족한 테이블쪽은 모두 NULL 처리된다.
     
    예) 장바구니 테이블에서 2005년 6월 판매된 상품별 판매금액을 구하시오.
        Alias는 상품번호, 판매수량, 판매금액 + 상품명

    SELECT A.CART_PROD 상품번호, 
           B.PROD_NAME 상품명,
           SUM(A.CART_QTY) 판매수량,
           SUM(A.CART_QTY * B.PROD_PRICE) 판매금액
      FROM CART A, PROD B
     WHERE SUBSTR(CART_NO,1,6) LIKE '200506%'
       AND A.CART_PROD = B.PROD_ID -- 외부조인을 사용하려면 SUBQ 또는 ANSI를 사용한다.
     GROUP BY A.CART_PROD, B.PROD_NAME
     ORDER BY 1;
     
     SELECT COUNT(*) FROM PROD;
      
      
    예) 장바구니 테이블에서 2005년 6월 판매된 회원별 판매금액을 구하시오.
        Alias는 상품번호, 판매수량, 판매금액 + 회원명
      
    SELECT C.MEM_ID 회원번호, 
           C.MEM_NAME 회원명,
           SUM(A.CART_QTY) 판매수량,
           SUM(A.CART_QTY * B.PROD_PRICE) 판매금액
      FROM CART A, PROD B, MEMBER C
     WHERE SUBSTR(CART_NO,1,6) LIKE '200506%'
       AND A.CART_PROD = B.PROD_ID -- 외부조인을 사용하려면 SUBQ 또는 ANSI를 사용한다.
       AND A.CART_MEMBER = C.MEM_ID
     GROUP BY C.MEM_ID, C.MEM_NAME
     ORDER BY 1; 
      
    예) 장바구니 테이블에서 2005년 6월 판매정보를 이용하여 거래처별 판매금액합계를 구하시오.
        Alias는 거래처번호, 거래처명, 판매수량, 판매금액
      
    SELECT C.BUYER_ID 거래처번호, 
           C.BUYER_NAME 거래처명,
           SUM(A.CART_QTY) 판매수량,
           SUM(A.CART_QTY * B.PROD_PRICE) 판매금액
      FROM CART A, PROD B, BUYER C, MEMBER D
     WHERE A.CART_NO LIKE '200506%'
       AND A.CART_PROD = B.PROD_ID -- 카트, 상품 테이블의 상품ID가 일치
       AND D.MEM_ID = A.CART_MEMBER -- 멤버, 카드 테이블의 멤버ID가 일치
       AND B.PROD_BUYER = C.BUYER_ID -- 상품, 거래처 테이블의 거래처ID가 일치
     GROUP BY C.BUYER_ID, C.BUYER_NAME
     ORDER BY 1;  
      
    예) 2005년도 5~6월 매입정보를 이용하여 거래처별 월별 매입액을 조회하시오.
       단, 매입금액이 1000만원 이상인 거래처 정보만 조회할 것.
       Alias는 거래처코드, 거래처명, 매입월, 매입수량,매입금액합계
    
    SELECT B.BUYER_ID AS 거래처코드, 
           B.BUYER_NAME AS 거래처명, 
           EXTRACT(MONTH FROM A.BUY_DATE) AS 매입월,
           SUM(A.BUY_QTY) AS 매입수량, 
           SUM(A.BUY_COST * A.BUY_QTY) AS 매입금액합계
      FROM BUYPROD A, BUYER B, PROD C
     WHERE A.BUY_DATE BETWEEN TO_DATE('2005/04/01','YYYY/MM/DD') AND TO_DATE('20050630')  -- 일반조건
       AND A.BUY_PROD = C.PROD_ID -- JOIN 조건 // 납품, 상품 테이블에서 상품별 ID가 일치
       AND B.BUYER_ID = C.PROD_BUYER -- JOIN 조건 // 거래처, 상품 테이블에서 거래처 ID가 일치
     GROUP BY B.BUYER_ID, B.BUYER_NAME, EXTRACT(MONTH FROM A.BUY_DATE)
    HAVING SUM(A.BUY_COST * A.BUY_QTY) >= 10000000  -- 집계함수 일반 조건 
     ORDER BY 1; 

     
   
  1.2. ANSI INNER JOIN
    1) 사용 형식
      SELECT 컬럼LIST
        FROM 테이블명1 
      -- ### 기준테이블 설정을 다른테이블들과 연관성이 있는 테이블이 기준이 되어야한다.
       INNER JOIN 테이블명2 ON (테이블1과 테이블2의 조인조건)
        [AND 테이블명2에 연관된 일반 조건]
      [INNER JOIN 테이블명3 ON (테이블1과 테이블3의 조인조건)
        [AND 테이블명3에 연관된 일반 조건]]
                      :
       WHERE 일반조건;  
     
    SELECT B.BUYER_ID AS 거래처코드, 
           B.BUYER_NAME AS 거래처명, 
           EXTRACT(MONTH FROM A.BUY_DATE) AS 매입월,
           SUM(A.BUY_QTY) AS 매입수량, 
           SUM(A.BUY_COST * A.BUY_QTY) AS 매입금액합계
      FROM PROD C
     INNER JOIN BUYPROD A ON (A.BUY_PROD = C.PROD_ID)
     INNER JOIN BUYER B ON (B.BUYER_ID = C.PROD_BUYER)
     WHERE A.BUY_DATE BETWEEN TO_DATE('2005/04/01','YYYY/MM/DD') AND TO_DATE('20050630')  -- 일반조건
     GROUP BY B.BUYER_ID, B.BUYER_NAME, EXTRACT(MONTH FROM A.BUY_DATE)
    HAVING SUM(A.BUY_COST * A.BUY_QTY) >= 10000000  -- 집계함수 일반 조건 
     ORDER BY 1;   
     
     
     
     
     
  1.3. SELF JOIN 
    - 하나의 테이블에 두개이상의 별칭을 사용하여 JOIN 을 수행한다.

    예) 사원테이블에서 부서코드가 20번으로 동일하고 사원번호가 다른 사원보다 더 작은 사원의 정보를 조회하시오.
        Alias는 사원번호, 사원명,부서코드,
    
    SELECT A.EMPLOYEE_ID AS 사원번호, 
           A.EMP_NAME AS 사원명,
           A.DEPARTMENT_ID AS 부서코드,
           B.EMPLOYEE_ID AS 사원번호, 
           B.EMP_NAME AS 사원명,
           B.DEPARTMENT_ID AS 부서코드
      FROM EMPLOYEES A, EMPLOYEES B
     WHERE A.DEPARTMENT_ID = 20
       AND A.DEPARTMENT_ID = B.DEPARTMENT_ID
       AND A.EMPLOYEE_ID < B.EMPLOYEE_ID;
    
     
    SELECT EMP_NAME
      FROM EMPLOYEES
     WHERE DEPARTMENT_ID=20; 
     
     
     
    예) 사원테이블에서 각 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원정보를 조회하시오.
        Alias는 사원번호, 사원명, 급여
        
    SELECT A.EMPLOYEE_ID AS 사원번호, 
           A.EMP_NAME AS 사원명,
           A.DEPARTMENT_ID AS 부서코드,
           D.DEPARTMENT_NAME 부서명,
           A.SALARY AS 급여,
           C.BSAL AS 부서평균급여
      FROM EMPLOYEES A, (SELECT B.DEPARTMENT_ID AS BID,
                                ROUND(AVG(NVL(B.SALARY,0))) AS BSAL
                           FROM EMPLOYEES B
                          GROUP BY B.DEPARTMENT_ID) C, DEPARTMENTS D
     WHERE C.BSAL <= A.SALARY
       AND A.DEPARTMENT_ID = C.BID
       AND D.DEPARTMENT_ID = A.DEPARTMENT_ID
     ORDER BY 3;
        
     
    SELECT DEPARTMENT_ID AS 부서코드,
           ROUND(AVG(NVL(A.SALARY,0))) AS 평균급여
      FROM EMPLOYEES A
     GROUP BY DEPARTMENT_ID;
     
     
     
  1.4. NON-EQUI JOINS
    - 조인 조건에 '=' 등호 연산자 이외의 연산자가 사용되는 JOIN 형태이다.  
    - IN, ANY, SOME, EXISTS 연산자가 사용된다.
    
    예) 사원테이블과 부서테이블의 정보를 이용하여 사원번호, 사원명, 부서코드, 부서명을 조회하시오.
        조건 : 부서테이블의 MANAGER_ID가 NULL인 부서에 속하지 않은 사원정보
    
    
    SELECT A.EMPLOYEE_ID AS 사원번호, 
           A.EMP_NAME AS 사원명, 
           A.DEPARTMENT_ID AS 부서코드, 
           B.DEPARTMENT_NAME AS 부서명
      FROM EMPLOYEES A, DEPARTMENTS B
     WHERE EXISTS/*<>ANY,IN*/ (SELECT C.DEPARTMENT_ID AS CID -- * 또는 1 을 써도 TRUE 값이므로 모두 출력된다.
                              FROM DEPARTMENTS C
                             WHERE C.MANAGER_ID IS NOT NULL) -- MANAGER_ID가 널이 아닌값을 테이블에서 모두 조회해서 해당되는 값만 반환된다.
       AND A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);