[MSSQL] 집합 연산자 (UNION, INTERSECT, EXCEPT) 합집합, 교집합, 차집합

합집합

집합 이론에서 두 집합 간의 모든 요소를 포함하는 집합이다.  (합집합의 개념)
T-SQL에서 UNION 연산자는 두 입력 쿼리의 결과를 하나로 결합한다. 두 가지 형태의 UNION 연산자를 지원하는데, UNION ALL과 UNION(암시적으로 DISTINCT 처리) 이다. 

 

UNION ALL

UNION ALL 멀티 셋 연산자는 두 입력 쿼리로부터 만들어지는 어떠한 행이라도 모두 결과에 포함 시켜 반환한다. 이 때 행을 비교하거나 중복을 제거하는 등의 작업을 수행하지 않는다.
Query 1이 m개 행을 반환하고 Query 2가 n개 행을 반환한다면 결과 쿼리는 m+n개의 행을 반환하게 된다.

UNION

UNION 집합 연산자는 두 쿼리의 결과를 결합한 다음 중복을 제거한다. 하나의 행이 양쪽 입력 집합에 모두 포함되어 있다면, 연산을 수행한 결과에는 단 한 번만 나타나게 된다. 다시 말해, 결과는 집합이지 멀티 셋이 아니다.

연산자의 두 입력 값이 결합된 후에 중복이 발생할 수 있으며, 중복을 포함해서 출력해야 하는 경우 UNION ALL을 사용해야 한다. 중복이 발생할 수 있는 가능성은 있지만 고유한 행들을 출력하고자 한다면 UNION을 사용해야 한다. 
두 입력 값을 결합해도 중복이 존재할 가능성이 없다면 UNION 이나 UNION ALL 모두 논리적으로 동일하다. 하지만 이러한 경우에는 UNION ALL을 사용할 것을 권장한다. 
왜냐하면 UNION을 사용할 경우 SQL Server에서 중복을 검사하는 작업을 수행하기 때문에 추가적인 비용이 발생하기 때문이다.

 

---

교집합

집합 이론에서 두 집합 간의 교집합 요소를 말한다.  T-SQL에서는 INTERSECT 집합 연산자를 이용해서 입력 쿼리들의 결과 집합 중 양쪽 집합 모두에 존재하는 교집합을 반환할 수 있다. 

INTERSECT 

INTERSECT 연산자는 우선 두 개의 입력 멀티 셋에 중복된 행을 제거해서 집합으로 만든다. 그런 다음, 양쪽 집합 모두에 존재하는 행만 반환하게 된다.

INTERSECT ALL

먼저 표준 SQL에서의 INTERSECT ALL 연산자에 대한 의미를 이해하고, T-SQL을 이용해서 구현하는 방법을 살펴보도록 하자
UNION ALL연산자에서 ALL 키워드는 모든 중복된 행들을 출력한다. 이와 마찬가지로  INTERSECT ALL은 중복된 교집합 결과를 제거하지 않는다. 
그런데, UNION ALL과 INTERSECT ALL이 다른 점은 INTERSECT ALL은 모든 중복을 반환하는 것이 아니라 중복된 행의 개수 만큼 반환한다는 점이다.

입력 쿼리 1에서  R이라는 행이 X번 나타났고 입력 쿼리 2에서 R이 y번 나타났다고 한다면, 그 결과로 R행을 min(x, y)번 출력한다.   

예를 들어, 입력 쿼리 1 에서 ('서울', NULL, '영업부')가 2번 나타났고 입력 쿼리 2 에서 3번 나타났다면 INTERSECT ALL의 결과로 ('서울', NULL, '영업부')는 2번 출력된다.
왜냐하면, 논리적인 수준에서 봤을 때, ('서울', NULL, '영업부')에 대한 교집합은 2번 발생되었기 때문이다.

 

SQL Server에서는 자체적으로 INTERSECT ALL연산자를 지원하지 않지만, 그와 동일한 결과를 반환할 수 있는 방법은 제공하고 있다.
ROW_NUMBER 함수를 이용해서, 각 입력 쿼리의 행마다 순번을 매기는 방식을 사용하면 된다.

select row_number() over(partition by city, tel, buseo order by (select 0)) as rownum 
     , city
     , tel
     , buseo 
from TBLINSA with(nolock) 
where jikwi = N'부장' 
intersect 
select row_number() over(partition by city, tel, buseo order by (select 0)) as rownum 
     , city
     , tel
     , buseo 
from TBLINSA with(nolock) 
where buseo = N'영업부' and city = N'서울'

ROW NUMBER에서 PARTITION BY절에는 모든 특성을 나열하고, 함수의 ORDER BY 절에서는 순서가 상관없다는 SELECT <상수>를 기술한다.

INTERSECT ALL 연산자는 행 번호를 출력하지 않도록 되어 있으며, SQL Server에서는 지원되지 않는 INTERSECT ALL 연산자를 구현하기 위해서 행 번호가 사용되었다. 만약, 결과에 행 번호를 출력하고 싶지 않다면,  쿼리 전체를 공통 테이블 식(CTE)과 같은 테이블 표현식으로 정의한 다음, rownum은 조회하지 않도록 한다.

 

 

---

차집합

집합 이론에서 두 집합 간의 차집합을 뜻한다. A와 B의 차집합은 A에서 B를 제거하는 것으로 이해하면 된다.  T-SQL에서 차집합은 EXCEPT 집합 연산자로 구현할 수 있다.  첫 번째 입력 결과 집합에는 있지만 두 번째 집합에는 없는 행들을 반환한다. 

EXCEPT 

EXCEPT 집합 연산자는 먼저 두 개의 입력 멀티 셋에서 중복된 행들을 제거해서 집합으로 만든다. 그런 다음, 첫 번째 집합에만 존재하고 두 번째 집합에는 존재하지 않는 행들만 반환한다. 

 

EXCEPT ALL

EXCEPT ALL 연산자는 EXCEPT 연산자와 매우 유사하지만 각 행들이 몇 번 나타나는 지까지도 고려한다는 점에서 차이가 있다. 입력 쿼리 1에서 R이라는 행이 x번 나타나고 입력 쿼리2 에는 y번 나타나며 x>y 이라면, 
입력 쿼리 1 EXCEPT ALL 입력 쿼리2 의 결과에서 R은 x-y번 나타나게 된다.

R : ('서울', NULL, '영업부')
x : 3
y : 2

즉, 논리적 수준에서  EXCEPT ALL은 입력 쿼리 2에 있는 행들에 대응되지 않는 입력 쿼리 1의 행들만 출력한다.