Union в access

SQL-Урок 12. Комбинированные запросы (UNION)

В большинстве 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 и 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

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

Они одинаковые для примера, только разные названия. Я заполнил их вот такими данными:

Теперь давайте напишем запрос, который объединит результирующие данные в одни, например через union. Синтаксис очень прост:

Как Вы видите, вывелось всего 5 строк, так как у нас первая строка в первом запросе и первая строка во втором запросе одинаковые, поэтому они объединились.

Теперь давайте объединим через union all

Здесь уже вывелись все строки, так как мы указали union all.

А теперь давайте рассмотрим, какие могут быть ошибки даже в этом простом запросе. Например, мы перепутали последовательность полей:

Или мы в первом запросе указали дополнительное поле, а во втором этого не сделали.

Также, например, при использовании order by:

Здесь мы указали сортировку в каждом запросе, а нужно было только в последнем, например:

И напоследок, хотел рассказать об одной хитрости, которую можно использовать тогда когда, например, все-таки необходимо вывести в одном запросе какое-то поле, а в других его нет или просто оно не нужно, для этого можете написать вот такой запрос:

т.е. как Вы видите просто там, где должно быть поле ставить пусто и запрос отлично отработает, например:

И еще один небольшой совет, так как запросы при объединении через union довольно обширные, то лучше на их основе создать представление (Views), в случае если данный запрос Вам требуется постоянно, и уже к этому представлению обращаться каждый раз, когда требуется, а зачем нужны представления мы с Вами уже рассматривали вот здесь – Что такое представления и зачем они нужны.

Наверное, все, что я хотел рассказать о конструкции union и union all языка SQL я рассказал, если есть вопросы по использованию этих операторов, задавайте их в комментариях. Удачи!

Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Microsoft Access UNION and UNION ALL

This Access tutorial explains how to use UNION and UNION ALL in Access Query.

You may also want to read:

UNION and UNION ALL in Access

UNION and UNION ALL are used to combine two Query results vertically into one Query, therefore both Queries require to have the same number of fields and same data types.

When two Queries are combined, there could be rows of record that are exactly the same (duplicates). UNION removes all duplicates while UNION does not remove anything.

UNION and UNION ALL commands have to be used in SQL View, meaning you have to type the SQL commands directly, you don’t have a Query Design View (the friendly drag and drop user interface) to use the commands. However, you can still write the two Queries separately in Query Design View first, and then use UNION to combine them.

Use UNION and UNION All in Access – no duplicated records

Assume that we have two staff lists as below without duplicated data

Create a new Query:

Navigate to CREATE tab > Query Design > SQL View

Type the SQL as below

The syntax of UNION is

For my case I want to select all columns, so I use *

Save and run the Query

Now we can see the results of stafflist_HK is on top, while resultlist_US is at the bottom.

This time instead of typing UNION, type UNION ALL

View the result, you will find no difference as before, it is because UNION is used to remove duplicate records, but the data set has no duplicates.

Use UNION and UNION All in Access – with duplicated records

Add employee 001 to stafflist_US so that both tables contain the same records

Use UNION ALL to combine the two lists

The result shows duplicated records

Now if we use UNION

The 2nd duplicated record is removed

Use UNION to remove duplicates within Table / Query

Assume that you have a Table or Query that contains duplicates. Use UNION to self join the Table

Use UNION in Create Table

To insert UNION result in a new table, you may create a Query first and then use Create Table.

Alternatively, you can directly write a SQL statement to perform UNION and create table in one Query.

Using stafflist_HK and stafflist_US as an example

Outbound References

Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
— Microsoft Most Valuable Professional (Excel)
— Microsoft Community Contributor
— Microsoft Office Specialist in Access / Excel
— Microsoft Specialist in MS Project
— Microsoft Technical Associate
— Microsoft Certified Professional
— IBM SPSS Specialist

Объединение UNION

Для объединения результатов двух и более SQL-запросов SELECT в единую таблицу применяется ключевое слово UNION. Запросыдолжны возвращать одинаковое число и последовательность совместимых типов данных в соответствующих столбцах.

Синтаксис применения SQL запроса UNION

Оператор UNION размещается между SQL запросами :

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

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

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

Правила применения UNION

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

• совместимость типов данных в соответствующих столбцах запросов;
• одинаковое число столбцов во всех объединяемых запросах;
• запрет пустых значений (NULL) в любом столбце объединения;
• нельзя использовать UNION в подзапросах.

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

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

В качестве примера рассмотрим две простенькие таблицы (архивная и рабочая) без определения первичных и внешних ключей, индексов, ограничений на значения колонок и прочих «прелестей» SQL-языка. Допускаем, что в архивной таблице хранится информация о заказах за прошедшие годы. В рабочей таблице размещаются текущие продажи/заказы. Список товаров и услуг, входящих в заказ, не рассматриваем.

Архивная таблица заказов :

Рабочая таблица заказов :

Использование UNION ALL

Запрос выборки данных по продажам товаров (предоставлении услуг) по месяцам за весь период :

Не следует относиться критично к конкатенации и функции преобразования даты; для различных СУБД это может различаться.

Результат выполнения запроса :

Использование UNION

Запрос выборки данных по продажам товаров (предоставлении услуг) по месяцам за весь период для определенного менеджера :

Результат выполнения запроса :

Записи в результирующем наборе можно упорядочить с помощью одного или нескольких полей с использованием оператором ASC или DESC. Номер 1 и 2 в предложении ORDER BY указывает по какому столбцу результирующего набора будет упорядочен результат выборки.

Так как столбцы объединения — это столбцы вывода, они не имеют имен, и следовательно, должны определяться по номеру. Этот номер указывает на их место среди других столбцов вывода.

Внешнее объединение с UNION

При помощи UNION можно создавать полные внешние объединения :

Иногда в запросы вставляют дополнительное отладочное поле, чтобы понять к какому SELECT относится текущая запись набора.

Как применять оператор SQL UNION для объединения результатов запросов

Оператор языка SQL UNION предназначен для объединения результирующих таблиц базы данных, полученных с применением слова SELECT. Условие объединения результирующих таблиц: совпадение числа, порядка следования и типа данных столбцов. ORDER BY следует применять к результату объединения и размещать только в конце составного запроса. Оператор UNION имеет следующий синтаксис:

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

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

Итоги и индивидуальные значения в одной таблице с помощью оператора SQL UNION

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

А если нам требуется получить в одной таблице и сводку всех индивидуальных значений, и итоговые значения? Здесь на помощь приходит оператор SQL UNION, с помощью которого два запроса объединяются. К результату объединения требуется применить упорядочение, используя оператор ORDER BY. Для чего это необходимо, будет лучше понятно из примеров.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Пример 1. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Salary (размер заработной платы), Job (должность) и Years (длительность трудового стажа). Первый запрос возвращает индивидуальные размеры заработной платы, упорядоченные по должностям:

Результатом выполнения запроса будет следующая таблица:

Второй запрос вернёт суммарную заработную плату по должностям. Мы уже готовим этот запрос для соединения с первым, поэтому будем помнить, что условием соединения является равное число столбцов, совпадение их названий, порядка следования и типов данных. Поэтому включаем в таблицу с итогами также столбец Name с произвольным значением ‘Z-TOTAL’:

Результатом выполнения запроса будет следующая таблица:

Теперь объединим запросы при помощи оператора UNION и применим оператору ORDER BY к результату объединения. Группировать следует по двум столбцам: должность (Job) и имя (Name), чтобы строки с итоговыми (суммарными) значениями, в которых значение имени — ‘Z-TOTAL’, находились ниже строк с индивидуальными значениями. Объединение результатов запросов будет следующим:

Результатом выполнения запроса с оператором UNION будет следующая таблица, в которой каждая первая строка в каждой группе должностей будет содержать суммарную заработную плату сотрудников, работающих на этой должности:

Написать запросы с использованием UNION самостоятельно, а затем посмотреть решение

Пример 2. Данные — те же, что в примере 1, но задача немного посложнее. Требуется вывести в одной таблице не только индивидуальные размеры заработной платы, упорядоченные по должностям и суммарную заработную плату по должностям, но суммарную заработную плату по всем сотрудникам.

Пример 3. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Name (фамилия), Dept (номер отдела), и Years (длительность трудового стажа).

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

Другие случаи объединения запросов к одной таблице с помощью оператора SQL UNION

Пример 4. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Salary (размер заработной платы), Job (должность) и Years (длительность трудового стажа). Первый запрос нужен для получения данных о сотрудниках, заработная плата которых более 21000:

Результатом выполнения запроса будет следующая таблица:

Второй запрос возвращает имена сотрудников, должность которых «менеждер», а число лет трудового стажа — менее 8:

Результатом выполнения запроса будет следующая таблица:

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

Результатом выполнения запроса будет следующая таблица:

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

Результатом выполнения запроса будет следующая таблица:

Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, UPDATE, DELETE, HAVING.

Объединение результатов запросов к двум таблицам с помощью оператора SQL UNION

До сих пор мы рассматривали запросы с оператором UNION, в которых объединялись результаты из одной таблицы. Теперь будем объединять результаты из двух таблиц.

Пример 6. Есть база данных склада строительных материалов. В ней есть таблицы, содержащая данные об обоях. Таблица Vinil содержит данные о виниловых обоях, таблица Paper — о бумажных обоях. Требуется узнать данные о ценах обоев из одной и другой таблицы.

Чтобы извлечь не повторяющиеся данные о ценах на виниловые обои, составим запрос со словом DISTINCT:

Результатом выполнения запроса будет следующая таблица:

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

Результатом выполнения запроса будет следующая таблица:

Теперь составим объединённый запрос с оператором UNION:

Так как мы не используем слово ALL, дубликаты значений 400, 500 и 530 выводиться не будут. Результатом выполнения запроса будет следующая таблица:

Пример 7. База данных и таблицы — те же, что и в предыдущем примере.

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

Результатом выполнения запроса будет следующая таблица:

При помощи оператора SQL UNION можно объединить как простые запросы, так и запросы, содержащие подзапросы (вложенные запросы). Рассмотрим соответствующий пример.

Пример 8. Есть база данных «Театр». В её таблице Play содержатся данные о постановках (названия — в столбце Name), в таблице Director — даные о режиссёрах (в столбце Fname — имя, в столбце Lname — фамилия). Первичный ключ таблицы Director — dir_id — идентификационный номер режиссёра. Dir_id также — внешний ключ таблицы Play, он ссылается на первичный ключ таблицы Director. Требуется вывести спектакли режиссеров John Barton и Trevor Nunn.

Решение. Объединим результаты двух запросов — один возвращает спектакли режиссёра John Barton, другой — режиссёра Trevor Nunn. А каждый из этих объединяемых запросов к таблице Play делаем с подзапросом к таблице Director, который возвращает dir_id по имени и фамилии режиссёра. Каждый внешний запрос принимает из вложенного запроса значение ключа dir_id и возвращает названия постановок (Name):