Объединение UNION и UNION ALL в SQL – описание и примеры
Пришло время поговорить об объединении данных по средствам конструкции union и union all, так как это иногда бывает очень полезно, и без использования такой конструкции бывает порой не обойтись. Примеры будем писать в СУБД MSSQL 2008, используя язык SQL.
И начать хотелось бы с того, что мы с Вами уже рассматривали много примеров написания запросов на SQL, например, оператор select языка SQL, или использование строковых функций SQL, также рассматривали программирование как на plpgsql так и на transact-sql, например, Как написать функцию на PL/pgSQL и Transact-sql – Табличные функции и временные таблицы соответственно.
Я не просто так указал вышеперечисленные статьи, а указал я их, потому, что для более лучшего понимания и усвоения сегодняшнего урока, необходимо начальные знания (это сайт для начинающих программистов), которые как раз Вы можете получить из вышеупомянутого материала.
И так приступим. И для начала давайте рассмотрим, что же это за операторы union и union all.
Что такое UNION и UNION ALL в SQL?
- UNION – это оператор SQL для объединения результирующего набора данных нескольких запросов, и данный оператор выводит только уникальные строки в запросах, т.е. например, Вы объединяете два запроса и в каждом из которых есть одинаковые данные, другими словами полностью идентичные, и оператор union объединит их в одну строку для того чтобы не было дублей;
- UNION ALL – это оператор SQL для объединения результирующего набора данных нескольких запросов, а вот данный оператор, выведет уже абсолютно все строки, даже дубли.
Необходимые условия для операторов union и union all
- Набор полей должен быть одинаковый во всех запросах, т.е. количество полей в каждом запросе, который будет объединяться по средствам конструкции union или union all, должно быть одинаковое;
- Типы данных полей также должны совпадать в каждом запросе, т.е. например, если Вы захотите написать один запрос, в котором будет тип данных int а во втором запросе тип данных varchar то у Вас запрос не выполнится а окно запроса выведет ошибку;
- В случае сортировки оператор order by можно указать только после последнего запроса.
Теперь давайте поговорим о том, в каких случаях нам может понадобиться использование этих операторов. Ну, например, у Вас есть несколько баз со схожей структурой, каждая из которых создана, например, для какого-нибудь филиала, а Вам необходимо объединить эти данные для предоставления отчетности по всем филиалам руководству и самое простое как это можно сделать, это написать запросы на SQL, каждый из которых будет обращаться к разным базам, и через конструкцию union или union all объединить их. Также иногда бывает необходимо объединить данные в одной базе таким образом, что обычными объединениями это не реализовать и приходится использовать union. Почему я говорю «приходится» да потому что данная конструкция значительно увеличивает время выполнения запроса, если например данных очень много, и злоупотреблять ею не нужно.
Хватит теории, переходим к практике.
Примечание! Как уже говорилось, запросы будем писать в Management Studio для SQL Server 2008
Примеры использования union и union all
Для начала создадим две простых таблицы test_table и test_table_2
CREATE TABLE [test_table]( [id] [bigint] IDENTITY(1,1) NOT NULL, [number] [numeric](18, 0) NULL, [text] [varchar](50) NULL, CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO --и вторая таблица CREATE TABLE [test_table_2]( [id] [bigint] IDENTITY(1,1) NOT NULL, [number] [numeric](18, 0) NULL, [text] [varchar](50) NULL, CONSTRAINT [PK_test_table_2] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Они одинаковые для примера, только разные названия. Я заполнил их вот такими данными:
Теперь давайте напишем запрос, который объединит результирующие данные в одни, например через union. Синтаксис очень прост:
Запрос 1 union Запрос 2 union Запрос 3 и т.д.
select number, text from test_table union select number, text from test_table_2
Как Вы видите, вывелось всего 5 строк, так как у нас первая строка в первом запросе и первая строка во втором запросе одинаковые, поэтому они объединились.
Теперь давайте объединим через union all
select number, text from test_table union all select number, text from test_table_2
Здесь уже вывелись все строки, так как мы указали union all.
А теперь давайте рассмотрим, какие могут быть ошибки даже в этом простом запросе. Например, мы перепутали последовательность полей:
Или мы в первом запросе указали дополнительное поле, а во втором этого не сделали.
Также, например, при использовании order by:
Здесь мы указали сортировку в каждом запросе, а нужно было только в последнем, например:
select number, text from test_table union all select number, text from test_table_2 order by number
И напоследок, хотел рассказать об одной хитрости, которую можно использовать тогда когда, например, все-таки необходимо вывести в одном запросе какое-то поле, а в других его нет или просто оно не нужно, для этого можете написать вот такой запрос:
select id ,number, text from test_table union all select '', number, text from test_table_2
т.е. как Вы видите просто там, где должно быть поле ставить пусто и запрос отлично отработает, например:
И еще один небольшой совет, так как запросы при объединении через union довольно обширные, то лучше на их основе создать представление (Views), в случае если данный запрос Вам требуется постоянно, и уже к этому представлению обращаться каждый раз, когда требуется, а зачем нужны представления мы с Вами уже рассматривали вот здесь – Что такое представления и зачем они нужны.
Наверное, все, что я хотел рассказать о конструкции union и union all языка SQL я рассказал, если есть вопросы по использованию этих операторов, задавайте их в комментариях. Удачи!
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
Секция UNION
Вы можете использовать UNION в двух режимах: UNION ALL или UNION DISTINCT .
Если UNION используется без указания ALL или DISTINCT , то его поведение определяется настройкой union_default_mode . Разница между UNION ALL и UNION DISTINCT в том, что UNION DISTINCT выполняет явное преобразование для результата объединения. Это равнозначно выражению SELECT DISTINCT из подзапроса, содержащего UNION ALL .
Чтобы объединить любое количество SELECT запросов путем объединения их результатов, вы можете использовать UNION . Пример:
SELECT CounterID, 1 AS table, toInt64(count()) AS c FROM test.hits GROUP BY CounterID UNION ALL SELECT CounterID, 2 AS table, sum(Sign) AS c FROM test.visits GROUP BY CounterID HAVING c > 0 Результирующие столбцы сопоставляются по их индексу (порядку внутри SELECT ). Если имена столбцов не совпадают, то имена для конечного результата берутся из первого запроса.
При объединении выполняется приведение типов. Например, если два запроса имеют одно и то же поле с не- Nullable и Nullable совместимыми типами, полученные в результате UNION данные будут иметь Nullable тип.
Запросы, которые являются частью UNION , могут быть заключены в круглые скобки. ORDER BY и LIMIT применяются к отдельным запросам, а не к конечному результату. Если вам нужно применить преобразование к конечному результату, вы можете разместить все объединенные с помощью UNION запросы в подзапрос в секции FROM.
Если используете UNION без явного указания UNION ALL или UNION DISTINCT , то вы можете указать режим объединения с помощью настройки union_default_mode, значениями которой могут быть ALL , DISTINCT или пустая строка. Однако если вы используете UNION с настройкой union_default_mode , значением которой является пустая строка, то будет сгенерировано исключение. В следующих примерах продемонстрированы результаты запросов при разных значениях настройки.
SET union_default_mode = 'DISTINCT'; SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2; SQL оператор UNION ALL
В этом учебном материале вы узнаете, как использовать SQL оператор UNION ALL с синтаксисом и примерами.
Описание
SQL оператор UNION ALL используется для объединения результирующих наборов из 2 или более операторов SELECT. Он не удаляет повторяющиеся строки между различными операторами SELECT (возвращаются все строки).
Каждый оператор SELECT в UNION ALL должен иметь одинаковое количество полей в наборах результатов с одинаковыми типами данных.
В чем разница между UNION и UNION ALL?
- Оператор UNION удаляет повторяющиеся строки.
- UNION ALL не удаляет повторяющиеся строки
Синтаксис
Синтаксис для оператора UNION ALL в SQL.
SELECT expression1, expression2, . expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, . expression_n
FROM tables
[WHERE conditions];
Параметры или аргумент
expression1 , expression2 , expression_n Столбцы или расчеты, которые вы хотите получить tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица WHERE conditions Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны
Примечание
- В обоих запросах SELECT должно быть одинаковое количество выражений
- Соответствующие выражения должны иметь одинаковый тип данных в запросах SELECT. Например: expression1 должен иметь одинаковый тип данных как в первом, так и во втором операторе SELECT
- Смотрите также оператор UNION
Пример — одиночное поле с тем же именем
Давайте посмотрим, как использовать SQL оператор UNION ALL, который возвращает одно поле. В этом простом примере поле в обоих операторах SELECT будет иметь одинаковое имя и тип данных.
Например.
SELECT supplier_id
FROM suppliers
SELECT supplier_id
FROM orders
ORDER BY supplier_id;
Этот SQL пример UNION ALL будет возвращать supplier_id несколько раз в наборе результатов, если это же значение появилось в таблицах suppliers и orders . SQL оператор UNION ALL не удаляет дубликаты. Если вы хотите удалить дубликаты, попробуйте использовать оператор UNION.
Теперь давайте рассмотрим этот пример, далее приведем некоторые данные.
Если у вас была таблица suppliers , заполненная следующими записями.
| supplier_id | supplier_name |
|---|---|
| 1000 | Microsoft |
| 2000 | Oracle |
| 3000 | Apple |
| 4000 | Samsung |
И таблица orders заполнена следующими записями.
| order_id | order_date | supplier_id |
|---|---|---|
| 2019-07-01 | 2000 | |
| 2019-07-01 | 6000 | |
| 2019-07-02 | 7000 | |
| 2019-07-03 | 8000 |
И вы выполнили следующий оператор UNION ALL.
SELECT supplier_id
FROM suppliers
SELECT supplier_id
FROM orders
ORDER BY supplier_id;
Вы получите следующие результаты.
| supplier_id |
|---|
| 1000 |
| 2000 |
| 2000 |
| 3000 |
| 4000 |
| 6000 |
| 7000 |
| 8000 |
Как видно из этого примера, UNION ALL взял все значения supplier_id из таблицы suppliers , а также из таблицы orders и возвратил комбинированный набор результатов. Дубликаты не были удалены, как вы можете видеть по значению supplier_id 2000, которое дважды появляется в наборе результатов.
Пример — разные имена полей
Нет необходимости, чтобы соответствующие столбцы в каждом операторе SELECT имели одинаковые имена, но они должны быть с одинаковыми соответствующими типами данных.
Если у вас нет одинаковых имен столбцов в операторах SELECT, это становится немного сложнее, особенно если вы хотите упорядочить результаты запроса, используя оператор ORDER BY.
Давайте посмотрим, как использовать оператор UNION ALL с разными именами столбцов, и упорядочить результаты запроса.
Например.
UNION против UNION ALL: разница и сравнение
Операторы SQL UNION и UNION ALL используются для объединения или связывания двух или более наборов результатов. SQL расшифровывается как Structured Query Language, язык для баз данных.
Это язык программирования, специально разработанный для хранения, извлечения, управления или манипулирования данными. Он также позволяет обрабатывать информацию с помощью таблиц.
Основные выводы
- UNION объединяет два или более оператора SELECT и удаляет повторяющиеся записи, а UNION ALL объединяет результат, не удаляя дубликаты.
- UNION работает медленнее из-за необходимости фильтровать дубликаты, тогда как UNION ALL обеспечивает более высокую производительность, поскольку не требует дополнительной обработки.
- UNION обеспечивает отдельный набор результатов, тогда как UNION ALL может возвращать больший набор результатов, содержащий все записи из объединенных таблиц.
СОЮЗ против СОЮЗА ВСЕХ
Разница между UNION и UNION ALL заключается в том, что UNION сохраняет только уникальные записи и удаляет дубликаты при объединении данных. Напротив, UNION ALL поддерживает все записи из исходного набора, включая повторяющиеся записи.
Оператор UNION в SQL действует как один из операторов SET. Он генерируется для объединения результатов нескольких таблиц или нескольких SQL запросы. Оператор объединяет таблицы и дает единый набор результатов. Команда UNION может объединять набор из двух или более выбранных операторов.
Команда UNION ALL также помогает объединять более двух операторов. Но этот отличается от предыдущего. UNION ALL допускает дублирование значений в результирующем наборе. Он не отфильтровывает одни и те же операторы и объединяет все значения в результирующем наборе.
Сравнительная таблица
Что такое СОЮЗ?
UNION — это тип оператора SET в SQL, и он используется для объединения нескольких операторов SQL. UNION объединяет результаты, сгенерированные несколькими таблицами, и превращает их в один результат. Результирующий набор содержит все строки, но повторяющиеся удаляются.
Чтобы использовать оператор UNION, выбранные операторы должны иметь одинаковое количество столбцов. И данные в этих столбцах должны быть совместимы с оператором. Порядок столбцов также должен быть проверен и сохранен.
Оператор UNION можно использовать для двух или более таблиц. Однако его функционирование требует времени, поскольку он устраняет повторяющиеся операторы. Несмотря на это, оператор UNION является предпочтительным среди пользователей, поскольку он помогает исключить дубликаты из результирующего набора.
Имя столбца, выбранное в разных запросах SELECT, должно быть в одном и том же порядке для правильного функционирования оператора. Однако имя столбца первого выбранного запроса будет именем результата.
Оператор UNION объединяет таблицы по вертикали. Для того чтобы оператор функционировал, основное требование состоит в том, чтобы поля SELECT имели одинаковое количество полей, а их тип данных также должен быть схожим. Благодаря своим особенностям пользователи предпочитают оператор UNION.
Что такое СОЮЗ ВСЕХ?
UNION ALL также используется для объединения данных двух разных наборов SQL. Это один из четырех операторов SQL SET, который в основном используется для объединения результирующего набора из двух или более наборов SQL. Он очень похож на оператор UNION, но есть и нюансы.
Основным требованием для использования оператора UNION ALL является то, что количество столбцов в запросах SELECT должно быть одинаковым. Их типы данных также должны быть совместимы и одинаковы. Кроме того, расположение столбцов и данных должно быть одинаковым, чтобы слияние могло быть выполнено эффективно.
UNION ALL похож на UNION и используется для объединения таблиц данных. Но результирующие наборы при использовании UNION ALL отличаются. Результат содержит все строки и столбцы из выбранных операторов.
UNION ALL не удаляет одни и те же запросы из результата. Вместо этого он объединяет все выбранные записи и не удаляет повторяющиеся. В этом основное различие между UNION и UNION ALL.
Поскольку UNION ALL не требует устранения повторяющихся операторов, он работает сравнительно лучше. Однако пользователи обычно не предпочитают использовать этого оператора.
Основные различия между UNION и UNION ALL
- Результаты, сгенерированные UNION, содержат уникальные операторы, тогда как UNION ALL также добавляет повторяющиеся записи и просто объединяет все записи в результирующем наборе.
- Пользователи предпочитают оператор UNION, хотя он и медленнее. UNION ALL не нравится пользователям. Эта склонность, вероятно, может быть из-за лучших характеристик UNION.
- Оператор UNION работает сравнительно медленнее по сравнению с оператором UNION ALL, потому что для устранения повторяющихся операторов требуется время.
- Оператор UNION имеет эту функцию по умолчанию для устранения повторяющихся операторов, но оператор UNION ALL не имеет такой функции.
- Оператор UNION не работает со столбцами с текстовым типом данных. Но UNION ALL работает со всеми типами столбцов данных.
Рекомендации
- https://link.springer.com/chapter/10.1007/978-1-4302-3229-2_4
- https://ieeexplore.ieee.org/abstract/document/6030237/
Один запрос?
Я приложил столько усилий, чтобы написать этот пост в блоге, чтобы предоставить вам ценность. Это будет очень полезно для меня, если вы подумаете о том, чтобы поделиться им в социальных сетях или со своими друзьями/родными. ДЕЛИТЬСЯ ♥️