조인 JOIN과 서브쿼리 subQuery

JOIN :  관계형 데이터베이스의 특징인 관계(Relatrionship)를 이용한 연산

- 테이블의 정규화 과정을 거치면 자료가 여러 테이블에 분산되어 저장되고 이러한 자료를
   외래키관계를 이용하여 조회할 수 있는 연산을 조인이라 함

- 종류
  . 외부조인(Outer Join), 내부조인(Inner Join)   
  . 일반조인, ANSI JOIN                       
  . 동등조인, 비동등조인, 셀프조인, Cartesian Product 등 

---

조인을 하려면?

1. 관계파악

2. 누가 부모인가 부모쪽으로 방향 

---

1. Cartesian Product
 - 조인조건이 없거나 조인조건이 잘못된 경우
 - n행 m열의 테이블과 a행 b열의 테이블이 카타시안 곱이 수행되면 최악의 경우
   (조인 조건이 없는 경우) n x a 행, m+b열의 결과를 반환함
 - 반드시 필요한 경우를 제외하고 수행해서는 안됨

(사용형식-일반조인)
    SELECT 컬럼List
      FROM 테이블명1 [별칭1], 테이블명2 [별칭2] [,테이블명3 [별칭3], ...]
   [WHERE] 조인조건;
   
(사용형식-ANSI조인)  CROSS JOIN = 안시의 카타시안 프로덕트
    SELECT 컬럼List
      FROM 테이블명1 [별칭1]
     CROSS JOIN 테이블명2 [별칭2] [ON 조인조건]
    [CROSS JOIN 테이블명3 [별칭3] [ON 조인조건]]
                   :
   [WHERE] 조인조건;

---

2. 내부 조인
 - 조인조건을 만족하는 데이터만 결과로 반환하고 조인조건을 만족하지 않는 자료는 무시
 1) 동등조인(Equi Join)
  - 조인조건에 '='연산자를 사용한 조인
  - 대부분의 내부 조인이 이에 속함

 (사용형식-일반조인)
    SELECT 컬럼 list  --별칭.컬럼명 
      FROM 테이블명1 [별칭1],테이블명2 [별칭2][,테이블명3 [별칭3],...]
     WHERE [별칭1].컬럼명=[별칭2].컬럼명  --공통의 컬럼 값이면 조인 가능. 꼭 외래키,기본키 관계가 아니어도 됨.
                        :
       AND
      [AND 일반조건]
     . 사용된 테이블이 n개 일때 조인조건은 적어도 n-1개 이상이어야 함
     . 일반조건과 조인조건은 AND로 연결되며 기술 순서는 실행과 관계없음
     
 (사용형식-ANSI조인)
    SELECT 컬럼 list
      FROM 테이블명1 [별칭1]    --내부조인일때는 크게 상관 없지만 외부조인할때는 웨어절에 조건을 기술하면 내부조인으로 변경되어버림
     INNER JOIN 테이블명2 [별칭2] ON([별칭1].컬럼명=[별칭2].컬럼명 [AND 일반조건1])  --테이블명1과 2는 반드시 직접조인해야한다.
    [INNER JOIN 테이블명3 [별칭3] ON([별칭].컬럼명=[별칭3].컬럼명 [AND 일반조건2])]  --테이블 1,2의 결과와 3이 조인
                        :     
    [WHERE 일반조건]
     . '테이블명1'과 '테이블명2'는 반드시 직접 조인 가능한 관계이어야 함
     . '일반조건1'은 '테이블명1'과 '테이블명2'에 관련된 조건이어야 함 
     . '테이블명3' 부터는 '테이블명1'과 '테이블명2'의 조인 결과와 조인
     . 'WHERE 일반조인':모든 테이블에 공통적으로 부여되는 조건 기술

---

3. 외부조인
 - 자료의 종류가 부족한 테이블에 NULL 값을 갖는 행을 삽입하여 수행하는 조인

 (일반 외부조인형식)  --많은쪽을 기준으로 하기에 자료가 너무 많아지므로, 되도록 사용 x
     SELECT 컬럼list
       FROM 테이블명1 [별칭1], 테이블명2 [별칭2],...
      WHERE 컬럼명1[(+)]=컬럼명2 --컬러명1이 자료가 부족한 테이블에 속한 경우
                 :
      . 일반 외부조인인 경우 외부조인 연산자 '(+)'를 부족한 자료를 가지고 있는 테이블
        쪽에 붙임
      . 여러 조인조건이 모두 외부조인조건인 경우 모두 외부조인연산자를 사용해야 함
      . 하나의 테이블은 여러 테이블과 동시에 외부조인 될 수 없다.
        즉, A,B,C테이블이 연산에 참여하고 A를 기준으로 B가 학장되고, 동시에 C를 기준으로 --C=B(+)는 안됨
        B가 확장될 수 없음(A(B(+) AND C=B(+)는 허용 안됨) --A=B(+), B=C(+) 이거는 가능 셋다 같아짐
      . 외부조인의 SELECT절에 사용되는 컬럼은 반드시 자료가 많은쪽 컬럼을 사용해야함
      . COUNT함수가 사용될 경우 '*' 대신 해당 테이블의 기본키를 기술하는 것이 안전함
      . 외부조인조건과 일반조건이 동시에 수행되면 외부조인 결과를 반환하지 못함=>서브쿼리나
        ANSI외부조인을 사용해야 함  --2020년 6월 구매한 모든 회원/ 서브쿼리 내부에서 일반조건 해결
        
 (ANSI 외부조인형식)
     SELECT 컬럼list
       FROM 테이블명1 [별칭1]
   LEFT|RIGHT FULL OUTER JOIN 테이블명2 [별칭2] ON(조인조건 [AND 일반조건])
   LEFT|RIGHT FULL OUTER JOIN 테이블명3 [별칭2] ON(조인조건 [AND 일반조건2])
                      :
     [WHERE 일반조건]
    . '테이블명1'과 '테이블명 2'는 반드시 직접 조인될 수 있어야함
    . '테이블명3'은 '테이블명1'과 '테이블명2'의 조인 결과와 조인
    . '일반조건1', '일반조건2'등은 해당 JOIN에 사용된 테이블과 관련된 조건만 기술
    . LEFT : FROM절쪽의 결과가 더 많은 종류를 가지고 있는 경우 사용
    . RIGHT : FROM절쪽의 결과가 더 적은 종류를 가지고 있는 경우 사용
    . FULL : 양쪽 모두 부족한 경우 사용
    . 'WERE 일반조건;' : 외부조인을 수행한 후 조건처리가 되기 때문에 보통 내부조인 결과로
      변환됨

 사용예)2020년 2월 모든 제품별 매입집계를 조회하시오
      Alias는 상품코드,상품명,매입수량합계,매입금액합계
**2020년 2월 매입정보
    SELECT COUNT(*)
      FROM BUYPROD
     WHERE BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201'))  
   -------------------------------------------------------------------------------   
    SELECT P.PROD_ID AS 상품코드,  --외부조인 할 때에는 더 큰곳에서의 컬럼을 가져온다.
           P.PROD_NAME AS 상품명,
           SUM(B.BUY_QTY) AS 매입수량합계,
           SUM(B.BUY_QTY*P.PROD_COST) AS 매입금액합계
      FROM BUYPROD B, PROD P
     WHERE B.BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201')) 
       AND P.PROD_ID=B.BUY_PROD(+)  --일반조인과 같이 써서 내부조인으로 바뀜. 안시나 서브쿼리로 한다.
     GROUP BY P.PROD_ID, P.PROD_NAME
     ORDER BY 1;
     
(서브쿼리 : 2020년 제품별 매입집계-제품코드, 매입수량, 매입금액)     
     SELECT B.BUY_PROD AS BID,
            SUM(B.BUY_QTY) AS SQTY,
            SUM(B.BUY_QTY*P.PROD_COST) AS SAMT
       FROM BUYPROD B, PROD P
      WHERE P.PROD_ID=B.BUY_PROD
        AND B.BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201')) 
      GROUP BY B.BUY_PROD

(메인쿼리)
    SELECT P.PROD_ID AS 상품코드,
           P.PROD_NAME AS 상품명,
           NVL(A.SQTY,0) AS 매입수량합계,
           NVL(A.SAMT,0) AS 매입금액합계
      FROM PROD P, 
           (SELECT B.BUY_PROD AS BID,
                  SUM(B.BUY_QTY) AS SQTY,
                  SUM(B.BUY_QTY*P.PROD_COST) AS SAMT
             FROM BUYPROD B, PROD P
            WHERE P.PROD_ID=B.BUY_PROD
              AND B.BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201'))
            GROUP BY B.BUY_PROD)A
     WHERE P.PROD_ID=A.BID(+)
     ORDER BY 1;
      
 (ANSI OUTER JOIN)
     SELECT P.PROD_ID AS 상품코드, 
           P.PROD_NAME AS 상품명,
           NVL(SUM(B.BUY_QTY),0) AS 매입수량합계,
           NVL(SUM(B.BUY_QTY*P.PROD_COST),0) AS 매입금액합계
      FROM BUYPROD B
     RIGHT OUTER JOIN PROD P ON(P.PROD_ID=B.BUY_PROD
           AND B.BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201')))
     GROUP BY P.PROD_ID, P.PROD_NAME
     ORDER BY 1;

---

4. SELF JOIN
 - 하나의 테이블에 2개 이상의 별칭을 부여하여 다른 테이블로 간주한 후 수행하는 조인

사용예)회원테이블에서 '오철희'회원의 마일리지보다 많은 마일리지를 보유한 회원들을 조회하시오.
      Alias는 회원번호,회원명,마일리지 --NON EQUI JOIN,  이혜나 있는 것 없는 것 별칭 따로 써서 조인
      SELECT B.MEM_ID AS 회원번호,
             B.MEM_NAME AS 회원명,
             B.MEM_MILEAGE AS 마일리지
       FROM MEMBER A, MEMBER B
      WHERE A.MEM_NAME='오철희' --일반조건
        AND B.MEM_MILEAGE>A.MEM_MILEAGE; --조인조건

사용예)회원테이블에서 20대 여성회원 중 마일리지가 많은 3명의 회원이 2020년 4월 ~ 6월 까지 
      구매한 집계를 조회하시오.
메인쿼리 : 20대 여성회원의 2020년 4~6 구매집계
    SELECT S. AS 회원번호,
           M.MEM_NAME AS 회원명,
           SUM(C.CART_QTY*D.PROD_PRICE) AS 구매금액집계
      FROM MEMBER M, CART C, PROD D,
           (     ) S
     WHERE S.회원번호=M.MEM_ID
       AND S.회원번호=C.CART_MEMBER
       AND C.CART_PROD=D.PROD_ID
       AND SUBSTR(C.CART_NO,1,6) BETWEEN '202004' AND '202006'
     GROUP BY S.회원번호,  M.MEM_NAME  
       
서브쿼리 : 20대 여성회원 > 마일리지가 많은 상위 3명 회원(서브쿼리 속 서브쿼리,오더바이 순서) 
    SELECT A.MEM_ID AS MID
      FROM (SELECT MEM_ID
              FROM MEMBER
             WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)=20--첫째자리를 잘라버리니 무조건 21
               AND SUBSTR(MEM_REGNO2,1,1) IN ('2','4')
             ORDER BY MEM_MILEAGE DESC) A
     WHERE ROWNUM<=3;

결합
    SELECT S.MID AS 회원번호,
           M.MEM_NAME AS 회원명,
           SUM(C.CART_QTY*D.PROD_PRICE) AS 구매금액집계
      FROM MEMBER M, CART C, PROD D,
           (SELECT A.MEM_ID AS MID
              FROM (SELECT MEM_ID
                      FROM MEMBER
                     WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)=20
                       AND SUBSTR(MEM_REGNO2,1,1) IN ('2','4')
                     ORDER BY MEM_MILEAGE DESC) A
             WHERE ROWNUM<=3) S
     WHERE S.MID=M.MEM_ID
       AND S.MID=C.CART_MEMBER
       AND C.CART_PROD=D.PROD_ID
       AND SUBSTR(C.CART_NO,1,6) BETWEEN '202004' AND '202006'
     GROUP BY S.MID,M.MEM_NAME;

---

3. DML 명령과 서브쿼리
 - 서브쿼리를 이용하여 데이터의 삽입, 변경, 삭제 수행

 1) 자료 삽입

  (사용형식)
   INSERT INTO 테이블명[(컬럼명,...)]
    서브쿼리                        --서브쿼리 괄호 생략은 INSERT, CREATE만
    . INSERT 문에 사용되는 서브쿼리는 '( )'와 VALUES절을 생략함 --VALUES절 대신 서브쿼리 
    .'(컬럼명,...)'이 사용된 경우 서브쿼리의 SELECT절의 컬럼의 갯수, 순서, 타입이 동일해야 함

사용예)재고수불테이블(REMAIN)에 상품테이블의 자료 중 상품코드, 적정재고를 이용하여
      모든 상품정보를 입력하시오
      [자료]
      REMAIN_YEAR에는 '2020'
      PROD_ID : 상품테이블의 PROD-ID
      REMAIN_J_00 : 상품테이블의 적정재고(PROD_PROPERSTOCK)
      REMAIN_I : 0
      REMAIN_O : 0
      REMAIN_J_99 : 상품테이블의 적정재고(PROD_PROPERSTOCK)
      REMAIN_DATE : '2019/12/31'
      
    INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,REMAIN_I,REMAIN_O,
                        REMAIN_J_99,REMAIN_DATE)
      SELECT '2020',PROD_ID,PROD_PROPERSTOCK,0,0,PROD_PROPERSTOCK,TO_DATE('2019/12/31')
        FROM PROD;

 

2) 자료 갱신

  (사용형식)
   UPDATE 테이블명 [별칭]
      SET (컬럼명,...)=(서브쿼리)
   [WHHERE 조건];
   
   .SET 절에 기술된 컬럼의 갯수,순서,타입과 서브쿼리의 SELECT문의 갯수,순서,타입은 일치해야함
   .'WHERE 조건'이 사용되지 않으면 모든 자료가 갱신 대상이 됨(주의를 요함)

 사용예)매입테이블에서 2020년 1월 제품별 매입수량을 구한뒤 재고 수불테이블에
       매입수량을 반영하시오.
       
(서브쿼리 : 2020년 1월 제품별 매입수량)
    SELECT BUY_PROD,
           SUM(BUY_QTY) AS SQTY
      FROM BUYPROD
     WHERE BUY_DATE BETWEEN TO_DATE('20200101') AND TO_DATE('20200131')
     GROUP BY BUY_PROD;
 
(메인쿼리 : UPDATE문)
    UPDATE REMAIN R
       SET (R.REMAIN_I,R.REMAIN_J_99,R.REMAIN_DATE)=
           (SELECT R.REMAIN_I+A.SQTY,R.REMAIN_J_99+A.SQTY,TO_DATE('20200331')
              FROM (SELECT BUY_PROD,
                           SUM(BUY_QTY) AS SQTY
                      FROM BUYPROD
                     WHERE BUY_DATE BETWEEN TO_DATE('20200201') 
                           AND TO_DATE('20200331')
                     GROUP BY BUY_PROD)A
             WHERE A.BUY_PROD=R.PROD_ID)
     WHERE R.PROD_ID IN(SELECT DISTINCT BUY_PROD
                        FROM BUYPROD
                       WHERE BUY_DATE BETWEEN TO_DATE('20200201') 
                             AND TO_DATE('20200331'));

 

 

sub query

: 하나의 SQL 문에 포함되어 있는 또 다른 SQL 문.  (성능은 좋지 않으나 사용방법이 쉽다.)

. 서브쿼리를 괄호로 감싸서 사용한다.
. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.
. 서브쿼리에서는 ORDER BY 를 사용하지 못한다.

서브쿼리가 사용 가능한 곳
1. SELECT 절
2. FROM 절
3. WHERE 절
4. HAVING 절
5. ORDER BY 절
6. INSERT 문의 VALUES 절
7. UPDATE 문의 SET 절

https://mozi.tistory.com/233

[DATABASE] 서브쿼리란? 서브쿼리 사용해보기