В этом учебном материале вы узнаете, как использовать SQL оператор UNION ALL с синтаксисом и примерами.

Описание

SQL оператор UNION ALL используется для объединения результирующих наборов из 2 или более операторов SELECT. Он не удаляет повторяющиеся строки между различными операторами SELECT (возвращаются все строки).
Каждый оператор SELECT в UNION ALL должен иметь одинаковое количество полей в наборах результатов с одинаковыми типами данных.

В чем разница между UNION и UNION ALL?

• Оператор UNION удаляет повторяющиеся строки.
• UNION ALL не удаляет повторяющиеся строки

Синтаксис

Синтаксис для оператора UNION ALL в SQL.

Параметры или аргумент

expression1 , expression2 , expression_n Столбцы или расчеты, которые вы хотите получить tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица WHERE conditions Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны

Примечание

• В обоих запросах SELECT должно быть одинаковое количество выражений
• Соответствующие выражения должны иметь одинаковый тип данных в запросах SELECT. Например: expression1 должен иметь одинаковый тип данных как в первом, так и во втором операторе SELECT
• Смотрите также оператор

Пример — одиночное поле с тем же именем

Давайте посмотрим, как использовать SQL оператор UNION ALL, который возвращает одно поле. В этом простом примере поле в обоих операторах SELECT будет иметь одинаковое имя и тип данных.
Например.

PgSQL

SELECT supplier_id FROM suppliers UNION ALL SELECT supplier_id FROM orders ORDER BY supplier_id;

Этот SQL пример UNION ALL будет возвращать supplier_id несколько раз в наборе результатов, если это же значение появилось в таблицах suppliers и orders . SQL оператор UNION ALL не удаляет дубликаты. Если вы хотите удалить дубликаты, попробуйте использовать оператор UNION.
Теперь давайте рассмотрим этот пример, далее приведем некоторые данные.

И вы выполнили следующий оператор UNION ALL.

PgSQL

SELECT supplier_id FROM suppliers UNION ALL SELECT supplier_id FROM orders ORDER BY supplier_id;

Как видно из этого примера, UNION ALL взял все значения supplier_id из таблицы suppliers , а также из таблицы orders и возвратил комбинированный набор результатов. Дубликаты не были удалены, как вы можете видеть по значению supplier_id 2000, которое дважды появляется в наборе результатов.

Пример — разные имена полей

Нет необходимости, чтобы соответствующие столбцы в каждом операторе SELECT имели одинаковые имена, но они должны быть с одинаковыми соответствующими типами данных.
Если у вас нет одинаковых имен столбцов в операторах SELECT, это становится немного сложнее, особенно если вы хотите упорядочить результаты запроса, используя оператор ORDER BY.
Давайте посмотрим, как использовать оператор UNION ALL с разными именами столбцов, и упорядочить результаты запроса.
Например.

PgSQL

В этом SQL примере UNION ALL, поскольку имена столбцов в двух операторах SELECT различаются, более выгодно ссылаться на столбцы в предложении ORDER BY по их положению в наборе результатов. В этом примере мы отсортировали результаты по supplier_id / company_id в порядке возрастания, как обозначено ORDER BY 1 . Поля supplier_id / company_id находятся в позиции № 1 в наборе результатов.
Теперь давайте рассмотрим этот пример подробнее с данными.
Если у вас была таблица suppliers , заполненная следующими записями.

И вы выполнили следующий запрос содержащий UNION ALL.

В большинстве 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 применять не разрешается.

На уроке будет рассмотрена тема использования операций объединения, пересечения и разности запросов. Разобраны примеры того, как используется SQL запрос Union, Exists, а также использование ключевых слов SOME, ANY и All. Рассмотрены строковые функции

Над множеством можно выполнять операции объединения, разности и декартова произведения. Те же операции можно использовать и в sql запросах (выполнять операции с запросами).

Для объединения нескольких запросов используется служебное слово UNION .
Синтаксис:

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

<запрос 1> UNION <запрос 2>

SQL запрос Union служит для объединения выходных строк каждого запроса в один результирующий набор.

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

Объединять вместе можно любое число запросов.

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

1. количество выходных столбцов каждого из запросов должно быть одинаковым;
2. выходные столбцы каждого из запросов должны быть сравнимы между собой по типам данных (в порядке их очередности);
3. в итоговом наборе используются имена столбцов, заданные в первом запросе;
4. ORDER BY может быть использовано только в конце составного запроса, так как оно применяетя к результату объединения.

Пример: Вывести цены на компьютеры и ноутбуки, а также их номера (т.е. произвести выгрузку из двух разных таблиц в одном запросе)

✍ Решение:

1 2 3 4 5 6

SELECT `Номер` , `Цена` FROM pc UNION SELECT `Номер` , `Цена` FROM notebook ORDER BY `Цена`

SELECT `Номер` , `Цена` FROM pc UNION SELECT `Номер` , `Цена` FROM notebook ORDER BY `Цена`

Результат:

Рассмотрим более сложный пример с объединением inner join:

Пример: Найти тип продукции, номер и цену компьютеров и ноутбуков

✍ Решение:

1 2 3 4 5 6 7 8

SELECT product. `Тип` , pc. `Номер` , `Цена` FROM pc INNER JOIN product ON pc. `Номер` = product. `Номер` UNION SELECT product. `Тип` , notebook. `Номер` , `Цена` FROM notebook INNER JOIN product ON notebook. `Номер` = product. `Номер` ORDER BY `Цена`

SELECT product.`Тип` , pc.`Номер` , `Цена` FROM pc INNER JOIN product ON pc.`Номер` = product.`Номер` UNION SELECT product.`Тип` , notebook.`Номер` , `Цена` FROM notebook INNER JOIN product ON notebook.`Номер` = product.`Номер` ORDER BY `Цена`

Результат:

SQL Union 1. Найти производителя, номер и цену всех ноутбуков и принтеров

SQL Union 2. Найти номера и цены всех продуктов, выпущенных производителем Россия

SQL Предикат существования EXISTS

В языке SQL есть средства для выполнения операций пересечения и разности запросов — предложение INTERSECT (пересечение) и предложение EXCEPT (разность). Эти предложения работают подобно тому, как работает UNION: в результирующий набор попадают только те строки, которые присутствуют в обоих запросах — INTERSECT , или только те строки первого запроса, которые отсутствуют во втором — EXCEPT . Но беда в том, что многие СУБД не поддерживают эти предложения. Но выход есть — использование предиката EXISTS .

Предикат EXISTS принимает значение TRUE (истина), если подзапрос возвращает хоть какое-нибудь количество строк, иначе EXISTS принимает значение FALSE. Существует также предикат NOT EXISTS, который действует противоположным образом.

Обычно EXISTS используется в зависимых подзапросах (например, IN).

EXISTS(табличный подзапрос)

Пример: Найти тех производителей компьютеров, которые производят также и ноутбуки

✍ Решение:

SELECT DISTINCT Производитель FROM product AS pc_product WHERE Тип = "Компьютер" AND EXISTS (SELECT Производитель FROM product WHERE Тип = "Ноутбук" AND Производитель = pc_product.Производитель)

Результат:

Найти тех производителей компьютеров, которые не производят принтеров

Ключевые слова SQL SOME | ANY и ALL

Ключевые слова SOME и ANY являются синонимами, поэтому в запросе можно использовать любое из них. Результатом такого запроса будет являться один столбец величин.

Синтаксис:

< выражение>< оператор сравнения> SOME | ANY (< подзапрос> )

<выражение><оператор сравнения>SOME | ANY (<подзапрос>)

Если для какого-нибудь значения X , получаемого из подзапроса, результат операции « » возвращает TRUE , то предикат ANY также равняется TRUE .

Пример: Найти поставщиков компьютеров, у которых номера отсутствуют в продаже (т.е. отсутствуют в таблице pc)

✍ Решение:

Исходные данные таблиц:

Результат:

В примере предикат Номер = ANY(SELECT Номер FROM pc) вернет в том случае значение TRUE, когда Номер из основного запроса найдется в списке Номеров таблицы pc (возвращаемом подзапросом). Кроме того, используется NOT . Результирующий набор будет состоять из одного столбца — Производитель. Чтобы один производитель не выводился несколько раз, введено служебное слово DISTINCT .
Теперь рассмотрим использование ключевого слова ALL:

Пример: Найти номера и цены ноутбуков, стоимость которых превышает стоимость любого компьютера

✍ Решение:

Важно: Стоит заметить, что в общем случае запрос с ANY возвращает множество значений. Поэтому использование подзапроса в предложении WHERE без операторов EXISTS , IN , ALL и ANY , которые дают булево значение (логическое), может привести к ошибке времени выполнения запроса

Пример: Найти номера и цены компьютеров, стоимость которых превышает минимальную стоимость ноутбуков

✍ Решение:

Этот запрос корректен по той причине, что скалярное выражение Цена сравнивается с подзапросом, который возвращает единственное значение

Функции работы со строками в SQL

Функция LEFT вырезает слева из строки заданное вторым аргументом число символов:

LEFT (<строка>,<число>)

Функция RIGHT возвращает заданное число символов справа из строкового выражения:

RIGHT(<строка>,<число>)

Пример: Вывести первые буквы из названий всех производителей

✍ Решение:

SELECT DISTINCT LEFT(`Производитель` , 1) FROM `product`

Результат:

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

✍ Решение:

Функция SQL Replace

Синтаксис:

SELECT `name` , REPLACE(`name` , "а", "аа") FROM `teachers`

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

В этом учебном пособии вы узнаете, как использовать MySQL оператор UNION с синтаксисом и примерами.

Описание

MySQL оператор UNION используется для объединения наборов результатов из 2 или более SELECT предложений. Он удаляет повторяющиеся строки между различными предложениями SELECT.

Каждое предложение SELECT в операторе UNION должно иметь одинаковое количество полей в наборах результатов с одинаковыми типами данных.

Синтаксис

Синтаксис для оператора UNION в MySQL:

Параметры или аргументы

expression1 , expression2 , … expression_n — столбцы или вычисления, которые вы хотите получить.
tables — таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в операторе FROM.
WHERE conditions — необязательный. Условия, которые должны быть выполнены для выбранных записей.
DISTINCT — необязательный. Удаляет дубликаты из результирующего набора, но включение модификатора DISTINCT не влияет на результирующий набор оператора UNION, потому что по умолчанию оператор UNION уже удаляет дубликаты.

Примечание

• В обоих предложениях SELECT должно быть одинаковое количество expression.
• Поскольку оператор UNION по умолчанию удаляет все повторяющиеся строки из результирующего набора, предоставление модификатора UNION DISTINCT не влияет на результаты.
• Имена столбцов из первого предложения SELECT в операторе UNION используются в качестве имен столбцов для результирующего набора.

Пример возвращающий одно поле

Ниже приведен пример MySQL оператора UNION, который возвращает одно поле из нескольких SELECT предложений (и оба поля имеют одинаковый тип данных):

MySQL

SELECT supplier_id FROM suppliers UNION SELECT supplier_id FROM order_details;

SELECT supplier_id FROM suppliers UNION SELECT supplier_id FROM order_details;

В этом примере с MySQL оператором UNION, если supplier_id используется как в suppliers , так и в таблице order_details , он появится один раз в вашем результирующем наборе. MySQL оператор UNION удалит дубликаты. Если вы не хотите удалять дубликаты, попробуйте использовать MySQL оператор UNION ALL.

Пример — использование ORDER BY

MySQL оператор UNION может использовать предложение ORDER BY, для определения результатов запроса.