집합 연산자

집합 연산 이론

집합 연산자

UNION연산자 : 합집합

: 여러 데이터 집합을 결합할 수 있음

• UNION : 결합된 집합을 정렬하고 중복을 제거함
• UNION ALL : 그렇지 않음

SELECT 'CUST' TYP, C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
UNION ALL 
SELECT 'ACTR' TYP, A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A;

SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME LIKE 'D%'
UNION ALL
SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME LIKE 'D%';

-- 중복 제거 UNION
SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME 'D%'
UNION 
SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME 'D%';

INTERSECT연산자(Mysql지원 X) : 교집합

SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'D%' AND C.LAST_NAME LIKE 'T%'
INTERSECT 
SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'D%' AND A.LAST_NAME LIKE 'T%';

SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME LIKE 'D%'
INTERSECT 
SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME LIKE 'D%';

EXCEPT연산자(Mysql지원 X) : 차집합

* Oracle은 EXCEPT연산자 대신 MINUS연산자 사용

SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME LIKE 'D%'
EXCEPT
SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME LIKE 'D%';

집합 연산 규칙

복합 쿼리 정렬

• 복합 쿼리 : 여러개의 독립적인 쿼리로 구성된 쿼리
• 쿼리 마지막에 ORDER BY절을 사용(첫 번째 쿼리에서 사용된 컬럼명을 사용해야 함)

SELECT A.FIRST_NAME FNAME, A.LAST_NAME LNAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME LIKE 'D%'
UNION ALL
SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME LIKE 'D%'
ORDER BY LNAME, FNAME;

집합 연산 순서

SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME LIKE 'D%'
UNION ALL
SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'M%' AND A.LAST_NAME LIKE 'T%'
UNION 
SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME LIKE 'D%';

SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'J%' AND A.LAST_NAME LIKE 'D%'
UNION
SELECT A.FIRST_NAME, A.LAST_NAME
FROM ACTOR A
WHERE A.FIRST_NAME LIKE 'M%' AND A.LAST_NAME LIKE 'T%'
UNION ALL
SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.FIRST_NAME LIKE 'J%' AND C.LAST_NAME LIKE 'D%';

-- UNION과 UNION ALL의 위치가 바뀜에 따라 결과가 바뀜

• 3개 이상의 쿼리를 포함하는 복합쿼리는 보통 위에서 아래로 순서대로 실행됨
  • ANSI 문법에서는 INTERSECT연산자가 다른 집합 연산자 보다 우선순위를 가짐
  • 괄호를 사용해 순서를 지정할 수도 있음

연습문제

-- 6-1
A UNION B : L M N O P Q R S T
A UNION ALL B : L M N O P P Q R S T
A INTERSECT B : P
A EXCEPT B : L M N O

-- 6-2, 6-3
SELECT A.FIRST_NAME AS FNAME, A.LAST_NAME AS LNAME
FROM ACTOR A
WHERE A.LAST_NAME LIKE 'L%'
UNION
SELECT C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.LAST_NAME LIKE 'L%'
ORDER BY LNAME