Оператор для работы с наборами UNION. Объединение UNION и UNION ALL в SQL – описание и примеры Объединение union

Я уже писал о . И там вывод одной таблицы зависил от содержимого другой. Но бывает нужно, когда требуется полная независимость вывода одной таблицы от другой. Всё, что хочется - это просто в одном запросе вытащить записи сразу из нескольких таблиц , не более того. И вот для этого используется в SQL ключевое слово UNION .

Давайте с Вами разберём SQL-запрос с использованием UNION :

SELECT `login`, `amount` FROM `employers` UNION SELECT `login`, `amount` FROM `staff`;

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

Таких UNION-частей может быть очень много, но самое главное после последнего UNION надо обязательно поставить точку с запятой .

Ещё одной хорошей особенностью UNION является отсутствие повторений. Например, если один и тот же человек находится и среди сотрудников, и среди работодателей, разумеется, с той же самой суммой на счету, то в выборке он будет не 2 раза, а только 1 , что, как правило, и требуется. А если всё-таки нужны повторения, то тогда есть UNION ALL :

SELECT `login`, `amount` FROM `employers` UNION ALL SELECT `login`, `amount` FROM `staff`;

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

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

1. Использование оператора UNION

Запросы в языке SQL комбинируются с помощью оператора UNION . Для этого необходимо указать каждый запрос SELECT и разместить между ними ключевое слово UNION . Ограничений по количеству использованного оператора UNION в одном общем запросе нет. В предыдущем разделе мы отмечали, что Access не имеет возможности создавать полное внешнее объединение , теперь мы посмотрим, как можно этого достичь через оператор UNION .

SELECT *
FROM Sumproduct LEFT JOIN Sellers ON Sumproduct.City = Sellers.City
UNION
SELECT *
FROM Sumproduct RIGHT JOIN Sellers ON Sumproduct.City = Sellers.City

Видим, что запрос отобразил как все колонки из первой таблицы - так и с другой, независимо от того, все ли записи имеют соответствия в другой таблице.

Также стоит отметить, что во многих случаях вместо UNION мы можем использовать предложение WHERE со многими условиями, и получать аналогичный результат. Однако из-за UNION записи выглядят более лаконичными и понятными. Также необходимо соблюдать определенные правила при написании комбинированных запросов:

• запрос UNION должен включать два и более операторов SELECT , отделенных между собой ключевым словом UNION (т.е. если в запросе используется четыре оператора SELECT, то должно быть три ключевых слова UNION)
• каждый запрос в операторе UNION должен иметь одни и те же столбцы, выражения или статистические функции, которые, к тому же, должны быть перечислены в одинаковом порядке
• типы данных столбцов должны быть совместимыми. Они не обязательно должны быть одного типа, однако обязаны иметь подобный тип, чтобы СУБД могла их однозначно преобразовать (например, это могут быть различные числовые типы данных или различные типы даты).

2. Включение или выключение повторяющихся строк

Запрос с UNION автоматически удаляет все повторяющиеся строки из набора результатов запроса (то есть, ведет себя как предложения WHERE с несколькими условиями в одном операторе SELECT ). Такое поведение оператора UNION по умолчанию, но при желании мы можем изменить это. Для этого нам следует использовать оператор UNION ALL вместо UNION .

3. Сортировка результатов комбинированных запросов

Результаты выполнения оператора SELECT сортируются с помощью предложения ORDER BY . При комбинировании запросов с помощью UNION только одно предложение ORDER BY может быть использовано, и оно должно быть проставлено в последнем операторе SELECT . Действительно, на практике нет особого смысла часть результатов сортировать в одном порядке, а другую часть - в другом. Поэтому несколько предложений ORDER BY применять не разрешается.

Union -ы (объединения) используют в двух случаях:

Для создания «универсального» типа данных , способного хранить не единственный, а один из предопределённых типов. Для этого к объединению добавляют целочисленное поле, указывающее тип хранимых в настоящий момент данных:

Struct variant { union tag_value { int intValue; float floatValue } value; unsigned storedType; };

Один из примеров подобного применения в реальной жизни - структура VARIANT из Windows API.

Иными словами, это предшественник современных boost::variant , QVariant и т. д. Однако вышеперечисленные классы могут хранить в себе непримитивные типы (с конструкторами, деструкторами и операторами копирования), а union - нет.

1. Для преобразования между несовместимыми типами. Традиционно для этих целей используют оператор преобразования (T) , либо reinterpret_cast<> . Однако эти способы опасны нарушением strict aliasing rule и, как результат, порождением неопределённого (то есть непредсказуемого) поведения.

   Правильные способы преобразования - это либо memcpy (подобный вызов которого выбрасывается компилятором), либо использование union -а.

   UPD: Внимание! Преобразование через union является допустимым только в Си , но не в C++. В ответе на вопрос «Accessing inactive union member and undefined behavior?» приводятся отсылки на следующие пункты стандартов:

   • 6.5.2.3 Структуры и члены объединений

     95) Если поле, используемое для чтения содержимого объекта-объединения, не является полем, использованным ранее для записи значения в этот объект, требуемая часть внутреннего представления объекта интерпретируется в соответствием с представлением затребованного типа согласно 6.2.6 (данный процесс известен также как type punning). Это представление может приводить к неопределённому поведению.

     6.5.2.3 Structure and union members

     95) If the member used to read the contents of a union object is not the same as the member last used to store a value in the object, the appropriate part of the object representation of the value is reinterpreted as an object representation in the new type as described in 6.2.6 (a process sometimes called ‘‘type punning’’). This might be a trap representation.

   • c++11 (нет явного разрешения type punning-а)

     9.5 Объединения

     В объединении в каждый момент времени может быть активно только одно нестатическое поле; вследствие этого в объединении в любой момент времени может находиться не более одного значения.

     9.5 Unions

     In a union, at most one of the non-static data members can be active at any time, that is, the value of at most one of the non-static data members can be stored in a union at any time.

Пришло время поговорить об объединении данных по средствам конструкции 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

1. Набор полей должен быть одинаковый во всех запросах, т.е. количество полей в каждом запросе, который будет объединяться по средствам конструкции union или union all, должно быть одинаковое;
2. Типы данных полей также должны совпадать в каждом запросе, т.е. например, если Вы захотите написать один запрос, в котором будет тип данных int а во втором запросе тип данных varchar то у Вас запрос не выполнится а окно запроса выведет ошибку;
3. В случае сортировки оператор order by можно указать только после последнего запроса .

Теперь давайте поговорим о том, в каких случаях нам может понадобиться использование этих операторов. Ну, например, у Вас есть несколько баз со схожей структурой, каждая из которых создана, например, для какого-нибудь филиала, а Вам необходимо объединить эти данные для предоставления отчетности по всем филиалам руководству и самое простое как это можно сделать, это написать запросы на SQL, каждый из которых будет обращаться к разным базам, и через конструкцию union или union all объединить их. Также иногда бывает необходимо объединить данные в одной базе таким образом, что обычными объединениями это не реализовать и приходится использовать union. Почему я говорю «приходится» да потому что данная конструкция значительно увеличивает время выполнения запроса, если например данных очень много, и злоупотреблять ею не нужно.

Хватит теории, переходим к практике.

Примечание! Как уже говорилось, запросы будем писать в Management Studio для SQL Server 2008

Примеры использования union и union all

Для начала создадим две простых таблицы test_table и test_table_2

CREATE TABLE ( IDENTITY(1,1) NOT NULL, (18, 0) NULL, (50) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO SET ANSI_PADDING OFF GO --и вторая таблица CREATE TABLE ( IDENTITY(1,1) NOT NULL, (18, 0) NULL, (50) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON 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 и union all языка SQL я рассказал, если есть вопросы по использованию этих операторов, задавайте их в комментариях. Удачи!

Синтаксис

Оператор указывается между запросами. В упрощенном виде это выглядит следующим образом:

< запрос1 > UNION [ ALL ] < запрос2 > UNION [ ALL ] < запрос3 > ..... ;

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

Необходимо отметить, что UNION сам по себе не гарантирует порядок строк. Строки из второго запроса могут оказаться в начале, в конце или вообще перемешаться со строками из первого запроса. В случаях, когда требуется определенный порядок, необходимо использовать выражение ORDER BY .

Правила использования

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

• Число и порядок извлекаемых столбцов должны совпадать во всех объединяемых запросах;
• Типы данных в соответствующих столбцах должны быть совместимы.

Определения столбцов, данные из которых извлекаются в объединяемых запросах, не должны совпадать, однако должны быть совместимыми путем неявного преобразования. Если типы данных различаются, то получившийся тип данных определяется на основе правил очередности типов данных (для конкретной СУБД). Если типы совпадают, но различаются в точности, масштабе или длине, результат определяется на основе правил, используемых для объединения выражений (для конкретной СУБД) . Типы не определенные ANSI, такие как DATA и BINARY, обычно должны совпадать с другими столбцами такого же нестандартного типа .

Еще одно ограничение на совместимость - это запрет пустых значений (NULL) в любом столбце объединения, причем эти значения необходимо запретить и для всех соответствующих столбцов в других запросах объединения, поскольку пустые значения (NULL) запрещены с ограничением NOT NULL. Кроме того, нельзя использовать UNION в подзапросах, а также нельзя использовать агрегатные функции в предложении SELECT запроса в объединении (однако большинство СУБД пренебрегают этими ограничениями) .

Применение

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

Также стоит отметить, что UNION ALL работает быстрее, чем просто UNION , поскольку по умолчанию при использовании оператора UNION проводится дополнительная фильтрация результата аналогичная SELECT DISTINCT , а при использовании UNION ALL - нет .

Примеры

Использование UNION при выборке из двух таблиц

Даны две таблицы:

При выполнении следующего запроса:

(SELECT * FROM sales2005) UNION (SELECT * FROM sales2006) ;

получается результирующий набор, однако порядок строк может произвольно меняться, поскольку ключевое выражение ORDER BY не было использовано:

В результате отобразятся две строки с Иваном, так как эти строки различаются значениями в столбцах. Но при этом в результате присутствует лишь одна строка с Алексеем, поскольку значения в столбцах полностью совпадают.

Использование UNION ALL при выборке из двух таблиц

Применение UNION ALL дает другой результат, так как дубликаты не скрываются. Выполнение запроса:

(SELECT * FROM sales2005) UNION ALL (SELECT * FROM sales2006) ;

даст следующий результат, выводимый без упорядочивания ввиду отсутствия выражения ORDER BY:

Использование UNION при выборке из одной таблицы

Аналогичным образом можно объединять два разных запроса из одной и той же таблицы (хотя вместо этого, как правило, необходимые параметры комбинируют в одном запросе при помощи ключевых слов AND и OR в условии WHERE):

В результате получится:

Использование UNION как внешнее объединение

При помощи UNION можно создавать также полные внешние объединения (иногда используется в случае отсутствия встроенной прямой поддержки внешних объединений):

(SELECT * FROM employee LEFT JOIN department ON employee. DepartmentID = department. DepartmentID) UNION (SELECT * FROM employee RIGHT JOIN department ON employee. DepartmentID = department. DepartmentID) ;

Но при этом необходимо помнить, что это все же не одно и то же, что и оператор JOIN .

См. также

Примечания

Ссылки

• Понимание SQL. Глава 14. Использование предложения UNION (рус.)
• SQL UNION Operator (англ.)
• UNION (DISTINCT) and UNION ALL (англ.)

• Описание UNION (Transact-SQL) в MSDN (рус.)

• Querying data by using SQL UNION (примеры использования в MySQL) (англ.)
• UNION Syntax (в MySQL) (англ.)

• UNION Clause (в PostgreSQL) (англ.)

• The UNION (ALL), INTERSECT, MINUS Operators (англ.)

• Compose advanced SELECT statements (англ.)