[Oracle SQL] 테이블조인 - 동등조인(Equi-JOIN)

고동이의 IT·2021년 10월 9일
0

Oracle SQL

목록 보기
17/31
post-thumbnail

테이블 조인

  • 관계형 데이터베이스의 주요 연산중 하나

  • 다수개의 테이블에 분산된 자료를 테이블 간에 설정된 관계를 이용하여 참조하는 연산

  • 구분
    . 일반조인 , ANSI 조인
    . 내부조인 -조인조건을 만족하는 애들만, 외부조인-가짓수가 더많은 애들만
    . 동등조인=, 세미조인, 안티조인, 카타시안조인 등

    1. 일반조인과 ANSI 조인
    • 일반조인은 DBMS 회사별로 제공되는 조인 형식
    • ANSI조인: 미국표준위원회에서 제정한 (89년 기준안) 조인문 형식
    1. 내부조인

1) 동등조인(Equi-JOIN)

 - 조인조건에 '=' 연산자가 사용된 조인
 - 가장 일반적인 조인 형식

(사용형식:일반조인)

 SELECT 컬럼list
    FROM 테이블명1 [별칭1], 테이블명2 [별칭2] [,테이블명3 [별칭3],...]
   WHERE 조인조건
    [AND 일반조건] 

  _  . '조인조건' : 사용된 테이블들 간 동일한 데이터를 가진 컬럼명을 '='연산자로 연결함
    . 조인조건의 갯수는 적어도 테이블의 수-1개 이상이어야함
    . '일반조건' 과 '조인조건'의 기술순서는 수행순서와 관계없음._
    


(사용형식:ASNI 조인)

SELECT 컬럼list
       FROM 테이블명1 [별칭1]
       INNER JOIN 테이블명2 [별칭2] ON(조인조건1 [AND 일반조건1]) 
       --1테이블과 2테이블은 이너조인수행 = 공통조인 필수로 가지고 있어야함.
       [INNER JOIN 테이블명3 [별칭3] ON(조인조건2 [AND 일반조건2])] 
       -- 테이블1,2의 조인결과와 테이블3이 조인되어짐
             :
      [WHERE 일반조건n]

      . '테이블명1'과 '테이블명2'는 반드시 직접 조인 가능해야함
      . '테이블명3'은 '테이블명1', '테이블명2' 조인 결과와 조인
      . '일반조건1'은 '테이블명1', '테이블명2'에만 관련된 조건
      . '일반조건n'은 모든 테이블에 공통적으로 적용되는 조건

사용예) 사원테이블에서 2005년 이후 입사한 사원들을 조회하시오
Alias 는 사원번호, 사원명, 부서명, 입사일이다.

   (일반조인)
SELECT A.EMPLOYEE_ID AS 사원번호, 
              A.EMP_NAME AS 사원명, 
              B.DEPARTMENT_NAME AS 부서명, 
              A.HIRE_DATE AS 입사일
         FROM HR.EMP A, HR.DEPT B
        WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
          AND EXTRACT(YEAR FROM A.HIRE_DATE) >=2005 --일반조건
          ORDER BY 3;
   (ANSI조인)
 SELECT A.EMPLOYEE_ID AS 사원번호, 
              A.EMP_NAME AS 사원명, 
              B.DEPARTMENT_NAME AS 부서명, 
              A.HIRE_DATE AS 입사일
  FROM HR.EMP A
        INNER JOIN HR.DEPT B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID
        AND EXTRACT(YEAR FROM A.HIRE_DATE) >=2005)
  ORDER BY 3;

사용예) 미국내에 있는 부서별 인원수를 조회하시오 --COUNTRY_ID가 US
Alias는 부서코드, 부서명, 인원수

   (일반조인) 
  SELECT B.DEPARTMENT_ID AS 부서코드, --A로하면?
              A.DEPARTMENT_NAME AS 부서명, 
              COUNT(*) AS 인원수
    FROM HR.DEPT A, HR.EMP B, HR.LOCATIONS C
  WHERE B.DEPARTMENT_ID=A.DEPARTMENT_ID
          AND A.LOCATION_ID=C.LOCATION_ID
          AND C.COUNTRY_ID='US'
   GROUP BY B.DEPARTMENT_ID, A.DEPARTMENT_NAME
   ORDER BY 1;
    (ANSI조인)
SELECT B.DEPARTMENT_ID AS 부서코드, 
             A.DEPARTMENT_NAME AS 부서명, 
             COUNT(*) AS 인원수
 FROM HR.DEPT A
         INNER JOIN HR.EMP B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID )
         INNER JOIN HR.LOCATIONS C ON(A.LOCATION_ID=C.LOCATION_ID)
         WHERE C.COUNTRY_ID='US'
          GROUP BY B.DEPARTMENT_ID, A.DEPARTMENT_NAME
       ORDER BY 1;

사용예) 2005년도 5월 거래처별 매입현황을 조회하시오
Alias는 거래처코드, 거래처명, 매입수량, 매입금액이다.

     SELECT C.BUYER_ID AS 거래처코드, 
            C.BUYER_NAME AS 거래처명, 
            SUM(A.BUY_QTY) AS 매입수량, 
            SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
       FROM BUYPROD A, PROD B, BUYER C
      WHERE A.BUY_PROD=B.PROD_ID
        AND B.PROD_BUYER=C.BUYER_ID
        AND A.BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050531')
      GROUP BY C.BUYER_ID, C.BUYER_NAME
      ORDER BY 1;
    (ANSI조인)

   SELECT C.BUYER_ID AS 거래처코드, 
          C.BUYER_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)
     INNER JOIN BUYER C ON(B.PROD_BUYER=C.BUYER_ID)
     WHERE A.BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050531')
      GROUP BY C.BUYER_ID, C.BUYER_NAME
   ORDER BY 1;

** 재고수불테이블을 생성하시오

CREATE TABLE REMAIN(
    REMAIN_YEAR    CHAR(4) NOT NULL, 
    --기본키는 기본적으로 NOT NULL 때문에 NOT NULL 안써도됨
    PROD_ID        VARCHAR2(10),
    REMAIN_J_00    NUMBER(5) DEFAULT 0 ,
    REMAIN_I       NUMBER(5)DEFAULT 0 ,
    REMAIN_O       NUMBER(5) DEFAULT 0 ,
    REMAIN_J_99    NUMBER(5) DEFAULT 0 ,
    REMAIN_DATE    DATE ,
CONSTRAINT pk_remain PRIMARY KEY(REMAIN_YEAR,PROD_ID),
CONSTRAINT fk_remain_prod FOREIGN KEY(PROD_ID)
 REFERENCES PROD(PROD_ID));

 

** 생성된 재고수불테이블에 다음 자료를 입력하시오
1)년도: 2005
2)상품코드: PROD테이블의 상품코드
3)기초재고수량: PROD테이블의 적정재고(PROD_PROPERSTOCK)
4)기말재고: 기초재고
5)갱신일자: 2005년 1월 1일

 INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,REMAIN_J_99,REMAIN_DATE)
           SELECT '2005', PROD_ID, PROD_PROPERSTOCK, PROD_PROPERSTOCK, TO_DATE('20050101')
             FROM PROD;
             -- 서브쿼리 쓰는 INSERT절은 VALUES 생략
         SELECT * FROM REMAIN;
         
         UPDATE REMAIN SET REMAIN_J_00=0, REMAIN_J_99=0, REMAIN_I=0, REMAIN_O=0;
         
         COMMIT;
       

사용예) 2005년도 1~4월 제품별 매입현황을 조회하시오
Alias는 상품코드, 매입수량이다.

       SELECT BUY_PROD AS BID, 
              SUM(BUY_QTY) AS CNT
         FROM BUYPROD
        WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050331')
      GROUP BY BUY_PROD;

사용예)위 예에서 조회된 자료를 이용하여 재고수불테이블을 갱신하시오

 UPDATE REMAIN A 
        SET (A.REMAIN_I, A.REMAIN_J_99,A.REMAIN_DATE) =
        (SELECT B.CNT, B.CNT, TO_DATE('20050430')
           FROM (SELECT BUY_PROD AS BID, 
                        SUM(BUY_QTY) AS CNT
                   FROM BUYPROD
                  WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050430')
                  GROUP BY BUY_PROD) B
                  WHERE A.PROD_ID=B.BID)
    WHERE REMAIN_YEAR ='2005'
      AND PROD_ID IN(SELECT DISTINCT BUY_PROD
                       FROM BUYPROD
                      WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050430'));
                      
                      SELECT * FROM REMAIN;
                  COMMIT;


사용예) 2005년도 4월 제품별 매출현황을 조회하시오
Alias 상품코드, 매출수량

       SELECT CART_PROD AS CID, 
              SUM(CART_QTY) AS CMT
         FROM CART
        WHERE CART_NO LIKE '200504%'
        GROUP BY  CART_PROD;
    (재고수불테이블 UPDATE)

      UPDATE REMAIN A 
         SET (A.REMAIN_O, A.REMAIN_J_99,A.REMAIN_DATE) =
             (SELECT A.REMAIN_O+B.CMT, A.REMAIN_J_99-B.CMT, TO_DATE('20050430')
                FROM (SELECT CART_PROD AS CID, 
                     SUM(CART_QTY) AS CMT
                 FROM CART
                WHERE CART_NO LIKE '200504%'
                GROUP BY CART_PROD) B
                WHERE A.PROD_ID=B.CID)
       WHERE REMAIN_YEAR ='2005'
         AND PROD_ID IN(SELECT DISTINCT CART_PROD
                           FROM CART
                          WHERE CART_NO LIKE '200504%');
                          
                          SELECT * FROM REMAIN;
                          COMMIT;
profile
삐약..뺙뺙

0개의 댓글