UNION в SQL: объединение таблиц и разница с UNION ALL

UNION склеивает результаты двух запросов по вертикали: строки второго добавляются к строкам первого. Требований два: одинаковое число колонок и совместимые типы. UNION убирает повторы и потому обязан их искать, UNION ALL отдаёт всё как есть и обходится дешевле. Дальше обе формы на живых данных, вместе с INTERSECT, EXCEPT и планами запросов.

Разбор идёт на трёх таблицах. Две знакомые:

users

orders

Третья появилась ради этой темы: старые заказы вынесли в orders_archive со структурой один в один.

orders_archive

Два подвоха заложены заранее. Заказ 10 успели скопировать в архив, но из orders не удалили, и теперь он лежит в обеих таблицах целиком одинаковый. У заказов 8 и 9 пустой user_id: их оформили без регистрации. Весь вывод ниже снят с PostgreSQL 16.

Что делает UNION

Ставит результаты запросов друг под друга. Сначала версия без дедупликации:

SELECT id, user_id, amount, created_at FROM orders_archive
UNION ALL
SELECT id, user_id, amount, created_at FROM orders;

Десять строк: четыре из архива, шесть из orders. Заказ 10 идёт дважды. Заменим оператор на UNION, и останется девять строк: вторая копия исчезнет.

Порядок рассыпался. Это настоящий вывод, а не опечатка: дедупликация идёт хешем, и строки выходят в том порядке, в каком их отдал хеш. Без явного ORDER BY порядок не гарантирован ни у UNION, ни у UNION ALL: у второго ветки может перетасовать Parallel Append.

Дубликатом считается полностью совпавшая строка, а не совпавший id. Заказы 12 и 13 оба на 1200, но id и даты разные, поэтому оба остались.

Чем UNION отличается от UNION ALL

Разницу в результате ты уже видел: 10 строк против 9. Вторая разница в цене, и она видна в плане.

EXPLAIN
SELECT id, user_id, amount, created_at FROM orders_archive
UNION ALL
SELECT id, user_id, amount, created_at FROM orders;

Append  (cost=0.00..52.10 rows=2140 width=16)
  ->  Seq Scan on orders_archive  (cost=0.00..20.70 rows=1070 width=16)
  ->  Seq Scan on orders  (cost=0.00..20.70 rows=1070 width=16)

Один узел Append: прочитал обе таблицы, отдал строки подряд. Тот же запрос с UNION:

HashAggregate  (cost=73.50..94.90 rows=2140 width=16)
  Group Key: orders_archive.id, orders_archive.user_id, orders_archive.amount, orders_archive.created_at
  ->  Append  (cost=0.00..52.10 rows=2140 width=16)
        ->  Seq Scan on orders_archive  (cost=0.00..20.70 rows=1070 width=16)
        ->  Seq Scan on orders  (cost=0.00..20.70 rows=1070 width=16)

Сверху вырос HashAggregate с группировкой по всем четырём колонкам — это и есть поиск повторов. Чтобы понять, какие строки одинаковые, база обязана собрать их все и сравнить. На десяти строках такая работа бесплатна. На объёме нет: возьми две таблицы по 300 000 строк при work_mem в 4 МБ, и план переключится на Sort + Unique, а EXPLAIN ANALYZE допишет Sort Method: external merge Disk: 15296kB. Сортировка не влезла в память и ушла на диск. Во сколько раз это дороже UNION ALL, зависит от железа и настроек, так что меряй на своих данных.

Правило короткое. По умолчанию пиши UNION ALL, а UNION бери тогда, когда дубликаты реально возможны и мешают.

UNION или JOIN: что выбрать

Путаница держится на слове «объединить». JOIN добавляет колонки, UNION добавляет строки.

SELECT u.name, u.city, o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY o.id;

Строк ровно столько, сколько заказов, но рядом с каждым встали имя и город. Данные стали шире. UNION же оставляет ширину нетронутой и добирает высоту: объединение orders с orders_archive из первой секции дало те же четыре колонки и десять строк вместо шести.

Признак для выбора такой. Нужны дополнительные сведения про те же сущности, значит JOIN. Нужны те же сведения из другого места, значит UNION. Пять видов соединений и их поведение со строками без пары разобраны в статье про JOIN.

Какие требования UNION предъявляет к запросам

Их два, и оба проверяются до выполнения.

Число колонок должно совпадать.

SELECT id, amount FROM orders_archive
UNION
SELECT id FROM orders;

ERROR:  each UNION query must have the same number of columns
LINE 3: SELECT id FROM orders;
               ^

Типы должны приводиться друг к другу. Первые колонки сошлись, обе integer. На второй позиции amount объявлен как integer, status как text, общего типа у них нет:

SELECT id, amount FROM orders_archive
UNION
SELECT id, status FROM orders;

ERROR:  UNION types integer and text cannot be matched
LINE 3: SELECT id, status FROM orders;
                   ^

«Совместимые» тут не значит «одинаковые»: integer и numeric уживаются, PostgreSQL приводит обе колонки к numeric и молча выполняет запрос. А integer с text придётся мирить руками, через amount::text.

Имена колонок берутся из первого запроса, алиасы остальных игнорируются:

SELECT count(*) AS archive_rows FROM orders_archive
UNION ALL
SELECT count(*) AS order_rows FROM orders;

Второй запрос назвал колонку order_rows, в выдаче этого имени нет.

Как отсортировать результат UNION

ORDER BY относится ко всему объединению и пишется один раз, после последнего запроса. Внутри отдельной части он запрещён:

SELECT id, amount FROM orders_archive
ORDER BY amount DESC
UNION ALL
SELECT id, amount FROM orders;

ERROR:  syntax error at or near "UNION"
LINE 3: UNION ALL
        ^

Ошибка синтаксическая, до смысла парсер даже не добирается: такой конструкции нет в грамматике.

Сортировать можно только по колонкам результата, а имена у результата, как уже выяснили, достались от первого запроса:

SELECT id AS archive_id, amount FROM orders_archive
UNION ALL
SELECT id AS order_id, amount FROM orders
ORDER BY order_id;

ERROR:  column "order_id" does not exist
LINE 4: ORDER BY order_id;
                 ^
DETAIL:  There is a column named "order_id" in table "*SELECT* 2", but it cannot be referenced from this part of the query.

По той же причине провалится ORDER BY created_at, если created_at не попал в SELECT: после объединения такой колонки в результате уже нет.

Сортировка внутри части законна, если часть взята в скобки. Практический смысл ей придаёт LIMIT: сортировка решает, какие именно строки из этой части дойдут до объединения, а какие отсекутся ещё до него.

(SELECT id, amount FROM orders_archive ORDER BY amount DESC, id LIMIT 2)
UNION ALL
(SELECT id, amount FROM orders ORDER BY amount DESC, id LIMIT 2);

Четыре строки: заказы 7 и 10 из архива, 11 и 12 из orders. id в сортировке дописан не для красоты: заказы 12 и 13 оба на 1200, и без второго ключа непонятно, который из них заберёт LIMIT. Порядок самих четырёх строк всё равно не гарантирован, и если он важен, добавляй ORDER BY снаружи скобок.

Что делают INTERSECT и EXCEPT

Оба подчиняются тем же правилам совместимости и тоже убирают дубликаты.

INTERSECT оставляет строки, которые нашлись в обоих запросах:

SELECT id FROM orders
INTERSECT
SELECT id FROM orders_archive;

Ответ: 10. Один заказ есть и в живой таблице, и в архиве — тот самый, который забыли удалить.

EXCEPT оставляет строки первого запроса, которых нет во втором:

SELECT id FROM orders
EXCEPT
SELECT id FROM orders_archive
ORDER BY id;

Ответ: 11, 12, 13, 14, 15. Порядок операндов здесь решает всё, EXCEPT несимметричен. Поменяй запросы местами, и orders_archive EXCEPT orders вернёт 7, 8, 9. Первый ответ показывает, что ещё не заархивировали; второй — что осталось только в архиве. Отсюда типовая работа для EXCEPT: сверка двух источников, когда выгрузку из биллинга вычитают из выгрузки CRM и смотрят на расхождение.

У обоих операторов есть варианты INTERSECT ALL и EXCEPT ALL, сохраняющие кратность строк так же, как UNION ALL.

Как UNION сравнивает NULL

Обычное сравнение с NULL не даёт ни истины, ни лжи: SELECT NULL = NULL возвращает NULL. Дедупликация ведёт себя иначе: для неё два NULL считаются одним значением. В архиве два заказа без user_id, и UNION ALL покажет оба. UNION схлопнет их в один:

SELECT user_id FROM orders_archive
UNION
SELECT user_id FROM orders
ORDER BY user_id;

Из десяти исходных значений осталось четыре, и NULL среди них один. Так же ведут себя DISTINCT, GROUP BY, INTERSECT и EXCEPT: в стандарте правило записано через IS NOT DISTINCT FROM, и NULL IS NOT DISTINCT FROM NULL действительно возвращает true. При этом обычное сравнение остаётся обычным, поэтому NOT IN с подзапросом, где встретился NULL, по-прежнему молча вернёт пустоту. Эта ловушка разобрана в статье про LEFT JOIN.

Типичные ошибки

UNION там, где нужен UNION ALL. Считаем общую выручку по обеим таблицам:

SELECT sum(amount) AS total FROM (
  SELECT amount FROM orders
  UNION
  SELECT amount FROM orders_archive
) t;

Ответ: 8300, и он неверный. UNION схлопнул одинаковые суммы: два заказа Бориса по 1200 стали одним, 900 из архива совпало с 900 из orders. Совпавшая сумма не означает дубликат заказа.

Правильный ответ зависит от того, что делать с заказом 10. Тот же запрос с UNION ALL по полным строкам (SELECT * вместо SELECT amount) даёт 10400 и считает заказ дважды, а UNION по полным строкам — 9500, потому что дедупликация идёт по всей строке, а не по одной колонке.

8300, 9500 и 10400 — три разных ответа на один вопрос, и выбирают между ними по смыслу задачи. Когда дубликаты в принципе невозможны (два непересекающихся месяца, две разные страны), UNION оплачивает сортировку впустую и ничего не меняет.

Разъехавшийся порядок колонок. Самая тихая из ошибок: типы совпали, сообщения нет, данные испорчены.

SELECT user_id, amount FROM orders
UNION ALL
SELECT amount, user_id FROM orders_archive;

В нижней половине под заголовком user_id стоят суммы заказов. Ошибки нет: обе колонки integer, база не возражает. Части UNION сопоставляются по позиции, имена ни на что не влияют. Поэтому пиши явный список полей в одинаковом порядке. SELECT * тут особенно уязвим: достаточно добавить колонку в одну из таблиц, и запрос упадёт уже на разном числе колонок.

Попытка отсортировать часть. Разобрана выше: запрос не выполнится. Обычно так делают, когда хотят «сначала живые заказы, потом архивные». Нужна колонка-метка, по которой сортируется уже готовое объединение: добавь в каждую часть константу (1 AS src и 2) и заверши запрос через ORDER BY src, id.

Частые вопросы

Работает ли UNION в MySQL и SQL Server

UNION и UNION ALL есть везде: PostgreSQL, MySQL, SQL Server, SQLite, Oracle. С INTERSECT и EXCEPT сложнее. SQL Server добавил их в версии 2005, MySQL — только в 8.0.31 (октябрь 2022), а SQLite поддерживает оба оператора, но без вариантов INTERSECT ALL и EXCEPT ALL. В Oracle тот же оператор исторически зовётся MINUS, а EXCEPT появился там как его синоним в 21c.

Можно ли объединить таблицы с разным набором колонок

Да, если добить недостающие до общего числа. Пустое место занимает NULL, источник помечают константой:

SELECT id, amount, created_at, NULL::text AS note FROM orders
UNION ALL
SELECT id, amount, created_at, 'архив' FROM orders_archive
ORDER BY id;

Каст NULL::text не обязателен: PostgreSQL выведет тип сам. Он просто делает намерение явным, чтобы через полгода никто не гадал, что за колонка.

В каком порядке выполняются несколько операторов подряд

Слева направо, за одним исключением: у INTERSECT приоритет выше, чем у UNION и EXCEPT, он срабатывает первым, как умножение перед сложением. В users идентификаторы с 1 по 4, в заказах с 7 по 15, пересечение пустое:

SELECT id FROM orders_archive
UNION
SELECT id FROM orders
INTERSECT
SELECT id FROM users
ORDER BY id;

Ответ: 7, 8, 9, 10. Сначала посчиталось orders INTERSECT users (ноль строк), потом архив объединился с пустотой. Обернёшь первые два запроса в скобки — получишь ноль строк, потому что пересечение будет считаться уже от объединения. Проще не держать приоритет в голове, а расставлять скобки.

Где потренироваться

Строки из двух источников почти всегда потом группируют и считают, поэтому дальше по маршруту идут агрегаты и соединения. Проверить, что тема улеглась, проще всего на задачах по SQL с решениями: их стоит набрать руками с автопроверкой, потому что прочитать чужое решение и написать своё — разные навыки.