데브러너 (DevRunner)

15.2.14 Set Operations with UNION, INTERSECT, and EXCEPT

SQL 집합 연산은 여러 쿼리 블록의 결과를 하나의 결과로 결합합니다.
_쿼리 블록(query block)_은 _단순 테이블(simple table)_이라고도 하며, SELECT과 같이 결과 집합을 반환하는 어떤 SQL 문장(statement)이든 해당합니다. MySQL 9.5는 TABLE 문장과 VALUES 문장도 지원합니다. 이들 문장에 대한 추가 정보는 이 장의 다른 부분에 있는 각 문장의 개별 설명을 참조하십시오.

SQL 표준은 다음 세 가지 집합 연산을 정의합니다:

UNION: 두 쿼리 블록의 모든 결과를 하나의 결과로 결합하며, 중복을 제거합니다.
INTERSECT: 두 쿼리 블록의 결과에 공통으로 존재하는 행만 결합하며, 중복을 제거합니다.
EXCEPT: 두 쿼리 블록 _A_와 _B_에 대해, _B_에는 존재하지 않고 _A_에만 존재하는 모든 결과를 반환하며, 중복을 제거합니다.

(Oracle과 같은 일부 데이터베이스 시스템은 이 연산자의 이름으로 MINUS를 사용합니다. 이는 MySQL에서는 지원되지 않습니다.)

MySQL은 UNION, INTERSECT, EXCEPT를 지원합니다.

이들 각 집합 연산자는 ALL 한정자(modifier)를 지원합니다. 집합 연산자 뒤에 ALL 키워드가 오면, 결과에 중복이 포함되도록 합니다. 각 연산자를 다루는 다음 절에서 더 많은 정보와 예제를 확인할 수 있습니다.

세 집합 연산자 모두 DISTINCT 키워드도 지원하며, 이는 결과에서 중복을 제거합니다. 이는 집합 연산자의 기본 동작이므로, 일반적으로 DISTINCT를 명시적으로 지정할 필요는 없습니다.

일반적으로, 쿼리 블록과 집합 연산은 개수와 순서에 상관없이 조합할 수 있습니다. 이를 크게 단순화한 표현은 다음과 같습니다:

1query_block [set_op query_block] [set_op query_block] ...
2
3query_block:
4    SELECT | TABLE | VALUES
5
6set_op:
7    UNION | INTERSECT | EXCEPT

이를 더 정확하고 자세하게 표현하면 다음과 같습니다:

1query_expression:
2  [with_clause] /* WITH clause */
3  query_expression_body
4  [order_by_clause] [limit_clause] [into_clause]
5
6query_expression_body:
7    query_term
8 |  query_expression_body UNION [ALL | DISTINCT] query_term
9 |  query_expression_body EXCEPT [ALL | DISTINCT] query_term
10
11query_term:
12    query_primary
13 |  query_term INTERSECT [ALL | DISTINCT] query_primary
14
15query_primary:
16    query_block
17 |  '(' query_expression_body [order_by_clause] [limit_clause] [into_clause] ')'
18
19query_block:   /* also known as a simple table */
20    query_specification                     /* SELECT statement */
21 |  table_value_constructor                 /* VALUES statement */
22 |  explicit_table                          /* TABLE statement  */

INTERSECT는 UNION이나 EXCEPT보다 먼저 평가된다는 점에 유의해야 합니다. 예를 들어, TABLE x UNION TABLE y INTERSECT TABLE z는 항상 TABLE x UNION (TABLE y INTERSECT TABLE z)로 평가됩니다. 자세한 내용은 Section 15.2.8, “INTERSECT Clause”를 참조하십시오.

또한 UNION과 INTERSECT 집합 연산자는 교환법칙이 성립(순서가 중요하지 않음)하지만, EXCEPT는 그렇지 않으며(피연산자의 순서가 결과에 영향을 줌), 이 점을 염두에 두어야 합니다. 다시 말해, 다음 문장은 모두 참입니다:

TABLE x UNION TABLE y와 TABLE y UNION TABLE x는 동일한 결과를 생성하지만, 행의 정렬 순서는 다를 수 있습니다. ORDER BY를 사용하면 같게 만들 수 있습니다. 자세한 내용은 Set Operations with ORDER BY and LIMIT를 참조하십시오.
TABLE x INTERSECT TABLE y와 TABLE y INTERSECT TABLE x는 동일한 결과를 반환합니다.
TABLE x EXCEPT TABLE y와 TABLE y EXCEPT TABLE x는 동일한 결과를 반환하지 않습니다. 예제는 Section 15.2.4, “EXCEPT Clause”를 참조하십시오.

추가 정보와 예제는 이후 절에서 확인할 수 있습니다.

Result Set Column Names and Data Types

집합 연산 결과의 열 이름은 첫 번째 쿼리 블록의 열 이름에서 가져옵니다. 예:

1mysql> CREATE TABLE t1 (x INT, y INT);
2Query OK, 0 rows affected (0.04 sec)
3
4mysql> INSERT INTO t1 VALUES ROW(4,-2), ROW(5,9);
5Query OK, 2 rows affected (0.00 sec)
6Records: 2  Duplicates: 0  Warnings: 0
7
8mysql> CREATE TABLE t2 (a INT, b INT);
9Query OK, 0 rows affected (0.04 sec)
10
11mysql> INSERT INTO t2 VALUES ROW(1,2), ROW(3,4);
12Query OK, 2 rows affected (0.01 sec)
13Records: 2  Duplicates: 0  Warnings: 0
14
15mysql> TABLE t1 UNION TABLE t2;
16+------+------+
17| x    | y    |
18+------+------+
19|    4 |   -2 |
20|    5 |    9 |
21|    1 |    2 |
22|    3 |    4 |
23+------+------+
244 rows in set (0.00 sec)
25
26mysql> TABLE t2 UNION TABLE t1;
27+------+------+
28| a    | b    |
29+------+------+
30|    1 |    2 |
31|    3 |    4 |
32|    4 |   -2 |
33|    5 |    9 |
34+------+------+
354 rows in set (0.00 sec)

이 규칙은 UNION, EXCEPT, INTERSECT 쿼리에도 동일하게 적용됩니다.

각 쿼리 블록에서 동일한 위치에 나열된 선택된 열은 같은 데이터 타입을 가져야 합니다. 예를 들어, 첫 번째 문장에서 선택된 첫 번째 열은 다른 문장에서 선택된 첫 번째 열과 동일한 타입이어야 합니다. 대응하는 결과 열의 데이터 타입이 일치하지 않는 경우, 결과의 열 타입과 길이는 모든 쿼리 블록에서 검색된 값을 고려하여 결정됩니다. 예를 들어, 결과 집합의 열 길이는 다음 예와 같이 첫 번째 문장의 값 길이로 제한되지 않습니다:

1mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
2+----------------------+
3| REPEAT('a',1)        |
4+----------------------+
5| a                    |
6| bbbbbbbbbbbbbbbbbbbb |
7+----------------------+

Set Operations with TABLE and VALUES Statements

TABLE 문장이나 VALUES 문장은 동일한 의미의 SELECT 문장을 사용할 수 있는 어떤 곳에서도 사용할 수 있습니다. 다음과 같이 t1과 t2 테이블이 생성되고 데이터가 채워졌다고 가정합니다:

1CREATE TABLE t1 (x INT, y INT);
2INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9);
3
4CREATE TABLE t2 (a INT, b INT);
5INSERT INTO t2 VALUES ROW(1,2),ROW(3,4);

위와 같은 전제에서, VALUES로 시작하는 쿼리의 출력에서 열 이름을 무시하면, 다음 UNION 쿼리는 모두 동일한 결과를 생성합니다:

1SELECT * FROM t1 UNION SELECT * FROM t2;
2TABLE t1 UNION SELECT * FROM t2;
3VALUES ROW(4,-2), ROW(5,9) UNION SELECT * FROM t2;
4SELECT * FROM t1 UNION TABLE t2;
5TABLE t1 UNION TABLE t2;
6VALUES ROW(4,-2), ROW(5,9) UNION TABLE t2;
7SELECT * FROM t1 UNION VALUES ROW(4,-2),ROW(5,9);
8TABLE t1 UNION VALUES ROW(4,-2),ROW(5,9);
9VALUES ROW(4,-2), ROW(5,9) UNION VALUES ROW(4,-2),ROW(5,9);

열 이름을 동일하게 강제하려면, 왼쪽 쿼리 블록을 SELECT 문장으로 감싸고 다음과 같이 별칭(alias)을 사용합니다:

1mysql> SELECT * FROM (TABLE t2) AS t(x,y) UNION TABLE t1;
2+------+------+
3| x    | y    |
4+------+------+
5|    1 |    2 |
6|    3 |    4 |
7|    4 |   -2 |
8|    5 |    9 |
9+------+------+
104 rows in set (0.00 sec)

Set Operations using DISTINCT and ALL

기본적으로, 집합 연산 결과에서는 중복 행이 제거됩니다. 선택적인 DISTINCT 키워드는 동일한 효과를 가지나, 이를 명시적으로 표현해 줍니다. 선택적인 ALL 키워드를 사용하면, 중복 행 제거는 발생하지 않고 유니온에 포함된 모든 쿼리의 일치하는 행이 결과에 포함됩니다.

하나의 쿼리 안에서 ALL과 DISTINCT를 혼용해서 사용할 수 있습니다. 혼합된 타입의 경우, DISTINCT를 사용하는 집합 연산은 자신의 왼쪽에 있는 어떤 ALL을 사용하는 연산보다 우선합니다.
DISTINCT 집합은 UNION, INTERSECT, EXCEPT와 함께 DISTINCT를 사용하여 명시적으로 생성하거나, 집합 연산 뒤에 DISTINCT나 ALL 키워드를 사용하지 않음으로써 묵시적으로 생성할 수 있습니다.

집합 연산은 하나 이상의 TABLE 문장, VALUES 문장 또는 둘 다를 사용하여 집합을 생성할 때에도 동일하게 동작합니다.

Set Operations with ORDER BY and LIMIT

유니온, 인터섹션 또는 다른 집합 연산의 일부로 사용되는 개별 쿼리 블록에 ORDER BY 또는 LIMIT 절을 적용하려면, 쿼리 블록에 괄호를 씌우고, 해당 절을 괄호 안에 배치합니다. 예:

1(SELECT a FROM t1 WHERE a=10 AND b=1 ORDER BY a LIMIT 10)
2UNION
3(SELECT a FROM t2 WHERE a=11 AND b=2 ORDER BY a LIMIT 10);
4
5(TABLE t1 ORDER BY x LIMIT 10)
6INTERSECT
7(TABLE t2 ORDER BY a LIMIT 10);

개별 쿼리 블록 또는 문장에서의 ORDER BY 사용은, 집합 연산으로 생성되는 행이 기본적으로 정렬되지 않기 때문에, 최종 결과에서 행이 나타나는 순서에 대해 아무것도 의미하지 않습니다. 따라서 이 문맥에서의 ORDER BY는 일반적으로 LIMIT과 함께 사용되어, 선택된 행 중에서 가져올 서브셋을 결정하는 데 사용되며, 설령 최종 결과에서 그 행의 순서를 반드시 변경하지는 않을 수 있습니다. 쿼리 블록 내에서 LIMIT 없이 ORDER BY가 나타나면, 어떤 경우에도 효과가 없으므로 최적화 과정에서 제거됩니다.

집합 연산 전체 결과를 정렬하거나 제한하기 위해 ORDER BY 또는 LIMIT 절을 사용하려면, ORDER BY 또는 LIMIT를 마지막 문장(statement) 뒤에 배치합니다:

1SELECT a FROM t1
2EXCEPT
3SELECT a FROM t2 WHERE a=11 AND b=2
4ORDER BY a LIMIT 10;
5
6TABLE t1
7UNION
8TABLE t2
9ORDER BY a LIMIT 10;

하나 이상의 개별 문장에서 ORDER BY, LIMIT 또는 둘 다를 사용하고, 여기에 더해 전체 결과에 대해 ORDER BY, LIMIT 또는 둘 다를 적용하려는 경우, 그러한 개별 문장 각각은 모두 괄호로 둘러싸야 합니다.

1(SELECT a FROM t1 WHERE a=10 AND b=1)
2EXCEPT
3(SELECT a FROM t2 WHERE a=11 AND b=2)
4ORDER BY a LIMIT 10;
5
6(TABLE t1 ORDER BY a LIMIT 10)
7UNION
8TABLE t2
9ORDER BY a LIMIT 10;

ORDER BY나 LIMIT 절이 없는 문장은 괄호로 감쌀 필요가 없습니다. 바로 앞 예제의 두 번째 문장에서 TABLE t2를 (TABLE t2)로 바꾸더라도 UNION의 결과는 변하지 않습니다.

집합 연산에서 VALUES 문장과도 ORDER BY와 LIMIT를 함께 사용할 수 있습니다. 다음 예제는 mysql 클라이언트를 사용한 것입니다:

1mysql> VALUES ROW(4,-2), ROW(5,9), ROW(-1,3)
2    -> UNION
3    -> VALUES ROW(1,2), ROW(3,4), ROW(-1,3)
4    -> ORDER BY column_0 DESC LIMIT 3;
5+----------+----------+
6| column_0 | column_1 |
7+----------+----------+
8|        5 |        9 |
9|        4 |       -2 |
10|        3 |        4 |
11+----------+----------+
123 rows in set (0.00 sec)

(TABLE 문장과 VALUES 문장은 WHERE 절을 허용하지 않는다는 점을 염두에 두어야 합니다.)

이러한 형태의 ORDER BY에서는 테이블 이름을 포함하는 열 참조(즉, tbl_name.col_name 형식)를 사용할 수 없습니다. 대신 첫 번째 쿼리 블록에서 열 별칭을 제공하고, ORDER BY 절에서 그 별칭을 참조해야 합니다. (ORDER BY 절에서 열 위치를 사용해 열을 참조할 수도 있지만, 열 위치 사용은 더 이상 사용이 권장되지 않으며(deprecated), 향후 MySQL 릴리즈에서 제거될 수 있습니다.)

정렬 대상 열이 별칭된 경우, ORDER BY 절은 열 이름이 아니라 반드시 해당 별칭을 참조해야 합니다. 다음 두 문장 중 첫 번째는 허용되지만, 두 번째는 Unknown column 'a' in 'order clause' 에러로 실패합니다:

1(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
2(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

UNION 결과의 행을, 각 쿼리 블록이 검색한 행 집합이 차례대로 이어지도록 만들려면, 각 쿼리 블록에서 정렬 열로 사용할 추가 열을 선택하고, 마지막 쿼리 블록 뒤에 해당 열을 기준으로 정렬하는 ORDER BY 절을 추가합니다:

1(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
2UNION
3(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

개별 결과 내의 정렬 순서를 유지하려면, ORDER BY 절에 보조 열을 추가합니다:

1(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
2UNION
3(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

추가 열을 사용하면 각 행이 어느 쿼리 블록에서 왔는지 알 수 있습니다. 추가 열은 테이블 이름을 나타내는 문자열처럼, 다른 식별 정보를 제공하는 데에도 사용할 수 있습니다.

Limitations of Set Operations

MySQL에서의 집합 연산은 몇 가지 제한 사항의 적용을 받으며, 이는 다음 몇 개의 단락에서 설명합니다.

SELECT 문장을 포함하는 집합 연산에는 다음 제한 사항이 있습니다:

첫 번째 SELECT의 HIGH_PRIORITY는 아무 효과도 없습니다. 그 이후의 SELECT에서의 HIGH_PRIORITY는 구문 에러(syntax error)를 발생시킵니다.
마지막 SELECT 문장만 INTO 절을 사용할 수 있습니다. 그러나 UNION 전체 결과가 INTO 출력 대상에 기록됩니다.

다음의 두 가지 INTO를 포함하는 UNION 변형은 더 이상 사용이 권장되지 않으며(deprecated), 향후 MySQL 버전에서 지원이 제거될 것으로 예상해야 합니다:

쿼리 표현식의 마지막 쿼리 블록에서, FROM 앞에서의 INTO 사용은 경고(warning)를 발생시킵니다. 예:

1... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name;

쿼리 표현식의 괄호로 둘러싸인 마지막 블록에서, INTO를 사용하는 경우(이는 FROM과의 상대적인 위치에 상관없이) 경고를 발생시킵니다. 예:

1... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);

이러한 변형은, 이름 있는 테이블이 아닌 전체 쿼리 표현식(UNION)에서 정보를 수집하는지에 대해 혼동을 일으키기 때문에 더 이상 사용이 권장되지 않습니다.

ORDER BY 절에서 집계 함수(aggregate function)를 사용하는 집합 연산은 ER_AGGREGATE_ORDER_FOR_UNION으로 거부됩니다. 에러 이름으로 볼 때 이것이 UNION 쿼리에만 해당하는 것처럼 보일 수도 있지만, 다음 예에서 보듯이, 이 규칙은 EXCEPT와 INTERSECT 쿼리에도 동일하게 적용됩니다:

1mysql> TABLE t1 INTERSECT TABLE t2 ORDER BY MAX(x);
2ERROR 3028 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to a UNION, EXCEPT or INTERSECT

FOR UPDATE나 LOCK IN SHARE MODE와 같은 잠금 절(locking clause)은 자신이 뒤따르는 쿼리 블록에 적용됩니다. 이는 집합 연산과 함께 사용되는 SELECT 문장에서, 잠금 절을 사용할 수 있는 경우는 쿼리 블록과 잠금 절이 괄호로 둘러싸인 경우뿐임을 의미합니다.