15.2.14 Set Operations with UNION, INTERSECT, and EXCEPT — MySQL 9.2

15.2.14 Операции с множествами с помощью UNION, INTERSECT и EXCEPT

Операции с множествами SQL объединяют результаты нескольких блоков запросов в один результат. Блок запроса, иногда также известный как простая таблица, — это любой оператор SQL, возвращающий набор результатов, такой как SELECT. MySQL 9.2 также поддерживает операторы TABLE и VALUES. Дополнительную информацию см. в отдельных описаниях этих операторов в этой главе.

Стандарт SQL определяет следующие три операции с множествами:

UNION: Объединяет все результаты из двух блоков запросов в один результат, удаляя дубликаты.
INTERSECT: Объединяет только те строки, которые присутствуют в результатах двух блоков запросов, удаляя дубликаты.
EXCEPT: Для двух блоков запросов A и B возвращает все результаты из A, которые также отсутствуют в B, удаляя дубликаты.

(Некоторые системы баз данных, такие как Oracle, используют MINUS для названия этого оператора. В MySQL это не поддерживается.)

MySQL поддерживает UNION, INTERSECT и EXCEPT.

Каждый из этих операторов множеств поддерживает модификатор ALL. Когда ключевое слово ALL следует за оператором множества, это приводит к включению дубликатов в результат. Дополнительную информацию и примеры см. в следующих разделах, посвященных отдельным операторам.

Все три оператора множеств также поддерживают ключевое слово DISTINCT, которое подавляет дубликаты в результате. Поскольку это стандартное поведение для операторов множеств, обычно не требуется явно указывать DISTINCT.

В общем случае блоки запросов и операции с множествами могут комбинироваться в любом количестве и порядке. Здесь показана сильно упрощенная запись:

query_block [set_op query_block] [set_op query_block] ...

query_block:
    SELECT | TABLE | VALUES

set_op:
    UNION | INTERSECT | EXCEPT

Это можно представить более точно и подробно так:

query_expression:
  [with_clause] /* WITH clause */
  query_expression_body
  [order_by_clause] [limit_clause] [into_clause]

query_expression_body:
    query_term
 |  query_expression_body UNION [ALL | DISTINCT] query_term
 |  query_expression_body EXCEPT [ALL | DISTINCT] query_term

query_term:
    query_primary
 |  query_term INTERSECT [ALL | DISTINCT] query_primary

query_primary:
    query_block
 |  '(' query_expression_body [order_by_clause] [limit_clause] [into_clause] ')'

query_block:   /* also known as a simple table */
    query_specification                     /* SELECT statement */
 |  table_value_constructor                 /* VALUES statement */
 |  explicit_table                          /* TABLE statement  */

Следует учитывать, что INTERSECT вычисляется до UNION или EXCEPT. Это означает, что, например, TABLE x UNION TABLE y INTERSECT TABLE z всегда вычисляется как TABLE x UNION (TABLE y INTERSECT TABLE z). Дополнительную информацию см. в разделе Раздел 15.2.8, «Оператор INTERSECT».

Кроме того, следует помнить, что, хотя операторы множеств UNION и INTERSECT коммутативны (порядок не важен), EXCEPT таковым не является (порядок операндов влияет на результат). Другими словами, все следующие утверждения верны:

TABLE x UNION TABLE y и TABLE y UNION TABLE x производят один и тот же результат, хотя порядок строк может отличаться. Вы можете принудительно установить их одинаковым порядком, используя ORDER BY; см. Операции с множествами с ORDER BY и LIMIT.
TABLE x INTERSECT TABLE y и TABLE y INTERSECT TABLE x возвращают один и тот же результат.
TABLE x EXCEPT TABLE y и TABLE y EXCEPT TABLE x не дают один и тот же результат. См. раздел Раздел 15.2.4, «Оператор EXCEPT» для примера.

Более подробную информацию и примеры можно найти в следующих разделах.

Имена и типы столбцов набора результатов

Имена столбцов результата операции с множествами берутся из имен столбцов первого блока запроса. Пример:

mysql> CREATE TABLE t1 (x INT, y INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES ROW(4,-2), ROW(5,9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT, b INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES ROW(1,2), ROW(3,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> TABLE t1 UNION TABLE t2;
+------+------+
| x    | y    |
+------+------+
|    4 |   -2 |
|    5 |    9 |
|    1 |    2 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql> TABLE t2 UNION TABLE t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    4 |   -2 |
|    5 |    9 |
+------+------+
4 rows in set (0.00 sec)

Это верно для UNION, EXCEPT и INTERSECT запросов.

Выбранные столбцы, указанные в соответствующих позициях каждого блока запроса, должны иметь одинаковый тип данных. Например, первый столбец, выбранный в первом операторе, должен иметь тот же тип, что и первый столбец, выбранный в других операторах. Если типы данных соответствующих столбцов результатов не совпадают, типы и длины столбцов в результирующем наборе учитывают значения, полученные всеми блоками запросов. Например, длина столбца в наборе результатов не ограничена длиной значения из первого оператора, как показано здесь:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
+----------------------+
| REPEAT('a',1)        |
+----------------------+
| a                    |
| bbbbbbbbbbbbbbbbbbbb |
+----------------------+

Операции с множествами с помощью операторов TABLE и VALUES

Вы также можете использовать оператор TABLE или оператор VALUES там, где вы можете использовать эквивалентный оператор SELECT. Предполагается, что таблицы t1 и t2 созданы и заполнены, как показано здесь:

CREATE TABLE t1 (x INT, y INT);
INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9);

CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES ROW(1,2),ROW(3,4);

При этом, и игнорируя имена столбцов в выходных данных запросов, начинающихся с VALUES, все следующие UNION запросы дают одинаковый результат:

SELECT * FROM t1 UNION SELECT * FROM t2;
TABLE t1 UNION SELECT * FROM t2;
VALUES ROW(4,-2), ROW(5,9) UNION SELECT * FROM t2;
SELECT * FROM t1 UNION TABLE t2;
TABLE t1 UNION TABLE t2;
VALUES ROW(4,-2), ROW(5,9) UNION TABLE t2;
SELECT * FROM t1 UNION VALUES ROW(4,-2),ROW(5,9);
TABLE t1 UNION VALUES ROW(4,-2),ROW(5,9);
VALUES ROW(4,-2), ROW(5,9) UNION VALUES ROW(4,-2),ROW(5,9);

Чтобы принудительно установить имена столбцов одинаковыми, оберните блок запроса слева в оператор SELECT и используйте псевдонимы, как показано здесь:

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

Операции с множествами с использованием DISTINCT и ALL

По умолчанию дублирующие строки удаляются из результатов операций с множествами. Ключевое слово DISTINCT имеет тот же эффект, но делает его явным. С ключевым словом ALL удаление дубликатов не происходит, и результат включает все совпадающие строки из всех запросов в объединении.

Вы можете смешивать ALL и DISTINCT в одном запросе. Смешанные типы обрабатываются так, что операция с множеством, использующая DISTINCT, переопределяет любую такую операцию, использующую ALL слева. Множество DISTINCT можно явно создать, используя DISTINCT с UNION, INTERSECT или EXCEPT, или неявно, используя операции с множествами без последующего ключевого слова DISTINCT или ALL.

Операции с множествами работают так же, когда используется один или несколько операторов TABLE, операторов VALUES или оба.

Операции над множествами с ORDER BY и LIMIT

Для применения условия ORDER BY или LIMIT к блоку запроса, используемому в рамках объединения, пересечения или другой операции над множествами, заключите этот блок запроса в скобки, поместив условие внутрь скобок, как показано ниже:

(SELECT a FROM t1 WHERE a=10 AND b=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND b=2 ORDER BY a LIMIT 10);

(TABLE t1 ORDER BY x LIMIT 10)
INTERSECT
(TABLE t2 ORDER BY a LIMIT 10);

Использование ORDER BY для отдельных блоков запросов или инструкций не подразумевает ничего о порядке строк в конечном результате, поскольку строки, полученные в результате операции над множествами, по умолчанию неупорядочены. Поэтому ORDER BY в этом контексте обычно используется в сочетании с LIMIT для определения подмножества выбранных строк для извлечения, даже если это не обязательно влияет на порядок этих строк в конечном результате. Если ORDER BY появляется без LIMIT внутри блока запроса, оно оптимизируется, так как не оказывает никакого эффекта.

Для использования условия ORDER BY или LIMIT для сортировки или ограничения всего результата операции над множествами, поместите ORDER BY или LIMIT после последней инструкции:

SELECT a FROM t1
EXCEPT
SELECT a FROM t2 WHERE a=11 AND b=2
ORDER BY a LIMIT 10;

TABLE t1
UNION
TABLE t2
ORDER BY a LIMIT 10;

Если одна или несколько отдельных инструкций используют ORDER BY, LIMIT или оба, и, кроме того, вы хотите применить ORDER BY, LIMIT или оба к целому результату, то каждая такая отдельная инструкция должна быть заключена в скобки.

(SELECT a FROM t1 WHERE a=10 AND b=1)
EXCEPT
(SELECT a FROM t2 WHERE a=11 AND b=2)
ORDER BY a LIMIT 10;

(TABLE t1 ORDER BY a LIMIT 10)
UNION
TABLE t2
ORDER BY a LIMIT 10;

Инструкция без условия ORDER BY или LIMIT не требует заключать её в скобки; замена TABLE t2 на (TABLE t2) во второй инструкции из приведенных выше не изменяет результат UNION.

Вы также можете использовать ORDER BY и LIMIT со VALUES инструкциями в операциях над множествами, как показано в этом примере с использованием клиента mysql:

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

(Следует помнить, что ни инструкции TABLE, ни инструкции VALUES не принимают условие WHERE.)

Этот вид ORDER BY не может использовать ссылки на столбцы, включающие имя таблицы (то есть имена в формате tbl_name.col_name). Вместо этого укажите псевдоним столбца в первом блоке запроса и ссылайтесь на псевдоним в условии ORDER BY. (Вы также можете сослаться на столбец в условии ORDER BY с помощью его позиции, но такое использование позиций столбцов устарело и, следовательно, может быть удалено в будущей версии MySQL.)

Если столбец, по которому требуется сортировка, имеет псевдоним, условие ORDER BY обязательно должно ссылаться на псевдоним, а не на имя столбца. Первое из следующих утверждений допустимо, но второе приводит к ошибке Unknown column 'a' in 'order clause':

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

Чтобы строки в результатах UNION состояли из наборов строк, извлечённых каждым блоком запроса один за другим, выберите дополнительный столбец в каждом блоке запроса для использования в качестве столбца сортировки и добавьте условие ORDER BY, которое сортирует по этому столбцу после последнего блока запроса:

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

Для сохранения порядка сортировки внутри отдельных результатов добавьте дополнительный столбец в условие ORDER BY:

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

Использование дополнительного столбца также позволяет определить, из какого блока запроса происходит каждая строка. Дополнительные столбцы могут предоставлять и другую идентифицирующую информацию, например, строку, указывающую имя таблицы.

Ограничения операций над множествами

Операции над множествами в MySQL подвержены некоторым ограничениям, которые описаны в следующих нескольких абзацах.

Операции над множествами, включающие SELECT инструкции, имеют следующие ограничения:

HIGH_PRIORITY в первой SELECT не имеет эффекта. HIGH_PRIORITY в любой последующей SELECT приводит к синтаксической ошибке.
Только последняя SELECT инструкция может использовать условие INTO. Однако весь результат UNION записывается в выходное место назначения INTO.

Эти два варианта UNION, содержащие INTO, устарели; ожидается, что их поддержка будет удалена в будущей версии MySQL:

В заключительном блоке запроса выражения, использование INTO перед FROM вызывает предупреждение. Пример:
```
... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name;
```
В заключительном блоке запроса выражения, заключенном в скобки, использование INTO (независимо от его положения относительно FROM) вызывает предупреждение. Пример:
```
... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);
```
Эти варианты устарели, потому что вводят путаницу, как будто они собирают информацию из именованной таблицы, а не из всего выражения запроса (UNION).

Операции над множествами с агрегатной функцией в условии ORDER BY отклоняются с . Хотя имя ошибки может предполагать, что это исключительно для UNION запросов, это также верно для EXCEPT и INTERSECT запросов, как показано здесь:

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

Условие блокировки (такое как FOR UPDATE или LOCK IN SHARE MODE) применяется к блоку запроса, за которым следует. Это означает, что в инструкции SELECT, используемой с операциями над множествами, условие блокировки может быть использовано только в том случае, если блок запроса и условие блокировки заключены в скобки.