집합연산자
- SELECT문을 실행하면 해당 조건에 맞는 데이터가 조회되며 이 데이터를 데이터집합(SET)이라 함
- 데이터 집합을 대상으로 수행하는 연산자를 집합연산자라고 함
- UNION, UNION ALL, INTERSECT, MINUS 등이 제공 됨
- 조인연산을 대신할 수도 있음
- 집합연산자로 연결되는 각 SELECT문의 컬럼의 갯수와 데이터 타입은 일치해야함
- ORDER BY 절은 마지막 SELECT문에서만 사용 가능
- BLOB,CLOB, BFILE 타입의 컬럼을 포함할 수 없다.
- UNION,INTERSECT, MINUS에서 LONG 타입의 컬럼을 포함할 수 없음.
1. UNION
. 합집합의 결과를 반환
. 각 데이터 집합의 원소들이 중복하지 않게 무두 포함된 결과를 반환
사용예) 회원테이블에서 직업이 주부인 회원과 마일리지가 3000이상인 모든회원의
회원번호,회원명,성별,직업,마일리지를 조회하시오
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('2','4') THEN
'여성회원'
ELSE
'남성회원'
END AS 성별,
MEM_JOB AS 직업,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_JOB='주부'
UNION
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('2','4') THEN
'여성회원'
ELSE
'남성회원'
END AS 성별,
MEM_JOB,
MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE>=3000
ORDER BY 5 DESC
사용예) 장바구니 테이블에서 2020년 6월과 7월에 판매된 상품정보를 조회하시오
Alias는 상품코드, 상품명,거래처명,매입가,매출가
--1) 6월 18건
SELECT A.CART_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
C.BUYER_NAME AS 거래처명,
B.PROD_COST AS 매입가,
B.PROD_PRICE AS 매출가
FROM CART A, PROD B, BUYER C
WHERE A.CART_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.CART_NO LIKE '202006%'
UNION
SELECT A.CART_PROD,
B.PROD_NAME,
C.BUYER_NAME,
B.PROD_COST,
B.PROD_PRICE
FROM CART A, PROD B, BUYER C
WHERE A.CART_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.CART_NO LIKE '202007%'
--2)7월 29건
-- 전체 33개
2. UNION ALL
. 합집합의 결과를 반환 (중복 포함)
. 각 데이터 집합의 원소들을 모두 포함된 결과를 반환
- 같은 예제
사용예) 회원테이블에서 직업이 주부인 회원과 마일리지가 3000이상인 모든회원의
회원번호,회원명,성별,직업,마일리지를 조회하시오
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('2','4') THEN
'여성회원'
ELSE
'남성회원'
END AS 성별,
MEM_JOB AS 직업,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_JOB='주부'
UNION ALL
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('2','4') THEN
'여성회원'
ELSE
'남성회원'
END AS 성별,
MEM_JOB,
MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE>=3000
ORDER BY 5 DESC사용예) 장바구니 테이블에서 2020년 6월과 7월에 판매된 상품정보를 조회하시오
Alias는 상품코드, 상품명,거래처명,매입가,매출가
--1) 6월 18건
SELECT A.CART_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
C.BUYER_NAME AS 거래처명,
B.PROD_COST AS 매입가,
B.PROD_PRICE AS 매출가
FROM CART A, PROD B, BUYER C
WHERE A.CART_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.CART_NO LIKE '202006%'
UNION ALL
SELECT A.CART_PROD,
B.PROD_NAME,
C.BUYER_NAME,
B.PROD_COST,
B.PROD_PRICE
FROM CART A, PROD B, BUYER C
WHERE A.CART_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.CART_NO LIKE '202007%'
--2)7월 29건
-- 전체 47개3. INTERSECT
. 교집합의 결과를 반환
사용예) 장바구니 테이블에서 2020년 6월과 7월에 동시에 판매된 상품정보를 조회하시오
Alias는 상품코드, 상품명,거래처명,매입가,매출가
사용예) 회원테이블에서 직업이 주부인 회원과 마일리지가 3000이상인 모든회원의
회원번호,회원명,성별,직업,마일리지를 조회하시오
사용예) 2020년 4월 매입과 매출이 동시에 발생된 상품을 조회하시오
Alias는 상품코드,상품명,매입수량,매출수량
SELECT A.BUY_PROD AS 상품코드,
B.PROD_NAME AS 상품명
-- SUM(A.BUY_QTY) AS 매입수량
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD=B.PROD_ID
AND A.BUY_DATE BETWEEN TO_DATE('20200401') AND TO_DATE('20200430')
-- GROUP BY A.BUY_PROD, B.PROD_NAME
INTERSECT
SELECT A.CART_PROD,
B.PROD_NAME
-- SUM(A.CART_QTY) AS 매출수량
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '202004%'
-- GROUP BY A.CART_PROD, B.PROD_NAME
ORDER BY 1;-- INTERSECT 25건
-- 매입이 발생된 것과 동시에 매출된것 25건 // 매입매출 수량까지도 같아야 1건이 나온다.
4. MINUS
. 차집합의 결과를 반환
. MINUS 연산자는 사용되는 데이터 SET의 위치에 따라 결과가 달라짐. 즉, A MINUS B <> B MINUS A
사용예)2020년 4월 매입없이 매출만 발생된 상품의 상품번호,상품명을 조회하시오
SELECT DISTINCT B.PROD_ID AS 상품번호,
B.PROD_NAME AS 상품명
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '202004%'
MINUS -- 72 - 26 = 46 에서 위에 하나 포함 안된게 1개있어서 72 - 25 = 47
SELECT DISTINCT A.BUY_PROD AS 상품번호,
B.PROD_NAME AS 상품명
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD=B.PROD_ID
AND A.BUY_DATE BETWEEN TO_DATE('20200401') AND TO_DATE('20200430')
-- 결과 47개
-- 매출 72개 / 매입 26개 // 매입 26개 중에 매출이 발생되지 않은 품목이 1개 있음------------ 위치 바꿔서 ----------
SELECT DISTINCT A.BUY_PROD AS 상품번호,
B.PROD_NAME AS 상품명
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD=B.PROD_ID
AND A.BUY_DATE BETWEEN TO_DATE('20200401') AND TO_DATE('20200430')
MINUS
SELECT DISTINCT B.PROD_ID AS 상품번호,
B.PROD_NAME AS 상품명
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '202004%'
-- 결과가 1개 나온다
사용예) 2020년 2020년 6월과 7월 중 6월에만 판매된 상품정보를 조회하시오
Alias는 상품코드,상품명,매입수량,매출수량
SELECT A.CART_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
C.BUYER_NAME AS 거래처명,
B.PROD_COST AS 매입가,
B.PROD_PRICE AS 매출가
FROM CART A, PROD B, BUYER C
WHERE A.CART_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.CART_NO LIKE '202006%'
MINUS
SELECT A.CART_PROD,
B.PROD_NAME,
C.BUYER_NAME,
B.PROD_COST,
B.PROD_PRICE
FROM CART A, PROD B, BUYER C
WHERE A.CART_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.CART_NO LIKE '202007%'
-- 13개가 6월달에만 발생되었다.
----------- MINUS 안쓰려면...
(NOT EXISTS 연산자 사용)
(NOT EXISTS 연산자 사용)
SELECT A.DID AS 상품코드,
B.PROD_NAME AS 상품명,
C.BUYER_NAME AS 거래처명,
B.PROD_COST AS 매입가,
B.PROD_PRICE AS 매출가
FROM ( SELECT DISTINCT D.CART_PROD AS DID
FROM CART D
WHERE NOT EXISTS(SELECT 1
FROM CART E
WHERE CART_NO LIKE '202007%' --7월에 판매되어지고
AND D.CART_PROD=E.CART_PROD)
AND D.CART_NO LIKE '202006%') A,
PROD B, BUYER C
WHERE A.DID=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID;
-- NOT EXISTS : 2020년 7월에 판매된것 이외의 자료를 구하고 그중에 6월에 구매된것