Oracle(2022.02.25)2-집합 연산자

집합연산자

 -합집합(UNION, UNION ALL),교집합(INTERSECT), 차집합(MINUS)결과를 반환하는 연산자
 -집합연산자의 대상은  SELECT문의 결과이다.
 -조인 등의 복잡한 연산을 줄일 수 있다.
 -모든 SELECT문의 컬럼의 수, 데이터타입, 순서가 일치해야함 
 -첫 SELECT문에 적용된 컬럼타입,이름이 기준이 됨 
 -CLOB,BLOB,BFILE 타입은 사용 불가
 -ORDER BY 절은 맨 마지막 SELECT문에만 사용 가능
 
 1.UNION, UNION ALL (외부조인에 해당)
 -합집합의 결과를 교집합 부분의 중복없이(UNION), 중복하여(UNION ALL) 반환한다.

   .첫번째 SELECT문의 결과와 나머지 SELECT문의 결과가 결합하여 반환

  (사용형식)
  SELECT 컬럼 list1
    FROM 테이블명
  [WHERE 조건]
  UNION|UNION ALL|INTERSECT|MINUS
  SELECT 컬럼 list2
    FROM 테이블명
  [WHERE 조건]
  UNION|UNION ALL|INTERSECT|MINUS
       :
  SELECT 컬럼 listn
    FROM 테이블명
  [WHERE 조건]  
  [ORDER BY 컬럼명];

사용예) 장바구니테이블에서 2005년 5월과 7월에 구매한 모든 회원을 조회하시오.
         Alias는 회원번호, 회원명, 주소, 마일리지이다.

 (집합연산자 사용X)
        SELECT DISTINCT A.CART_MEMBER AS 회원번호, 
               B.MEM_NAME AS 회원명, 
               B.MEM_ADD1||' '||B.MEM_ADD2 AS 주소,
               B.MEM_MILEAGE AS 마일리지
          FROM CART A, MEMBER B
         WHERE A.CART_MEMBER=B.MEM_ID
           AND SUBSTR(CART_NO,1,6) IN('200505','200507')
        ORDER BY 1;

   (집합연산자 UNION 사용O) 20명 11명 합치니까 23명 8명 겹침 
         SELECT DISTINCT A.CART_MEMBER AS 회원번호, 
               B.MEM_NAME AS 회원명, 
               B.MEM_ADD1||' '||B.MEM_ADD2 AS 주소,
               B.MEM_MILEAGE AS 마일리지
          FROM CART A, MEMBER B
         WHERE A.CART_MEMBER=B.MEM_ID
           AND SUBSTR(CART_NO,1,6) ='200505'
       UNION    
            SELECT DISTINCT A.CART_MEMBER ,
               B.MEM_NAME AS, 
               B.MEM_ADD1||' '||B.MEM_ADD2 ,
               B.MEM_MILEAGE 
          FROM CART A, MEMBER B
         WHERE A.CART_MEMBER=B.MEM_ID
           AND SUBSTR(CART_NO,1,6) ='200507'
         ORDER BY 1;

200505

200507

 

 (집합연산자 UNION ALL 사용O) 31건 반환 8명 중복된거 포함 
      SELECT DISTINCT A.CART_MEMBER AS 회원번호, 
               B.MEM_NAME AS 회원명, 
               B.MEM_ADD1||' '||B.MEM_ADD2 AS 주소,
               B.MEM_MILEAGE AS 마일리지
          FROM CART A, MEMBER B
         WHERE A.CART_MEMBER=B.MEM_ID
           AND SUBSTR(CART_NO,1,6) ='200505'
       UNION ALL    
            SELECT DISTINCT A.CART_MEMBER ,
               B.MEM_NAME AS, 
               B.MEM_ADD1||' '||B.MEM_ADD2 ,
               B.MEM_MILEAGE 
          FROM CART A, MEMBER B
         WHERE A.CART_MEMBER=B.MEM_ID
           AND SUBSTR(CART_NO,1,6) ='200507'
         ORDER BY 1;

---

사용예) 2005년 2월과 6월 매입된 상품을 모두 조회하시오.
       Alias는 상품코드, 상품명

     

(집합연산자 사용X)
       SELECT DISTINCT A.BUY_PROD AS 상품코드,
              B.PROD_NAME AS 상품명
       FROM BUYPROD A,PROD B
       WHERE A.BUY_DATE BETWEEN TO_DATE('20050201') 
             AND LAST_DAY(TO_DATE('20050201'))
             AND A.BUY_PROD=B.PROD_ID
             
        SELECT DISTINCT A.BUY_PROD AS 상품코드,
              B.PROD_NAME AS 상품명
       FROM BUYPROD A,PROD B
       WHERE A.BUY_DATE BETWEEN TO_DATE('20050601') 
             AND LAST_DAY(TO_DATE('20050601'))
             AND A.BUY_PROD=B.PROD_ID;

2월 데이터 일부

5월 데이터일부

(집합연산자 UNION 사용O) 41가지중 6가지 중복 제외하고 출력
      SELECT DISTINCT A.BUY_PROD AS 상품코드,
              B.PROD_NAME AS 상품명
       FROM BUYPROD A,PROD B
       WHERE A.BUY_DATE BETWEEN TO_DATE('20050201') 
             AND LAST_DAY(TO_DATE('20050201'))
             AND A.BUY_PROD=B.PROD_ID
       UNION      
        SELECT DISTINCT A.BUY_PROD AS 상품코드,
              B.PROD_NAME AS 상품명
       FROM BUYPROD A,PROD B
       WHERE A.BUY_DATE BETWEEN TO_DATE('20050601') 
             AND LAST_DAY(TO_DATE('20050601'))
             AND A.BUY_PROD=B.PROD_ID
       ORDER BY 1;

 (집합연산자 INTERSECT 사용O) 6가지가 중복된 갯수(2월도 6월도)
     SELECT DISTINCT A.BUY_PROD AS 상품코드,
              B.PROD_NAME AS 상품명
       FROM BUYPROD A,PROD B
       WHERE A.BUY_DATE BETWEEN TO_DATE('20050201') 
             AND LAST_DAY(TO_DATE('20050201'))
             AND A.BUY_PROD=B.PROD_ID
       INTERSECT      
        SELECT DISTINCT A.BUY_PROD AS 상품코드,
              B.PROD_NAME AS 상품명
       FROM BUYPROD A,PROD B
       WHERE A.BUY_DATE BETWEEN TO_DATE('20050601') 
             AND LAST_DAY(TO_DATE('20050601'))
             AND A.BUY_PROD=B.PROD_ID
       ORDER BY 1;

---

2.INTERSECT
 -교집합의 결과를 반환

사용예) 사원테이블과 직무변동테이블(JOB_HISTORY)에서 사원번호와
       직책코드가 같은 사원을 조회하시오   

(집합연산자 사용 X)
       SELECT  A.EMPLOYEE_ID AS 사원번호,
               A.EMP_NAME AS 사원명,
               A.JOB_ID AS 직책코드
         FROM  HR.EMPLOYEES A
        WHERE (A.EMPLOYEE_ID,A.JOB_ID )=ANY (SELECT EMPLOYEE_ID,JOB_ID
                                            FROM HR.JOB_HISTORY)
                                            
 (또 다른 코드)
 
       SELECT  A.EMPLOYEE_ID AS 사원번호,
               A.EMP_NAME AS 사원명,
               B.JOB_ID AS 직책코드
             FROM  HR.EMPLOYEES A,( SELECT EMPLOYEE_ID,JOB_ID
                                      FROM HR.JOB_HISTORY)B
            WHERE A.JOB_ID=B.JOB_ID
              AND A.EMPLOYEE_ID=B.EMPLOYEE_ID;

      
쌍으로 비교가 되어야 하는데 앞이 사원번호, 직업번호 순서로 양쪽에서나온 행을 비교해야한다. 하지만 오른쪽은 결과가 10개이므로 관계연산자 '='를 사용X 다중연산자를 사용해야한다.                                                   
 즉'=': 1명분의 자료 VS 10줄이므로 사용이 안되고 ANY를 써야한다. (확인해보니 IN,=SOME다 가능한듯)
        

 (집합연산자를 사용O)
        SELECT  A.EMPLOYEE_ID AS 사원번호,
                A.JOB_ID AS 직책코드
         FROM  HR.EMPLOYEES A 
     INTERSECT    
         SELECT EMPLOYEE_ID,
                JOB_ID
          FROM HR.JOB_HISTORY;

 (오류 주의- 위에는 3개 밑에는 2개 개수가 맞아야한다.)
        SELECT  A.EMPLOYEE_ID AS 사원번호,
                A.EMP_NAME AS 사원명,
                A.JOB_ID AS 직책코드
         FROM  HR.EMPLOYEES A 
     INTERSECT    
         SELECT EMPLOYEE_ID,
                JOB_ID
          FROM HR.JOB_HISTORY;

---

사용예) 2005년 4월과 5월 6월에 공통으로 판매된 상품을 모두 조회하시오.
       Alias는 상품코드, 상품명
 

사용예) 2005년 4월과 5월 6월에 공통으로 판매된 상품을 모두 조회하시오.
       Alias는 상품코드, 상품명
    (EXISTS 연산자 사용)
    --2005년 4월에 판매된 상품코드와 상품명 조회
    SELECT DISTINCT A.CART_PROD AS 상품코드,
           B.PROD_NAME AS 상품명
      FROM CART A, PROD B
      WHERE A.CART_PROD=B.PROD_ID
        AND A.CART_NO LIKE '200504%'
    --2005년 5월에 판매된 상품코드와 상품명 조회
     SELECT DISTINCT CART_PROD AS BID
      FROM CART 
     WHERE CART_NO LIKE '200505%'
     --2005년 6월에 판매된 상품코드와 상품명 조회
     SELECT DISTINCT CART_PROD AS CID     
      FROM CART
     WHERE CART_NO LIKE '200506%'

4월

5월

6월

 --서브쿼리를 사용한 결합 2005년 6월 판매된 상품코드가 5월 상품코드같은거 참 2005년 5월 상품코드가 2005년 4월상품코드랑 같고 그러면 출력해
     SELECT DISTINCT A.CART_PROD AS 상품코드,
           B.PROD_NAME AS 상품명
      FROM CART A, PROD B
      WHERE A.CART_PROD=B.PROD_ID
        AND A.CART_NO LIKE '200504%'
        AND EXISTS ( SELECT 1
                       FROM CART C
                      WHERE CART_NO LIKE '200505%'
                        AND C.CART_PROD=A.CART_PROD
                        AND EXISTS(SELECT 1    
                                     FROM CART D
                                    WHERE CART_NO LIKE '200506%'
                                      AND D.CART_PROD=C.CART_PROD))
      ORDER BY 1;

(집합연산자 INTERSECT 사용)
     SELECT DISTINCT A.CART_PROD AS 상품코드,
           B.PROD_NAME AS 상품명
      FROM CART A, PROD B
      WHERE A.CART_PROD=B.PROD_ID
        AND A.CART_NO LIKE '200504%'
 INTERSECT       
     SELECT DISTINCT A.CART_PROD,
           B.PROD_NAME 
      FROM CART A, PROD B
      WHERE A.CART_PROD=B.PROD_ID
        AND A.CART_NO LIKE '200505%'
 INTERSECT       
     SELECT DISTINCT A.CART_PROD,
           B.PROD_NAME 
      FROM CART A, PROD B
      WHERE A.CART_PROD=B.PROD_ID
        AND A.CART_NO LIKE '200506%'
      ORDER BY 1;

 (결합(서브쿼리+집합연산자))
    SELECT DISTINCT A.CART_PROD AS 상품코드,
           B.PROD_NAME AS 상품명
      FROM CART A, PROD B
      WHERE A.CART_PROD=B.PROD_ID
        AND A.CART_NO LIKE '200504%'
        AND A.CART_PROD IN ( SELECT DISTINCT CART_PROD 
                              FROM CART 
                            WHERE CART_NO LIKE '200505%'
                      INTERSECT
                            SELECT DISTINCT CART_PROD
                              FROM CART 
                             WHERE CART_NO LIKE '200506%')
      ORDER BY 1;

---

3. MINUS(차집합)
 - 복수개의 쿼리결과에 대하여 차집합의 결과 반환

사용예)매입테이블에서 2005년 1월과 4월의 매입을 비교하여 1월에만 매입된 상품정보를
      출력 하시오(단, 동일 상품의 메입가격이 1월과 4월이 다른경우도 있음)
      Alias는 상품코드,매입가,매입수량합계

--1월 상품별 매입수량 1월 결과 밖에 안나옴 BUY_PROD,SUM(BUY_QTY)이 같은것을 빼야하는데 1월39,4월26 SUM개수 달라서 하나도 안뺴짐 오류!
      SELECT BUY_PROD,
             SUM(BUY_QTY)
        FROM BUYPROD
       WHERE BUY_DATE BETWEEN '20050101' AND '20050131'
       GROUP BY BUY_PROD
 MINUS      
       SELECT BUY_PROD,
             SUM(BUY_QTY)
        FROM BUYPROD
       WHERE BUY_DATE BETWEEN '20050401' AND '20050430'
       GROUP BY BUY_PROD
       ORDER BY BUY_PROD;