Организовать запросы с объединением union. Использование UNION в SQL. Включение или выключение повторяющихся строк

Я уже писал о . И там вывод одной таблицы зависил от содержимого другой. Но бывает нужно, когда требуется полная независимость вывода одной таблицы от другой. Всё, что хочется - это просто в одном запросе вытащить записи сразу из нескольких таблиц , не более того. И вот для этого используется в 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 относится к классу операторов для работы с наборами данных (set operator). К другим таким операторам относятся INTERSECT и ЕХСЕРТ (ЕХСЕРТ и MINUS являются функциональными эквивалентами, но ЕХСЕРТ входит в стандарт ANSI/ Все операторы для наборов данных используются для одновременного манипулирования результирующими наборами двух и более запросов, отсюда и их название.

Синтаксис S0L2003

Технических ограничений на количество запросов в операторе UNION не существует. Общий синтаксис следующий.

UNION

UNION

Ключевые слова

UNION

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

ALL

Объединяются и дубликаты строк из всех результирующих наборов.

DISTINCT

Из результирующего набора удаляются дубликаты строк. Столбцы, содержащие значения NULL, считаются дубликатами. (Если ключевые слова ALL и DISTINCT не используются, по умолчанию принимается DISTINCT.)

Общие правила

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

Типы данных не обязательно должны быть идентичны, но они должны быть совместимы. Например, типы CHAR и VARCHAR являются совместимыми. По умолчанию результирующий цабор использует размер наибольшего из совместимых типов, и в запросе, в котором объединяются три столбца типа CHAR - CHAR(5), CHAR(IO) и CHAR(12), результаты будут в формате CHAR(12), а в столбцы меньшего размера будут добавляться дополнительные пробелы.

Хотя согласно стандарту ANSI оператор INTERSECT имеет более высокий приоритет по сравнению с другими операторами для работы с наборами, на многих платформах эти операторы рассматриваются как имеющие одинаковый приоритет. Вы можете явным образом управлять приоритетом операторов, используя скобки. В противном случае СУБД, скорее всего, будет выполнять их в порядке слева направо.

Предложение DISTINCT может (в зависимости от платформы) потребовать существенных затрат производительности, поскольку при этом часто требуется дополнительный проход по результирующему набору для удаления дублирующихся записей. Предложение ALL для увеличения производительности можно указывать во всех случаях, когда не ожидается наличия дубликатов (или если дубликаты допустимы).

Согласно стандарту ANSI в запросе можно использовать только одно предложение ORDER BY. Ставьте его в конце последней инструкции SELECT. Чтобы избежать двусмысленности при указании столбцов и таблиц, обязательно присваивайте всем столбцам всех таблиц соответствующие псевдонимы. Тем не менее при указании имен столбцов в запросе SELECT … UNION используется только псевдоним из первого запроса. Например:

SELECT au_lname AS lastname, au_fname AS firstname FROM authors UNION SELECT emp_lname AS lastname, emp_fname AS firstname FROM employees ORDER BY lastname, firstname;

Кроме того, поскольку в запросах оператора UNION могут содержаться столбцы с совместимыми типами данных, возможны вариации поведения кода на разных платформах, особенно в том, что касается длины типа данных столбца. Например, если столбец au_fname первого запроса заметно длиннее столбца emplname второго запроса, то разные платформы могут применять разные правила определения используемой длины. Однако, как правило, платформы выбирают для результирующего набора более длинный (и менее ограниченный) тип данных.

Каждая СУРБД может применять свои собственные правила определения имени столбца, если столбцы в разных таблицах имеют разные имена. Как правило, используются имена из первого запроса.

DB2

Платформа DB2 поддерживает ключевые слова UNION и UNION ALL стандарта ANSI плюс предложение VALUES.

[, (выражение-!, выражение2, …)] […] […]

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

Хотя предложение UNION DISTINCT не поддерживается, функциональным эквивалентом является предложение UNION. Предложение CORRESPONDING не поддерживается.

С ключевым словом UNION нельзя использовать такие типы данных, как VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, и структурные типы (но их можно использовать с предложением UNION ALL).

Если во всех таблицах используется одно имя столбца, в результирующем наборе используется это имя. Если же имена столбцов различаются, то DB2 генерирует новое имя столбца. После этого этот столбец нельзя использовать в предложении ORDER BY или предложении FOR UPDATE.

Если в одном запросе используется несколько операторов для наборов данных, то первыми выполняются те, которые заключены в скобки. После этого операторы выполняются в порядке слева направо. Однако все операции INTERSECT выполняются до операций UNION или ЕХСЕРТ. Например:

SELECT empno FROM employee WHERE workdept LIKE "E%" UNION SELECT empno FROM emp_act WHERE projno IN ("IF1000", "IF2000", "AD3110") UNION VALUES ("AA0001), (AB0002"), ("AC0003")

В этом примере мы получаем все ID сотрудников из таблицы employee, которые состоят в любом департаменте, с названием, начинающимся с «Е», а также ID всех сотрудников из бухгалтерской таблицы emp_act, которые работают в проектах IF1000", "IF2000", and "AD3110". Кроме того, сюда всегда включаются ID сотрудников "AA000T, "АВ0002", and "АС00031.

MySQL

Не поддерживается.

Oracle

Платформа Oracle поддерживает ключевые слова UNION и UNION ALL стандарта SQL ANSI. Синтаксис следующий.

Oracle не поддерживает предложение CORRESPONDING. Предложение UNION DISTINCT не. поддерживается, но функциональным эквивалентом является предложение UNION. Платформа Oracle не поддерживает использование предложений UNION ALL и UNION в следующих ситуациях.

Если первый запрос в операторе содержит какие-либо выражения в списке элементов, то присвойте этому столбцу псевдоним при помощи предложения AS. Кроме того, только последний запрос в операторе может содержать предложение ORDER BY. Например, вы можете получить все уникальные идентификаторы магазинов (store_id), без дубликатов используя следующий запрос.

SELECT stor_id FROM stores UNION SELECT stor_id FROM sales;

PostgreSQL

Платформа PostgreSQL поддерживает ключевые слова UNION и UNION ALL стандартного синтаксиса ANSI.

инструкция SElECT 2 UNION

Платформа PostgreSQL не поддерживает использование предложений UNION и UNION ALL в запросах с предложением FOR UPDATE. PostgreSQL не поддерживает предложение CORRESPONDING. Предложение UNION DISTINCT не поддерживается, по функциональным эквивалентом является предложение UNION.

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

SELECT a.au_lname FROM authors AS a WHERE a.au_lnanie LIKE "P%" UNI0N SELECT e.lname FROM employee AS e WHERE e.lname LIKE "P%";

SQL Server

Платформа SQL Server поддерживает ключевые слова UNION и UNION ALL стандартного синтаксиса ANSI.

инструкция SELECT 1 UNION

инструкция SELECT 2 UNION

SQL Server не поддерживает предложение CORRESPONDING. Предложение UNION DISTINCT не поддерживается, но функциональным эквивалентом является предложение UNION.

С предложениями UNION и UNION ALL вы можете использовать инструкцию SELECT…INTO, но ключевое слово INTO должно находиться в первом запросе оператора объединения. Специальные ключевые слова, такие, как SELECT ТОР и GROUP BY…WITH CUBE, можно использовать во всех запросах объединения. Однако обязательно включайте эти предложения во все запросы объединения. Если вы используете предложения SELECT ТОР или GROUP BY… WITH CUBE в одном запросе, операция выполнена не будет.

Все запросы в объединении должны содержать одно и то же количество столбцов. Типы данных столбцов не обязательно должны быть идентичны, но они должны быть неявным образом приводимы друг к другу. Например, совместное применение столбцов CHAR и VARCHAR допускается. При выводе данных SQL Server при определении размера типа данных для столбца результирующего набора использует размер наибольшего столбца. Таким образом, если в инструкции SELECT… UNION используются столбцы CHAR(5) и CHAR(IO), то данные обоих столбцов будут выводиться в столбце CHAR(IO). Числовые типы данных приводятся и отображаются в виде типа с наибольшей точностью.

Например, в следующем запросе объединяются результаты двух независимых запросов, использующих предложение GROUP BY…WITH CUBE.



Похожие публикации