В этом учебном пособии вы узнаете, как использовать 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, для определения результатов запроса.

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

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.

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