[Oracle] SQL JOIN : 내부 조인, 일반 조인

조인(JOIN)

    - 필요한 자료가 복수개의 테이블에 분산되어 저장되어 있으며
      공통의 컬럼으로 관계를 형성하고 있을때 이 관계를 이용하여 자료를 추출하는
      연산이 JOIN 이다.
    - 구분
      - 내부조인(INNER JOIN) / 외부조인(OUTER JOIN) 

      - 내부  : 같은 결과만 출력 / 외부조인 : 많은 쪽을 기준으로 NULL값을 추가해서 맞춰서 출력
      - 일반조인 / ANSI JOIN
         그 밖에 CARTESIAN JOIN(CROSS JOIN), NATURAL JOIN
       

1. 내부 조인

        - 조인조건에 일치하는 자료만으로 결과를 도출
        - 조인조건을 만족하지 않는 자료는 무시함
        - 동등조인(EQUI JOIN), 비동등조인(NONE EQUI-JOIN), INNER JOIN(ANSI JOIN)
    (일반조인 사용형식)

    SELECT [테이블별칭.] 컬럼명 [AS 별칭] [,]
                        :
           [테이블별칭.] 컬럼명 [AS 별칭]
      FROM 테이블명 [별칭], 테이블명 [별칭] [,테이블명 [별칭]...]
     WHERE 조인조건
      [AND 일반조건]

        - 테이블명 [별칭] : 사용되는 모든 테이블의 컬럼명이 모두 다른 경우 '별칭'은 생략 가능
        - '테이블 별칭'은 SELECT 절이나 WHERE 절 등에서 이름이 동일한 컬럼명 들을 참조할때는 반드시 사용해야 함
        - 조인조건 : 사용되는 테이블 사이의 공통 컬럼을 동등연산자('=')를 
                    사용한 조건식이나(EQUI-JOIN), 동등연산자('=') 이외의 연산자를 사용한 조건식(NONE EQUI-JOIN)을 기술
        - 조인조건과 일반조건은 AND 연산자로 연결함
        
    (ANSI 조인 사용형식)

    SELECT [테이블별칭.] 컬럼명 [AS 별칭] [,]
                        :
           [테이블별칭.] 컬럼명 [AS 별칭]
      FROM 테이블명 [별칭]
     INNER JOIN 테이블명 [별칭] ON(조인조건 [AND 일반조건])
          INNER JOIN 테이블명 [별칭] ON(조인조건 [AND 일반조건])

     WHERE 조인조건

     
  1) CARTESIAN JOIN(CROSS JOIN)
     - 조인조건이 생략되었거나 잘못된 조인조건이 부여도나ㅣ 경우
     - 결과는 두 테이블의 행은 곱한 갯수와 열은 더한 결괄르 반환
     - 반드시 필요한 경우가 아니면 수행 자제
     
 (ANSI FORMAT)

    SELECT COUNT(*)
      FROM BUYPROD, PROD, CART;

     
  사용ㅇㅖ) 

    SELECT COUNT(*)
      FROM BUYPROD, PROD, CART;
      
    SELECT COUNT(*)
      FROM BUYPROD
     CROSS JOIN PROD
     CROSS JOIN CART;

      

     

  2. 동등조인(Equi join)

     - 조인조건에 동등연산자 '='이 사용된 조인
     - 대부분의 조인이 동등조인임
     
  사용예) 사원테이블에서 근속년수가 5년이상인 사원들의 사원번호, 사원명, 부서명, 입사일 조회

         SELECT EMPLOYEE_ID AS 사원번호, 
                EMP_NAME AS 사원명, 
                DEPARTMENT_NAME AS 부서명,
                HIRE_DATE AS 입사일
           FROM HR.EMPLOYEES, HR.DEPARTMENTS 
          WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >=5
            AND HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID;

         SELECT A.EMPLOYEE_ID AS 사원번호, 
                A.EMP_NAME AS 사원명, 
                B.DEPARTMENT_NAME AS 부서명,
                A.HIRE_DATE AS 입사일
           FROM HR.EMPLOYEES A, HR.DEPARTMENTS B 
          WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM A.HIRE_DATE) >=5
            AND A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

    (ANSI JOIN)

         SELECT A.EMPLOYEE_ID AS 사원번호, 
                A.EMP_NAME AS 사원명, 
                B.DEPARTMENT_NAME AS 부서명,
                A.HIRE_DATE AS 입사일
           FROM HR.EMPLOYEES A
          INNER JOIN  HR.DEPARTMENTS B ON(A.DEPARTMENT_ID= B.DEPARTMENT_ID AND
                EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM A.HIRE_DATE) >=5);

-  ANSI JOIN은 FROM에 테이블명 하나만 올수 있음
- WHERE 절 전에 INNER 절에 적고 
- INNER 절 ) 처음에 나오는 테이블은 FROM 과 직접조인되어 질 수 있는 테이블명 기술  반드시!!)
                    ) 두번쨰 나오는 테이블은 FROM 과 조인이 안 되어도 상관없음

                    ) ON 에 AND 를 적어서 일반 조건을 집어넣을 경우 

                       지금 까지 제어되어야할 조건만 기술해야함 (순차 적용)

 

사용예) 2020년 1-3월 매입집계를 조회하시소
         Alias는 제품코드, 제품명, 매입수량, 매입금액 (단가 제품이 없음)

         SELECT A.BUY_PROD 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('20200101') AND TO_DATE('20200331')
          GROUP BY A.BUY_PROD, B.PROD_NAME
          ORDER BY 1;

-- 집계함수가 사용되엇으니까 SUM을 안쓴 SELECT빼고 다 써야함
-- WHERE 두 테이블에 동등조건 :  하나 이상의 조인 조건이 필요함 = 제품 코드

  

(ANSI JOIN)
        사용예) 2020년 1-3월 매입집계를 조회하시소
         Alias는 제품코드, 제품명, 매입수량, 매입금액 (단가 제품이 없음)

         SELECT A.BUY_PROD AS 제품코드,
                B.PROD_NAME AS 제품명, 
                SUM(A.BUY_QTY) AS 매입수량, 
                SUM(A.BUY_QTY * B.PROD_COST) AS 매입금액 
           FROM BUYPROD A
          INNER JOIN PROD B ON(A.BUY_PROD = B.PROD_ID)
          WHERE A.BUY_DATE BETWEEN TO_DATE('20200101') AND TO_DATE('20200331')
          GROUP BY A.BUY_PROD, B.PROD_NAME
          HAVING SUM(A.BUY_QTY) >=100
          ORDER BY 1;

         
  사용예) 2020년 1-6월 상품의 분류별 판매집계를 조회하시오
         Alias는 분류코드, 분류명, 판매금액(CART) 

         -- 공통 컬럼을 가지고 잇어야함/ 분류, 제품 코드르 가지고 있는 제품테이블을 사용
         -- 집적조인이 안됨 
         
         SELECT L.LPROD_GU AS 분류코드, 
                L.LPROD_NM AS 분류명, 
                SUM(C.CART_QTY * P.PROD_COST) AS 판매금액
           FROM CART C, PROD P, LPROD L
          WHERE SUBSTR(C.CART_NO,1,6) BETWEEN '202001' AND '202006'
            AND L.LPROD_GU = P.PROD_LGU
            AND P.PROD_ID = C.CART_PROD
          GROUP BY L.LPROD_GU, L.LPROD_NM
          ORDER BY 1;

          
    (ANSI JOIN) -- 이중 INNER JOIN
       

	SELECT L.LPROD_GU AS 분류코드, 
                L.LPROD_NM AS 분류명, 
                SUM(C.CART_QTY * P.PROD_COST) AS 판매금액
           FROM LPROD L
          INNER JOIN PROD P ON(L.LPROD_GU = P.PROD_LGU) --분류명 추출
          INNER JOIN CART C ON(P.PROD_ID = C.CART_PROD
            AND SUBSTR(C.CART_NO,1,6) BETWEEN '202001' AND '202006')
          GROUP BY L.LPROD_GU, L.LPROD_NM
          ORDER BY 1;

  사용예) HR계정에서 부서가 미국 이외에 위치한 부서에 근무하는 사원정보를 조회하시오
         Alias는 사원번호 E, 사원명 E, 부서코드(E/D), 부서명 D

         -- 부서의 위치(사원테이블) 사원소속 테이블 (부서테이블) 부서에 위치 (위치테이블)
         -- 미국의 국가코드는 'US'
         -- 일반 조건 : 국가 코드가 미국이 아닌거
         -- 일반조건과 조인조건은 OR | AND 로 연결 해야함
         LOCATION
         
         SELECT E.EMPLOYEE_ID AS 사원번호, 
                E.EMP_NAME AS 사원명, 
                D.DEPARTMENT_ID AS 부서코드,
                D.DEPARTMENT_NAME AS 부서명
           FROM HR.EMPLOYEES E, HR.DEPARTMENTS D, HR.LOCATIONS L
          WHERE L.LOCATION_ID != 'US' 
            AND E.EMPLOYEE_ID = D.DEPARTMENT_ID
            AND D.LOCATION_ID = L.LOCATION_ID;
            
         -- 테이블이 3개가 사용되었으니까 → WHERE 조인조건이 2개이상

 

  
  (ANSI JOIN)

         SELECT E.EMPLOYEE_ID AS 사원번호, 
                E.EMPLOYEE_NAME AS 사원명, 
                D.DEPARTMENTS_ID AS 부서코드,
                D.DEPARTMENTS_NAME AS 부서명
           FROM HR.EMPLOYEES E, HR.DEPARTMENTS D, HR.LOCATIONS L
          INNER JOIN E.EMPLOYEE_ID = D.DEPARTMENT_ID
          INNER JOIN D.LOCATION_ID = L.LOCATION_ID
            AND L.LOCATION_ID != 'US';