집합 연산자 -- 의외로 많이씀
- 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%');