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

На уроке будет рассмотрена тема использования операций объединения, пересечения и разности запросов. Разобраны примеры того, как используется 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`

Я уже писал о . И там вывод одной таблицы зависил от содержимого другой. Но бывает нужно, когда требуется полная независимость вывода одной таблицы от другой. Всё, что хочется - это просто в одном запросе вытащить записи сразу из нескольких таблиц , не более того. И вот для этого используется в 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-запросе , упрощающий процедуру вывода сразу из множества таблиц однотипных данных, что в свою очередь очень хорошо скажется на производительности.

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.

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

SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ UNION SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ

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

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

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

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

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

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

SELECT Name, Job, Salary FROM STAFF ORDER BY Job

Name Job Salary
Sanders Mgr 18357.5
Marenghi Mgr 17506.8
Pernal Sales 18171.2
Doctor Sales 12322.4
Factor Sales 16228.7

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

SELECT "Z-TOTAL" AS Name, Job, SUM (Salary) AS Salary FROM STAFF GROUP BY Job

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

Name Job Salary
Z-TOTAL Mgr 35864.3
Z-TOTAL Sales 46722.3

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

(SELECT Name, Job, Salary FROM STAFF ) UNION (SELECT "Z-TOTAL" AS Name, Job, SUM (Salary) AS Salary FROM STAFF GROUP BY Job) ORDER BY Job, Name

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

Name Job Salary
Marenghi Mgr 17506.8
Sanders Mgr 18357.5
Z-TOTAL Mgr 35864.3
Doctor Sales 12322.4
Factor Sales 16228.7
Pernal Sales 18171.2
Z-TOTAL Sales 46722.3

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

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

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

Name Dept Years
Sanders 20 7
Pernal 20 8
Marenghi 38 5
Doctor 20 5
Factor 38 8

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

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

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

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

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

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

ID Name
10 Sanders
30 Marenghi
100 Plotz
140 Fraye
160 Molinare
240 Daniels
260 Jones

Запрос с оператором UNION может возвращать и большее количество столбцов, важно, повторимся, чтобы в объединяемых запросах число столбцов, порядок их следования и типы данных совпадали.

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

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

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

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

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

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

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

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

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

SELECT DISTINCT Price FROM VINIL

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

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

SELECT DISTINCT Price FROM VINIL UNION SELECT DISTINCT Price FROM PAPER

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

Price
300
320
360
400
430
500
530
610
720
800
850

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

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

SELECT DISTINCT Price FROM VINIL UNION ALL SELECT DISTINCT Price FROM PAPER

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

Price
300
320
360
400
400
430
500
500
530
530
610
720
800
850

При помощи оператора 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):

Реляционные базы данных и язык 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):

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

person amount
Иван 1000
Сергей 5000

Использование 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 (англ.)
Реализация в MS SQL Server
  • Описание UNION (Transact-SQL) в MSDN (рус.)
Реализация в MySQL
  • Querying data by using SQL UNION (примеры использования в MySQL) (англ.)
  • UNION Syntax (в MySQL) (англ.)
Реализация в PostgreSQL
  • UNION Clause (в PostgreSQL) (англ.)
Реализация в Oracle
  • The UNION (ALL), INTERSECT, MINUS Operators (англ.)
Реализация в Informix
  • Compose advanced SELECT statements (англ.)
SQL
Версии SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2008
Ключевые слова Create Delete From Having Insert Join Merge Null Order by Prepare Select Top Truncate Union