Пришло время поговорить об объединении данных по средствам конструкции 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 я рассказал, если есть вопросы по использованию этих операторов, задавайте их в комментариях. Удачи!