데이터베이스
Oracle(2022.02.25)2-집합 연산자
AIN99
2022. 2. 25. 17:43
728x90
집합연산자
-합집합(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;
(집합연산자 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;
(집합연산자 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%'
--서브쿼리를 사용한 결합 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;728x90