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

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

SQL-операции над множествами объединяют результаты нескольких блоков запросов в один результат. Блок запроса, иногда также известный как простая таблица, — это любое SQL-утверждение, которое возвращает набор результатов, например SELECT. MySQL 8.4 также поддерживает 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, используемой с операциями над множествами, условие блокировки может быть использовано только в том случае, если блок запроса и условие блокировки заключены в скобки.