[DB][SQL] 집합 연산자(UNION, INTERSECT, EXCEPT)

※ 해당 포스팅은 개인의 공부 정리용 글입니다. 틀린 내용이 있다면 추후 수정될 수 있습니다.

※ 해당 포스팅은 Mycrosoft SQL Server에서 사용하는 T-SQL 문법을 기반으로 하고 있으며, 예시는 SSMS(SQL Server Management Studio) 환경에서 작성하고 있습니다. 

 

 

 

이번 포스팅에서는 두 쿼리의 결과물에 사용할 수 있는 집합 연산자들에 대해 알아보겠습니다.

0. 서문

 이번 포스팅에서 알아볼 UNION, INTERSECT, EXCEPT 키워드들은 모두 두개의 쿼리 사이에 작성한다는 특징이 있습니다. 

SELECT 속성1, 속성2, ...
WHERE 조건식1 (AND/OR) 조건식2 ...
GROUP BY 속성1, ....
(UNION/INTERSECT/EXCEPT)
SELECT 속성1, 속성2, ...
WHERE 조건식1 (AND/OR) 조건식2 ...
GROUP BY 속성1, ....
ORDER BY 속성1 (ASC/DESC), ...

 위와 같은 식으로 작성하게 되며, 결과물은 두 쿼리의 결과물에 집합 연산자를 적용한 하나의 테이블(릴레이션)이 됩니다. ORDER BY 키워드의 경우 아래쪽 쿼리에만 작성할 수 있으며, 집합 연산자까지 적용된 결과물을 정렬합니다.

 

 그리고 집합 연산자이기에 두 쿼리의 결과물이 합병 가능한 테이블(릴레이션)이어야 합니다. 합병 가능 조건에 대한 내용은 관계 데이터 모델 관련 포스팅을 참조해주시기 바랍니다.

https://sam0308.tistory.com/46

[DB] 관계 데이터 모델

 

 또한 후술할 예시에서는 baseballData 데이터베이스를 사용할 예정입니다.

 

 

1. UNION

 UNION은 합집합(∪) 연산을 수행하는 키워드로, 두 쿼리의 결과물의 합집합을 반환합니다. 수학에서의 합집합과 마찬가지로 교환법칙이 성립하므로 쿼리의 순서는 중요하지 않습니다. 다음은 UNION 키워드를 사용하는 예시입니다.

-- 평균 연봉이 10,000,000 보다 높거나 
-- 출생지가 Atlanta인 선수
SELECT playerID
FROM salaries
GROUP BY playerID
HAVING AVG(salary) > 10000000
UNION
SELECT playerID
FROM players
WHERE birthCity = 'Atlanta';

사진 1. UNION 결과

 위쪽 쿼리는 playerID로 그룹화한 뒤 평균 연봉(AVG(salary))이 10,000,000보다 높은 선수의 playerID를 출력하는 내용이며, 아래쪽 쿼리는 출생지가 Atlanta인 선수의 playerID를 출력하는 내용입니다. UNION으로 두 쿼리의 결과물의 합집합을 출력하고 있습니다. 참고로 ORDER BY 키워드가 없음에도 결과물이 정렬되어 있는 것을 볼 수 있는데, 이는 UNION 키워드가 결과물의 중복을 허용하지 않기 때문입니다. 따라서 중복 제거를 위해 내부적으로 정렬을 실행하기 때문에 ORDER BY 키워드가 없어도 자동으로 정렬된 결과를 반환합니다.

 

 다만 정렬 비용은 데이터가 많을수록 무시할 수 없으므로, 이를 막기 위해 중복을 허용하는 UNION ALL을 사용할 수도 있습니다.  UNION과 같은 합집합 연산이지만, 중복을 허용하므로 내부적으로 정렬을 실행하지 않습니다. 다음은 위 예시에서 UNION을 UNION ALL로 대체한 예시입니다.

-- 평균 연봉이 10,000,000 보다 높거나 
-- 출생지가 Atlanta인 선수
-- 중복 허용(정렬 미실시)
SELECT playerID
FROM salaries
GROUP BY playerID
HAVING AVG(salary) > 10000000
UNION ALL
SELECT playerID
FROM players
WHERE birthCity = 'Atlanta';

사진 2. UNION ALL 결과

 playerID가 정렬되어 있지 않으며, 'mayerer01'이라는 playerID가 중복해서 나타나는 것을 확인할 수 있습니다.

 

 

2. INTERSECT 

 INTERSECT는 교집합(∩) 연산을 수행하는 키워드로, 두 쿼리의 결과물의 교집합을 반환합니다. 수학에서의 교집합과 마찬가지로 교환법칙이 성립하므로 쿼리의 순서는 중요하지 않습니다. 다음은 INTERSECT 키워드를 사용하는 예시입니다.

-- 평균 연봉이 2,000,000 보다 높고
-- 출생지가 Atlanta인 선수
SELECT playerID
FROM salaries
GROUP BY playerID
HAVING AVG(salary) > 2000000
INTERSECT
SELECT playerID
FROM players
WHERE birthCity = 'Atlanta';

사진 3. INTERSECT 결과

 위쪽 쿼리는 playerID로 그룹화한 뒤 평균 연봉(AVG(salary))이 2,000,000보다 높은 선수의 playerID를 출력하는 내용이며, 아래쪽 쿼리는 출생지가 Atlanta인 선수의 playerID를 출력하는 내용입니다. INTERSECT 키워드를 통해 양쪽 쿼리의 결과에 모두 속하는 선수를 출력하고 있습니다.

 

 

 

3. EXCEPT

 EXCEPT는 차집합(－) 연산을 수행하는 키워드로, 두 쿼리의 결과물의 차집합을 반환합니다. 수학에서의 차집합과 마찬가지로 교환법칙이 성립하지 않으므로 쿼리의 순서에 주의해야 합니다(A EXCEPT B => A－B). 다음은 EXCEPT 키워드를 사용하는 예시입니다.

-- 평균 연봉이 2,000,000보다 높은 선수 중
-- 출생지가 Atlanta인 선수 제외
SELECT playerID
FROM salaries
GROUP BY playerID
HAVING AVG(salary) > 2000000
EXCEPT
SELECT playerID
FROM players
WHERE birthCity = 'Atlanta';

사진 4. EXCEPT 결과

 위쪽 쿼리는 playerID로 그룹화한 뒤 평균 연봉(AVG(salary))이 2,000,000보다 높은 선수의 playerID를 출력하는 내용이며, 아래쪽 쿼리는 출생지가 Atlanta인 선수의 playerID를 출력하는 내용입니다. EXCEPT 키워드를 통해 위쪽 쿼리의 결과에서 아래쪽 쿼리의 결과에 속하는 데이터를 제외하고 출력하고 있습니다. 위 예시의 경우 '평균 연봉이 2,000,000보다 높은 선수 중 출생지가 Atlanta인 선수를 제외'한 결과를 출력합니다.

 

 차집합은 쿼리 순서에 영향을 받으며, 위쪽 쿼리와 아래쪽 쿼리의 순서를 바꿀 경우 '출생지가 Atlanta인 선수 중 평균 연봉이 2,000,000보다 높은 선수를 제외'한 결과를 출력합니다. 다음은 위아래 쿼리의 순서를 바꾸는 경우의 예시입니다. 

-- 출생지가 Atlanta인 선수 중
-- 평균 연봉이 2,000,000보다 높은 선수 제외
SELECT playerID
FROM players
WHERE birthCity = 'Atlanta'
EXCEPT
SELECT playerID
FROM salaries
GROUP BY playerID
HAVING AVG(salary) > 2000000;

사진 5. EXCEPT -   순서 변경

 사진 5의 결과를 보면 사진 4와 전혀 다른 결과임을 확인할 수 있습니다.