UNION и UNION ALL
Объединение результатов нескольких SELECT-запросов
Теория
ОператорUNION объединяет результаты двух или нескольких отдельных SELECT-запросов в один общий результат. Каждый такой запрос сначала возвращает свой набор строк, а потом PostgreSQL собирает эти наборы вместе.
Таким образом, UNION работает с результирующими наборами строк, входящих в него SQL-запросов. Один запрос вернул свои строки, второй вернул свои строки, третий — свои, а затем они объединяются по вертикали, как будто один результат подклеивается под другой.
Это необходимо в ситуациях, когда нужные данные лежат в разных таблицах, но на выходе нужно получить один общий список (таблицы не обязательно должны быть разными, можно объединять строки двух выборок из одной и той же таблицы). Например, можно взять имена клиентов из таблицы users, затем имена сотрудников из таблицы workers и показать всё в одном общем результате. Тем самым мы получим имена клиентов и имена сотрудников в одной результирующей таблице.
Существует оператор UNION и оператор UNION ALL.
Обычный UNION убирает полные дубликаты строк. Если одна и та же строка попала в результат из обеих частей (дубликаты могут быть и в одной части), в итоговой выдаче она останется один раз.
UNION ALL работает почти так же, но повторы не убирает. Абсолютно все строки выводятся в результате. Если коротко: UNION объединяет и убирает дубликаты, а UNION ALL просто объединяет.
У этой операции есть несколько простых ограничений:
• количество столбцов, выводимых в объединяемых запросах должно быть одинаково;
• типы данных столбцов должны совпадать или быть совместимыми.
Таким образом, UNION работает с результирующими наборами строк, входящих в него SQL-запросов. Один запрос вернул свои строки, второй вернул свои строки, третий — свои, а затем они объединяются по вертикали, как будто один результат подклеивается под другой.
Это необходимо в ситуациях, когда нужные данные лежат в разных таблицах, но на выходе нужно получить один общий список (таблицы не обязательно должны быть разными, можно объединять строки двух выборок из одной и той же таблицы). Например, можно взять имена клиентов из таблицы users, затем имена сотрудников из таблицы workers и показать всё в одном общем результате. Тем самым мы получим имена клиентов и имена сотрудников в одной результирующей таблице.
Существует оператор UNION и оператор UNION ALL.
Обычный UNION убирает полные дубликаты строк. Если одна и та же строка попала в результат из обеих частей (дубликаты могут быть и в одной части), в итоговой выдаче она останется один раз.
UNION ALL работает почти так же, но повторы не убирает. Абсолютно все строки выводятся в результате. Если коротко: UNION объединяет и убирает дубликаты, а UNION ALL просто объединяет.
У этой операции есть несколько простых ограничений:
• количество столбцов, выводимых в объединяемых запросах должно быть одинаково;
• типы данных столбцов должны совпадать или быть совместимыми.
Синтаксис
SELECT column1, column2
FROM table_1
UNION
SELECT column1, column2
FROM table_2;
SELECT column1
FROM table_1
UNION ALL
SELECT column1
FROM table_2
ORDER BY column1;
Каждая часть конструкции UNION или UNION ALL — это обычный SELECT. Сначала пишется первый запрос, затем оператор объединения, потом второй запрос. Если нужно, таких частей может быть и больше.
PostgreSQL сопоставляет столбцы по позиции:
• первый столбец первого запроса объединяется с первым столбцом второго;
• второй — со вторым;
• третий — с третьим.
Поэтому здесь важны не названия столбцов, а структура результата. Можно назвать столбцы по-разному, но их количество, порядок и типы должны совпадать.
Имена итоговых столбцов, которые будут отображаться в результате, PostgreSQL берёт из первой части. Поэтому если нужен понятный заголовок результата, псевдоним лучше задать именно там.
Если типы столбцов не совпадают, их можно привести явно в нужный тип данных. Например, можно написать worker_id::text, если в верхней части число, а в нижней текст. Если этого не сделать PostgreSQL выведет ошибку.
Также будет ошибка если не совпадает количество столбцов. Но в этом случае при необходимости структуру можно выравнить вручную. Например, в одной объединяемой части можно вернуть реальный столбец, а в другой добавить NULL AS some_column или константу вроде 'нет данных'.
WHERE, вычисления, приведения типов и псевдонимы пишутся отдельно внутри каждого SELECT запроса. А вот ORDER BY и LIMIT обычно пишут в самом конце всей конструкции, когда общий результат уже собран.
Если написать ORDER BY или LIMIT внутри отдельной части, PostgreSQL снова выдаст ошибку. Но если сортировка или ограничение LIMIT все таки нужны именно до объединения, каждый SQL-запрос можно завернуть отдельно в скобки. В этом случае ошибки не будет и вся конструкция отработает.
Если объединяются данные из разных таблиц, полезно добавить столбец с источником строки. Например, в одной части вернуть 'user', а в другой 'worker'. Тогда итоговый результат легче читать и проверять.
PostgreSQL сопоставляет столбцы по позиции:
• первый столбец первого запроса объединяется с первым столбцом второго;
• второй — со вторым;
• третий — с третьим.
Поэтому здесь важны не названия столбцов, а структура результата. Можно назвать столбцы по-разному, но их количество, порядок и типы должны совпадать.
Имена итоговых столбцов, которые будут отображаться в результате, PostgreSQL берёт из первой части. Поэтому если нужен понятный заголовок результата, псевдоним лучше задать именно там.
Если типы столбцов не совпадают, их можно привести явно в нужный тип данных. Например, можно написать worker_id::text, если в верхней части число, а в нижней текст. Если этого не сделать PostgreSQL выведет ошибку.
Также будет ошибка если не совпадает количество столбцов. Но в этом случае при необходимости структуру можно выравнить вручную. Например, в одной объединяемой части можно вернуть реальный столбец, а в другой добавить NULL AS some_column или константу вроде 'нет данных'.
WHERE, вычисления, приведения типов и псевдонимы пишутся отдельно внутри каждого SELECT запроса. А вот ORDER BY и LIMIT обычно пишут в самом конце всей конструкции, когда общий результат уже собран.
Если написать ORDER BY или LIMIT внутри отдельной части, PostgreSQL снова выдаст ошибку. Но если сортировка или ограничение LIMIT все таки нужны именно до объединения, каждый SQL-запрос можно завернуть отдельно в скобки. В этом случае ошибки не будет и вся конструкция отработает.
Если объединяются данные из разных таблиц, полезно добавить столбец с источником строки. Например, в одной части вернуть 'user', а в другой 'worker'. Тогда итоговый результат легче читать и проверять.
Примеры
1. Собираем единый список имён из двух таблиц
Обе части возвращают по одному текстовому столбцу, поэтому их можно спокойно объединить. Обычный UNION уберёт полные дубликаты имён, если они встретятся и в users, и в workers.
🔄 Попробуйте изменить запрос:
- • Уберите псевдоним person_name из второго SELECT, а потом и из первого, и проверьте, как это повлияет на итоговое имя столбца
- • Задайте в первой части псевдоним client_name, а во второй worker_name, затем проверьте, какое имя столбца останется в результате
2. Сохраняем повторы через UNION ALL
Здесь повторы не удаляются. Если один и тот же пользователь встречается в обеих выборках, в результате он тоже появится несколько раз. Это задача для UNION ALL, а не для обычного UNION.
🔄 Попробуйте изменить запрос:
- • Замените UNION ALL на обычный UNION и обратите внимание, как изменится количество строк в результате
3. Объединяем значения из разных справочников и сразу помечаем источник
Обе части запроса возвращают по два текстовых столбца: сам столбец из таблицы и признак источника. Этот приём полезен, когда нужно собрать единый список значений и не потерять понимание, откуда пришла каждая строка.
🔄 Попробуйте изменить запрос:
- • Попробуйте заменить UNION на UNION ALL
- • Сделайте источник короче: 'dep' и 'cat'
4. В каждой ветке свой фильтр, а сортировка одна общая
Здесь каждый запрос фильтруется отдельно. В первом остаются пользователи с указанным городом, во втором — работники с высокой зарплатой. Потом PostgreSQL объединяет оба результата.
🔄 Попробуйте изменить запрос:
- • Измените фильтр во втором запросе на salary >= 100000
- • Добавьте в первый запрос фильтр по registration_date
5. Если LIMIT нужен внутри части запроса, используем скобки
Здесь ORDER BY и LIMIT применяются не ко всему результату сразу, а к каждой части отдельно. Без скобок PostgreSQL такую запись не примет. Скобки превращают каждую часть в отдельный подзапрос, и после этого их уже можно объединять.
🔄 Попробуйте изменить запрос:
- • Уберите скобки у обеих частей и выполните запрос, чтобы увидеть ошибку PostgreSQL
- • Оставьте скобки, но уберите внутренние LIMIT и перенесите один LIMIT в конец всей конструкции, затем сравните результат
6. Приводим типы к общему виду перед объединением
Здесь в первой части запроса возвращается числовой идентификатор работника, а во второй — имя пользователя, то есть текст. Чтобы PostgreSQL смог объединить такие столбцы, число в первой части явно приведено к text. Это объясняет ситуацию, когда типы данных сами по себе разные, но их можно привести к одному виду.
🔄 Попробуйте изменить запрос:
- • Уберите явное приведение ::text у worker_id и выполните запрос, чтобы увидеть ошибку
7. Выравниваем количество столбцов через NULL
В первой части запроса возвращаются два столбца: имя и дополнительная информация. Во второй части городов нет, поэтому недостающий столбец добавлен через NULL. Так структура результата становится одинаковой, и объединение выполняется без ошибки.
🔄 Попробуйте изменить запрос:
- • Замените NULL на строковую константу 'нет города'
- • Попробуйте убрать второй столбец из второго запроса и посмотрите, что получится
Типичные ошибки
Разное количество столбцов в частях запроса
В одной части запроса нельзя вернуть два столбца, а в другой один. Структура результата должна совпадать.
Путают UNION и UNION ALL
UNION убирает полные дубликаты строк, а UNION ALL сохраняет их. Если повторы важны по смыслу, обычный UNION даст уже другой результат.
Ставят ORDER BY внутри каждой части отдельно
В стандартном сценарии сортировка должна стоять в конце всей конструкции. Сначала объединяем, потом сортируем общий результат.
Пишут LIMIT внутри части запроса без скобок
LIMIT, как и ORDER BY, обычно ставят в конце всей конструкции. Если ограничение нужно применить к отдельной части, её нужно обернуть в скобки.
Смотрят только на названия столбцов и забывают про порядок
PostgreSQL сопоставляет столбцы по позиции. Поэтому важно не только количество, но и порядок: первый с первым, второй со вторым.
Не выравнивают структуру результата
Если в одной части запроса столбцов больше, чем в другой, запрос не выполнится. В таких случаях можно добавить недостающий столбец через NULL или константу, чтобы количество столбцов совпало.
Не приводят типы, когда это нужно
Если одна часть запроса возвращает текст, а другая число или дату в неудобном для объединения виде, часто помогает явное приведение, например worker_id::text.
Думают, что итоговые имена столбцов берутся из любой части запроса
Обычно ориентироваться нужно на первую часть. Именно она задаёт итоговые имена столбцов, с которыми потом работает внешний ORDER BY.
Практика
Проверь себя
Ответьте на вопросы, чтобы закрепить материал:
1
Чем UNION отличается от UNION ALL?
2
Что обязательно должно совпадать у двух SELECT-запросов в объединении?
3
Где обычно ставится ORDER BY в запросе с UNION?
4
Откуда обычно берутся итоговые имена столбцов в результате объединения?
5
Первый запрос вернул один столбец со значениями: Москва, Казань, Самара, Казань. Второй запрос вернул один столбец со значениями: Казань, Омск, Самара. Сколько строк вернёт результат после применения UNION? Введите только число.
6
Первый запрос вернул один столбец со значениями: 1, 1, 2, 5. Второй запрос вернул один столбец со значениями: 2, 2, 3. Сколько строк вернёт результат после применения UNION ALL? Введите только число.
7
Для приветственного списка нужно вывести имена и фамилии клиентов, которые зарегистрировались в июле 2022 года, и объединить их с именами и фамилиями сотрудников, которые в этом же месяце и году устроились на работу. Повторяющиеся строки в итоговом списке оставлять не нужно. Используйте таблицы users и workers. Отсортируйте результат по first_name и last_name.
8
Для контрольной выборки нужно собрать общий список order_id: отдельно все заказы из таблицы orders, оформленные в июле 2024 года, и отдельно все заказы из таблицы order_items, где количество товара не меньше 8. Если один и тот же order_id встречается в обеих частях, его нужно сохранить повторно. Отсортируйте результат по order_id.