[Oracle SQL] 집합연산자

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

Oracle SQL

목록 보기

20/31

집합 연산자 -- 의외로 많이씀

SELECT 문에 의한 결과를 하나의 집합(SET)이라고 함

이 집합을 대상으로한 연산을 담당하는 연산자

UNION, UNION ALL, INTERSECT, MINUS 가 제공됨

집합연산에 사용되는 모든 SELECT 문의 컬럼의 갯수와 타입 및 순서가 일치해야함

CLOB, BLOB, BFILE 타입의 컬럼은 연산에 참여 불가능

ORDER BY 절은 맨 마지막 SELECT문에만 사용 가능

출력의 기준이 되는 컬럼은 첫 번째 SELECT 문이다.

2)컬럼

    컬럼명         데이터타입

  COUNTRY         VARCHAR2(10)
  SEQ_NO          NUMBER(4)
  GOODS           VARCHAR2(80)

3) 자료

  COUNTRY     SEQ_NO     GOODS

   한국          1     원유제외 석유류
   ""           2     자동차
   ""           3     전자집적회로
   ""           4     선박
   ""           5     LCD
   ""           6     자동차 부품
   ""           7     휴대전화
   ""           8     환식탄화수소
   ""           9     무선송신기 디스플레이 부품
   ""           10    철 또는 비철합금강
   
   일본          1     자동차
   ""           2     자동차 부품
   ""           3     전자집적회로
   ""           4     선박
   ""           5     반도체 웨이퍼
   ""           6     화물차
   ""           7     원유제외 석유류
   ""           8     건설기계
   ""           9     다이오드, 트렌지스터
   ""           10    기계류

CREATE TABLE EXP_GOODS_ASIA(
   COUNTRY VARCHAR2(10),
   SEQ_NO NUMBER(4),
   GOODS VARCHAR(80)) ;
   
   INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',1,'원유제외 석유류');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',2,'자동차');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',3,'전자집적회로');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',4,'선박');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',5,'LCD');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',6,'자동차 부품');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',7,'휴대전화');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',8,'환식탄화수소');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',9,'무선송신기 디스플레이 부품');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('한국',10,'철 또는 비철합금강');
     
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',1,'자동차');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',2,'자동차 부품');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',3,'전자집적회로');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',4,'선박');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',5,'반도체 웨이퍼');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',6,'화물차');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',7,'원유제외 석유류');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',8,'건설기계');
      INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',9,'다이오드, 트렌지스터'); 
     INSERT INTO EXP_GOODS_ASIA
     VALUES('일본',10,'기계류');
     
     SELECT * FROM EXP_GOODS_ASIA;

1) UNION

합집합의 결과를 반환

중복 배제

사용예) 한국과 일본의 수출품목을 조회하시오

(한국의 수출품 순위)
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='한국'
       ORDER BY SEQ_NO;
       
     (일본의 수출품 순위)
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='일본'
       ORDER BY SEQ_NO;
       
     (결합)
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='한국'
      UNION
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='일본';

사용예) 2005년 6월과 7월에 판매된 상품 판매현황을 조회하시오

(집합연산자를 사용하지 않고)
      SELECT A.CART_PROD AS 상품코드, 
             B.PROD_NAME AS 상품명, 
             SUM(A.CART_QTY) AS 수량, 
             SUM(A.CART_QTY*B.PROD_PRICE) AS 금액
        FROM CART A, PROD B
        WHERE A.CART_PROD= B.PROD_ID 
          AND SUBSTR(A.CART_NO,1,6) BETWEEN '200506' AND '200507'
        GROUP BY  A.CART_PROD, B.PROD_NAME
        ORDER BY 1;
        
 (집합연산자 사용: 2005년 4월과 2005년 7월의 조회를 별도의  SQL문으로 작성)
      SELECT DISTINCT A.CART_PROD AS 상품코드, 
             B.PROD_NAME AS 상품명 
        FROM CART A, PROD B
        WHERE A.CART_PROD = B.PROD_ID 
          AND SUBSTR(A.CART_NO,1,6) = '200506'
     UNION
       SELECT DISTINCT A.CART_PROD AS 상품코드, 
             B.PROD_NAME AS 상품명 
        FROM CART A, PROD B
        WHERE A.CART_PROD = B.PROD_ID 
          AND SUBSTR(A.CART_NO,1,6) = '200507'

2) UNION ALL

합집합의 결과를 반환

중복 허용

사용예) 한국과 일본의 수출품목을 중복을 허용하여 조회하시오

(한국의 수출품 순위)
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='한국'
       ORDER BY SEQ_NO;
       
     (일본의 수출품 순위)
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='일본'
       ORDER BY SEQ_NO;
       
     (결합)
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='한국'
      UNION ALL
      SELECT GOODS
        FROM EXP_GOODS_ASIA
       WHERE COUNTRY='일본'
       ORDER BY 1;

사용예) 2005년 6월과 7월에 판매된 상품현황을 조회하시오

     SELECT DISTINCT A.CART_PROD AS 상품코드, 
            B.PROD_NAME AS 상품명 
       FROM CART A, PROD B
       WHERE A.CART_PROD = B.PROD_ID 
         AND SUBSTR(A.CART_NO,1,6) = '200506'
    UNION ALL
      SELECT DISTINCT A.CART_PROD AS 상품코드, 
            B.PROD_NAME AS 상품명 
       FROM CART A, PROD B
       WHERE A.CART_PROD = B.PROD_ID 
         AND SUBSTR(A.CART_NO,1,6) = '200507'
         ORDER BY 1;

3) INTERSECT

교집합의 결과를 반환

사용예) 한국과 일본의 수출상품 중 같은 품목을 조회하시오

(집합 연산자 사용하지 않은 경우 : 서브쿼리)
     SELECT A.GOODS AS 품목
       FROM( SELECT GOODS 
               FROM EXP_GOODS_ASIA
              WHERE COUNTRY = '한국')A,
              (SELECT GOODS 
                 FROM EXP_GOODS_ASIA
                WHERE COUNTRY = '일본')B
      WHERE A.GOODS=B.GOODS ;
          
     (집합 연산자 사용)
           SELECT GOODS 
             FROM EXP_GOODS_ASIA
            WHERE COUNTRY = '한국'
              INTERSECT 
           SELECT GOODS 
             FROM EXP_GOODS_ASIA
            WHERE COUNTRY = '일본';

사용예) 2005년 1월, 3월, 6월 3개월 모두 매입된 상품정보를 조회하시오
Alias는 상품코드, 상품명

  --   (1월) 39개
       SELECT DISTINCT A.BUY_PROD AS 상품코드, 
                       B.PROD_NAME AS 상품명
         FROM BUYPROD A, PROD B
        WHERE A.BUY_PROD=B.PROD_ID
          AND BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050131')
           INTERSECT 
     --  (3월) 6개
       SELECT DISTINCT A.BUY_PROD AS 상품코드, 
                       B.PROD_NAME AS 상품명
         FROM BUYPROD A, PROD B
        WHERE A.BUY_PROD=B.PROD_ID
          AND BUY_DATE BETWEEN TO_DATE('20050301') AND TO_DATE('20050331')
           INTERSECT 
    --   (6월) 12개
       SELECT DISTINCT A.BUY_PROD AS 상품코드, 
                       B.PROD_NAME AS 상품명
         FROM BUYPROD A, PROD B
        WHERE A.BUY_PROD=B.PROD_ID
          AND BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630')
          ORDER BY 1;

4) MINUS

차집합의 결과를 반환

사용예) 한국과 일본 두 나라의 수출 품목 중 한국만 수출하는 품목을 조회하시오

  (집합연산자를 사용하지 않은 경우)
    SELECT GOODS
      FROM EXP_GOODS_ASIA
     WHERE COUNTRY='한국'
      AND  GOODS NOT IN(SELECT GOODS
                          FROM EXP_GOODS_ASIA
                         WHERE COUNTRY = '일본');
                         
   (집합연산자를 사용한 경우)
    SELECT GOODS
      FROM EXP_GOODS_ASIA
     WHERE COUNTRY='한국'
      MINUS
       SELECT GOODS
      FROM EXP_GOODS_ASIA
     WHERE COUNTRY='일본';

사용예) 2005년 5월과 7월 판매된 상품 중 5월에만 판매된 상품을 조회하시오

 (집합연산자를 사용한경우)
      SELECT DISTINCT A.CART_PROD,
             B.PROD_NAME
        FROM CART A, PROD B
      WHERE CART_NO LIKE '200505%'
      AND A.CART_PROD = B.PROD_ID
         MINUS
               SELECT DISTINCT A.CART_PROD,
             B.PROD_NAME
        FROM CART A, PROD B
      WHERE CART_NO LIKE '200507%'
      AND A.CART_PROD = B.PROD_ID;
      
      (집합연산자를 사용하지 않은 경우-서브쿼리 사용)
      SELECT DISTINCT A.CART_PROD,
             B.PROD_NAME
        FROM CART A, PROD B
      WHERE A.CART_NO LIKE '200505%'
        AND A.CART_PROD=B.PROD_ID
        AND NOT EXISTS (SELECT 1
                          FROM CART C
                         WHERE A.CART_PROD=C.CART_PROD
                           AND C.CART_NO LIKE '200507%');

고동이의 IT

삐약..뺙뺙

이전 포스트

[Oracle SQL] 외부조인

다음 포스트

[Oracle SQL] 집합연산자

Oracle SQL

1) UNION

2) UNION ALL

3) INTERSECT

4) MINUS

[Oracle SQL] 외부조인

[Oracle SQL] 문자열데이터타입

0개의 댓글