SQL 집합 연산자

상위 문서: SQL

Set Operator
두 개 이상의 SELECT 결과를 집합으로 보고 합집합·교집합·차집합으로 결합하는 연산자

집합 연산자는 관계 대수의 합집합(∪), 교집합(∩), 차집합(−)을 SQL로 구현한 것이다. 조인이 칼럼을 옆으로 붙인다면, 집합 연산자는 같은 모양의 결과 행을 위아래로 결합한다. UNION과 UNION ALL의 기본 사용법은 SQL UNION 연산자 문서에 있으며, 이 문서는 네 연산자를 함께 비교하고 결과 행 수 계산, NULL 처리, 우선순위를 다룬다.

종류

연산자	집합 연산	중복 행	설명
UNION	합집합	제거	두 결과를 합친 뒤 중복 행을 하나로 만든다.
UNION ALL	합집합	유지	두 결과를 그대로 이어 붙인다. 행 수 = 첫째 결과 행 수 + 둘째 결과 행 수
INTERSECT	교집합	제거	두 결과에 모두 있는 행만 남긴다.
MINUS (Oracle) / EXCEPT (표준)	차집합	제거	첫째 결과에는 있고 둘째 결과에는 없는 행만 남긴다. 순서를 바꾸면 결과가 달라진다.

DBMS	차집합 연산자
Oracle	MINUS. 21c부터 EXCEPT도 MINUS의 동의어로 지원하고, INTERSECT ALL·MINUS ALL·EXCEPT ALL도 추가되었다.
SQL Server(MS SQL)	EXCEPT (MINUS 없음)
PostgreSQL	EXCEPT, EXCEPT ALL

시험은 고전 Oracle(19c 이하) 기준이므로 Oracle의 차집합은 MINUS로 기억하면 된다. 이 문서의 결과는 Oracle 23ai 계열에서 실행한 것이며, 여기서는 EXCEPT도 MINUS와 같은 결과를 낸다.

작성 규칙

SELECT 칼럼1, 칼럼2 FROM 테이블1 [WHERE ...] [GROUP BY ...] [HAVING ...]
{UNION | UNION ALL | INTERSECT | MINUS}
SELECT 칼럼1, 칼럼2 FROM 테이블2 [WHERE ...] [GROUP BY ...] [HAVING ...]
[ORDER BY ...];   -- 맨 마지막에 한 번만

각 SELECT의 칼럼 수가 같아야 한다. 다르면 ORA-01789(query block has incorrect number of result columns) 오류가 난다.
대응하는 칼럼의 데이터 타입이 호환되어야 한다. 문자 칼럼과 숫자 1을 합치면 ORA-01790(expression must have same datatype as corresponding expression) 오류가 난다.
결과의 칼럼명(별칭)은 첫 번째 SELECT의 것을 쓴다. 따라서 ORDER BY에는 첫 번째 SELECT의 칼럼명·별칭이나 위치 번호를 쓴다.
ORDER BY는 전체 결과에 대해 마지막 SELECT 뒤에 한 번만 쓸 수 있다. 중간 SELECT에 ORDER BY를 쓰면 문법 오류다.
WHERE, GROUP BY, HAVING은 각 SELECT마다 따로 쓸 수 있다.

SELECT 값 AS 첫째 FROM A
UNION
SELECT 값 AS 둘째 FROM B
ORDER BY 둘째;   -- ORA-00904: "둘째": invalid identifier
                 -- ORDER BY 첫째 또는 ORDER BY 1 이어야 한다

결과 행 수 계산 예제

중복 행과 NULL이 들어 있는 두 테이블을 준비한다.

A.값
가
가
나
다
NULL

B.값
나
나
라
NULL

SELECT 값 FROM A UNION     SELECT 값 FROM B;
SELECT 값 FROM A UNION ALL SELECT 값 FROM B;
SELECT 값 FROM A INTERSECT SELECT 값 FROM B;
SELECT 값 FROM A MINUS     SELECT 값 FROM B;
SELECT 값 FROM B MINUS     SELECT 값 FROM A;

연산	결과 값	행 수	계산 방법
A UNION B	가, 나, 다, 라, NULL	5	서로 다른 값 {가, 나, 다, 라, NULL}
A UNION ALL B	가, 가, 나, 다, NULL, 나, 나, 라, NULL	9	5 + 4, 중복과 NULL을 모두 유지
A INTERSECT B	나, NULL	2	양쪽에 모두 있는 서로 다른 값
A MINUS B	가, 다	2	A의 서로 다른 값 {가, 나, 다, NULL}에서 B에 있는 나, NULL을 뺀다
B MINUS A	라	1	B의 서로 다른 값 {나, 라, NULL}에서 A에 있는 나, NULL을 뺀다

UNION, INTERSECT, MINUS는 결과에서 중복을 제거한다. A에 '가'가 두 번 있어도 A MINUS B에는 '가'가 한 번만 나온다.
집합 연산에서는 NULL끼리 같은 값으로 취급한다. 그래서 NULL이 INTERSECT 결과에 남고, UNION 결과에는 NULL이 한 번만 나오며, MINUS에서는 NULL이 빠진다.
반면 WHERE 조건에서 NULL = NULL은 참이 아니다. 같은 데이터로 SELECT 값 FROM A WHERE 값 IN (SELECT 값 FROM B)를 실행하면 '나' 1행만 나오고 NULL은 나오지 않는다. 집합 연산의 비교와 조건식의 비교가 다르다는 점이 자주 출제된다.

참고: ALL 붙은 교집합·차집합

Oracle 21c 이상에서는 중복을 개수대로 계산하는 INTERSECT ALL, MINUS ALL(EXCEPT ALL)도 쓸 수 있다. 같은 데이터로 실행하면 A INTERSECT ALL B는 나, NULL(2행), A MINUS ALL B는 가, 가, 다(3행)이다. A의 '나' 1개는 B의 '나' 2개 중 하나와 짝지어져 빠지고, '가' 2개는 그대로 남는다. 19c 이하에는 없는 문법이다.

정렬과 성능

UNION, INTERSECT, MINUS는 중복을 없애야 하므로 내부적으로 정렬이나 해시 같은 추가 작업이 필요하다. UNION ALL은 이 작업이 없어 일반적으로 더 빠르다.
두 결과에 겹치는 행이 없다는 것이 확실하거나 중복을 그대로 보여 줘야 한다면 UNION 대신 UNION ALL을 쓴다. 단, 겹치는 행이 있으면 두 연산의 결과 행 수가 달라지므로 성능만 보고 바꾸면 안 된다.
중복 제거 과정에서 결과가 정렬되어 나오는 경우가 있지만 보장되지 않는다. 위 예제의 A UNION B를 ORDER BY 없이 실행하면 결과가 가, 나, 다, NULL, 라 순서로 나왔다. 결과 순서가 필요하면 반드시 마지막에 ORDER BY를 쓴다.
ORDER BY 1로 정렬하면 가, 나, 다, 라, NULL 순서가 된다. Oracle은 오름차순 정렬에서 NULL을 맨 뒤에 둔다.

연산자 우선순위

Oracle은 모든 집합 연산자의 우선순위가 같아서, 괄호가 없으면 위(왼쪽)에서부터 차례로 계산한다. 반면 SQL 표준과 PostgreSQL 등은 INTERSECT를 UNION·EXCEPT보다 먼저 계산한다. Oracle 문서도 앞으로 표준을 따르도록 바뀔 수 있으니 INTERSECT를 다른 집합 연산자와 섞을 때는 괄호를 쓰라고 권한다.

-- (1) 괄호 없음
SELECT '가' AS 값 FROM DUAL
UNION
SELECT '나' FROM DUAL
INTERSECT
SELECT '나' FROM DUAL;

-- (2) INTERSECT를 먼저 계산하도록 괄호 사용
SELECT '가' AS 값 FROM DUAL
UNION
(SELECT '나' FROM DUAL
 INTERSECT
 SELECT '나' FROM DUAL);

쿼리	Oracle 계산 순서	결과
(1)	({가} UNION {나}) INTERSECT {나}	나 (1행)
(2)	{가} UNION ({나} INTERSECT {나})	가, 나 (2행)

표준 SQL을 따르는 DBMS에서는 (1)도 (2)처럼 계산되어 2행이 나온다.
앞 예제의 테이블로 A UNION ALL B MINUS B를 실행하면 (A UNION ALL B) MINUS B로 계산되어 가, 다 2행이 나온다. MINUS가 결과의 중복까지 제거하므로 UNION ALL로 생긴 중복은 남지 않는다.

시험 포인트

중복과 NULL이 섞인 두 테이블로 UNION, UNION ALL, INTERSECT, MINUS의 결과 행 수를 계산하는 문제가 가장 흔하다. 집합 연산에서는 NULL을 같은 값으로 보고 중복 제거 대상에 넣는다.
결과 칼럼명은 첫 번째 SELECT 기준이며, ORDER BY는 맨 마지막에 한 번만 쓴다.
Oracle의 MINUS = 표준·SQL Server의 EXCEPT이며, A MINUS B와 B MINUS A는 결과가 다르다.
Oracle은 집합 연산자 우선순위가 모두 같아 위에서부터 계산한다. 괄호가 있으면 괄호 안을 먼저 계산한다.
UNION ALL은 중복 제거 작업이 없어 UNION보다 빠르지만, 중복이 있으면 결과가 다르다.

같이 보기

참고 문헌