Оба оператора складывают результаты двух запросов друг на друга, поэтому SQL UNION vs UNION ALL может показаться второстепенным выбором. Это не. Одно лишнее слово меняет как ваш результат, так и то, насколько усердно приходится работать базе данных.

Мы выполнили каждый запрос здесь, в SQLite 3.50, и показанные результаты и время получены из этих запусков. Основное поведение — стандартный SQL для основных баз данных. Однако время будет отличаться в зависимости от вашей базы данных, данных и оборудования, поэтому воспринимайте наше как иллюстрацию.

Короткий ответ

UNION объединяет два набора результатов и возвращает только разные строки. UNION ALL объединяет их и возвращает все, включая дубликаты. В этом вся разница, и оставшаяся часть статьи посвящена тому, почему это важно.

UNION против UNION ALL бок о бок

Пример данных

Представьте себе магазин, который хранит онлайн- и офлайн-заказы в отдельных таблицах. Обратите внимание, что у Аши есть два одинаковых онлайн-заказа и один идентичный заказ в магазине.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

Посмотрите разницу

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

Пять входных строк стали тремя с UNION, а все пять остались с UNION ALL. Две вещи, на которые следует обратить внимание:

• UNION удалил дубликаты из всего результата, включая те, которые находятся в первой таблице. Это не только сравнение между таблицами.
• Строка считается дубликатом только в том случае, если каждый выбранный столбец совпадает.

Если вам действительно нужен отдельный список, UNION будет совершенно правильным. Это дает каждому клиенту, совершившему покупку через любой канал:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Тотальная неправильная ошибка

Вот почему выбор действительно имеет значение. Предположим, вам нужен общий доход. Сумма заказов выше составляет 800. Посмотрите, что дает каждый оператор:

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

Версия UNION ошибочна на 200. Поскольку мы выбрали только столбец total, три отдельных заказа на 100 рупий выглядели как дубликаты и схлопнулись в один. Сообщения об ошибке нет, а число 600 выглядит вполне правдоподобным. Подобные тихие ошибки могут оставаться в финансовом отчете месяцами.

Правила, которым должны следовать оба

Оба оператора имеют одинаковые требования:

1. Каждый SELECT должен возвращать то же количество столбцов.
2. Столбцы сопоставляются по позиции, а не по имени, поэтому сохраняйте их в том же порядке.
3. Типы данных в каждой позиции должны быть совместимы.
4. Имена столбцов результатов берутся из first SELECT.
5. ORDER BY идет в самом конце и применяется ко всему результату.

Нарушение первого правила приводит к ошибке. Вот сообщение от SQLite:

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

Второе правило более опасно, поскольку оно может не выполниться без ошибки. Если вы поменяете местами два столбца одного и того же типа, например first_name и last_name, запрос выполнится, и данные будут получены неверными. SQLite также небрежно относится к типам, в то время как такие базы данных, как PostgreSQL, более строги и могут требовать явного указания CAST.

Пометьте источник

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

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

Как только вашим данным будет присвоена метка, вы сможете сгруппировать их, чтобы составить отчет о продажах по каналам, используя GROUP BY. Если вам нужно объединить несколько шагов, оберните объединение в CTE, чтобы оно было читабельным.

Производительность: что произошло в нашем тесте

Чтобы удалить дубликаты, база данных должна сравнить строки, обычно путем их сортировки или хеширования. UNION ALL может пропустить этот шаг. Мы создали две таблицы по 500 000 строк в каждой, содержащие случайные числа от 0 до 50 000, поэтому дубликатов было много. Мы запускали каждый запрос несколько раз и фиксировали лучшее время.

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

Огромный разрыв отчасти объясняется тем, что SQLite может подсчитать это значение, не читая каждую строку, поэтому, пожалуйста, не читайте это как «в тысячи раз быстрее». Тем не менее, это отражает основную идею: UNION должен был проделать реальную работу, а UNION ALL — нет.

Затем мы извлекли все строки в Python, и результат изменился:

Здесь UNION ALL заняло больше времени, поскольку выдало в двадцать раз больше строк. Дедупликация не является причиной замедления процесса. Обработка миллиона строк была. Итак, честное правило заключается в том, что UNION ALL дешевле вычислить в базе данных, но размер результата тоже имеет значение. Многие онлайн-диаграммы сводят это к «UNION ALL всегда быстрее», и наш тест показывает, почему это не так просто.

Не делайте выбор только по скорости. Сначала выберите оператора, дающего правильный ответ, а затем настройтесь. Если запрос выполняется медленно, прочитайте наше руководство по оптимизации SQL-запросов.

Когда использовать каждый

Используйте UNION ALL, когда:

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

Используйте UNION, когда:

• Вам нужен уникальный список, например каждый адрес электронной почты в двух системах.
• Перекрывающиеся источники неоднократно содержат один и тот же факт.

Считайте UNION ALL значением по умолчанию и используйте UNION сознательно. Добавление предложения WHERE в каждую ветку для ранней фильтрации помогает в обоих случаях.

ПЕРЕСЕЧЕТЬ и ИСКЛЮЧИТЬ

Два связанных оператора множества отвечают на разные вопросы. Оба следуют одним и тем же правилам столбцов:

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle традиционно использует MINUS, а не EXCEPT, и поддержка этих операторов немного различается в зависимости от базы данных и версии.

СОЮЗ против ПРИСОЕДИНЯЙТЕСЬ

Их часто путают, но они объединяют данные в разных направлениях:

Распространенные ошибки

• Использование UNION по привычке. Это может стоить вам скорости или удалять допустимые строки.
• Выбрано слишком мало столбцов. Если не указать идентификатор, отдельные строки будут выглядеть одинаково.
• Несоответствующий порядок столбцов. Значения могут оказаться в неправильном столбце без каких-либо ошибок.
• Добавление ORDER BY в каждую ветку. Сортировка один раз, в конце.
• Использование UNION для исправления неправильного соединения. Если из-за соединения появляются дубликаты, исправьте соединение.
• При условии порядка результатов. Без ORDER BY порядок строк не гарантируется ни для одного оператора.

Итоги

Каждый раз задавайте один вопрос: идентичные строки — это один и тот же факт или отдельные факты? Если они разделены или перекрытие невозможно, используйте UNION ALL. Если вам нужен уникальный список, используйте UNION. Чтобы проверить свои собственные отчеты, найдите в своем коде UNION рядом с SUM или COUNT и убедитесь, что эти суммы действительно верны.

Часто задаваемые вопросы

Продолжайте учиться