UNION (Transact-SQL) - объединение

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

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

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

Тип данных должен быть совестимым.

Соглашения о синтаксическом обозначении в Transact-SQL

Синтаксис

{ <query_specification> | (<query_expression>) }

UNION [ ALL ]

<query_specification | (<query_expression>)

[ UNION [ ALL ] <query_specification> | (<query_expression>)

[...n ] ]

Аргументы

<query_specification> | (<query_expression>)

Спецификация запроса или выражение запроса, возвращающее данные для объединения с данными из другой спецификации запроса или выражения запроса. Определения столбцов, которые являются частью операции UNION, не должны совпадать, однако должны быть совместимыми посредством неявного преобразования. Если типы данных различаются, то получившийся тип данных определяется на основе правил очередности типов данных. Если типы совпадают, но различаются в точности, масштабе или длине, результат определяется на основе правил, используемых для объединения выражений. Дополнительные сведения см. в разделе Точность, масштаб и длина (Transact-SQL).

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

UNION

Указывает на то, что несколько результирующих наборов следует объединить и возвратить в виде единого результирующего набора.

ALL

Объединяет в результирующий набор все строки. Это относится и к дублирующимся строкам. Если обратное не указано, дубликаты строк удаляются.

Пример: получить все Интеренет-ссылки, хранимые в базе данных publications. Эти ссылки хранятся в таблицах publishers и wwwsites. Для того, чтобы получить их в одной таблице, мы должны построить следующие запрос:

SELECT publisher,url FROM publishers

UNION

SELECT site,url FROM wwwsites;

В стандарте языка SQL имеются предложения оператора SELECT для выполнения операций пересечения и разности результатов запросов-операндов. Этими предложениями являются INTERSECT [ALL] (пересечение) и EXCEPT [ALL] (разность), которые работают аналогично предложению UNION. В результирующий набор попадают только те строки, которые присутствуют в обоих запросах (INTERSECT) или только те строки первого запроса, которые отсутствуют во втором (EXCEPT). При этом оба запроса, участвующих в операции, должны иметь одинаковое число столбцов, и соответствующие столбцы должны иметь одинаковые (или неявно приводимые) типы данных. Имена столбцов результирующего набора формируются из заголовков первого запроса.

Если не используется ключевое слово ALL (по умолчанию подразумевается DISTINCT), то при выполнении операции автоматически устраняются дубликаты строк. Если указано ALL, то количество дублированных строк подчиняется следующим правилам (n1 - число дубликатов строк первого запроса, n2 - число дубликатов строк второго запроса):

INTERSECT ALL: min(n1, n2)

EXCEPT ALL: n1 - n2, если n1>n2.

Пример

Найти корабли, которые присутствуют как в таблице Ships, так и в таблице Outcomes.

SELECT name FROM Ships

INTERSECT

SELECT ship FROM Outcomes;

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

SELECT ship FROM Outcomes

INTERSECT

SELECT name FROM Ships;

за исключением заголовка. В первом случае единственный столбец будет иметь заголовок name, а во втором - ship. Поэтому запрос

SELECT name FROM (

SELECT ship FROM Outcomes

INTERSECT

SELECT name FROM Ships

) x;

приведет к ошибке:

Invalid column name 'name'.

(неверное имя столбца 'name').

Найти корабли из таблицы Outcomes, которые отсутствуют в таблице Ships.

Задача легко решается при помощи оператора EXCEPT:

SELECT ship FROM Outcomes

EXCEPT

SELECT name FROM Ships;

Операция разности не является коммутативной, поэтому если переставить местами запросы, то мы получим решение совсем другой задачи:

"Найти корабли из таблицы Ships, которые отсутствуют в таблице Outcomes".

Эта задача на языке предметной области звучит так: "Найти корабли, которые не принимали участие в сражениях".

Заметим, что если какой-либо корабль принимал участие в сражениях несколько раз, то по причине исключения дубликатов он будет присутствовать только один раз в результирующем наборе. У нас есть такой корабль - California, но он присутствует также и в таблице Ships, а потому не выводится рассмотренным выше запросом. Поэтому, чтобы продемонстрировать сказанное, исключим его из результатов второго запроса в операции разности:

SELECT ship FROM Outcomes

EXCEPT

SELECT name FROM Ships WHERE name <> 'California';

ship

Bismarck

California

Duke of York

Fuso

Hood

King George V

Prince of Wales

Rodney

Schamhorst

West Virginia

Yamashiro

Для имеющегося набора данных тот же результат мы получим при выполнении следующего запроса:

SELECT ship FROM Outcomes

EXCEPT ALL

SELECT name FROM Ships;

(2 дубликата для 'California' в таблице Outcomes минус 1 - в Ships)

Соответственно, запрос

SELECT ship FROM Outcomes

EXCEPT ALL

SELECT name FROM Ships WHERE name<>'California';

даст нам два вхождения корабля 'California' в результирующем наборе (2 - 0 = 2):

ship

Bismarck

California

California

Duke of York

Fuso

Hood

King George V

Prince of Wales

Rodney

Schamhorst

West Virginia

Yamashiro

8. Запросы с подзапросами в языке SQL, экзистенциальные запросы, подзапросы в операторах манипулирования данными.

Подзапрос - это оператор выбора, который содержится внутри другого оператора выбора, вставки, обновления или удаления, внутри условного оператора или внутри другого подзапроса. Подзапросы обычно содержатся в предложениях where или having SQL оператора или в списке выбора этого оператора. С помощью подзапросов можно проводить дальнейший отбор данных из результатов других запросов. Оператор, содержащий подзапрос, может обрабатывать строки некоторой таблицы, основываясь на результатах вычисления списка выбора подзапроса, который в свою очередь может ссылаться на эту же таблицу как внешний запрос, или обращаться к другой таблице. В языке Transact-SQL подзапросы допускаются почти везде, где допускаются выражения, если подзапрос возвращает одно значение.

Операторы выбора, содержащие один или несколько подзапросов, называются также составными запросами или составными операторами выбора. Возможность включения одного оператора выбора внутрь другого является одной из причин, по которой язык SQL называется “структурированным” (Structured Query Language).

SQL оператор, который включает подзапросы, называемые также внутренними запросами, можно иногда заменить соединением. Есть вопросы, которые можно сформулировать только с помощью подзапросов. Некоторые люди предпочитают всегда использовать подзапросы, поскольку находят их легкими для понимания. Другие стремятся их избегать всегда, когда это возможно. Читатель может выбрать сам удобный для себя способ. (SQL Сервер также переводит некоторые подзапросы в соединения, прежде чем выполнять их).

Если нужно найти все книги, имеющие ту же цену, что и книга Straight Talk About Computers, то это можно сделать за два шага. Во-первых найти цену этой книги:

select price

from titles

where title = "Straight Talk About Computers"

price

-------------

$19.99

(Выбрана 1 строка)

Затем, используя этот результат во втором запросе, уже можно найти все книги, имеющие ту же стоимость, что и Straight Talk:

select title, price

from titles

where price = $19.99

title price

------------------------------------------------------------- --------

The Busy Executive's Database Guide 19.99

Straight Talk About Computers 19.99

Silicon Valley Gastronomic Treats 19.99

Prolonged Data Deprivation: Four Case Studies 19.99

(Выбрано 4 строки)

С помощью подзапроса эта задача решается одним оператором:

select title, price

from titles

where price =

(select price

from titles

where title = "Straight Talk About Computers")

title price

-------------------------------------------------------- --------

The Busy Executive's Database Guide 19.99

Straight Talk About Computers 19.99

Silicon Valley Gastronomic Treats 19.99

Prolonged Data Deprivation: Four Case Studies 19.99

(Выбрано 4 строки)

Общие правила написания и синтаксис подзапросов

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

(select [distinct] список_выбора_подзапроса

[from [[database.]owner.]{название_таблицы | название_вьювера}

[({index название_индекса | prefetch size |[lru|mru]})]}

[holdlock | noholdlock] [shared]

[,[[database.]owner.]{ название_таблицы | название_вьювера }

[({index название_индекса | prefetch size |[lru|mru]})]}

[holdlock | noholdlock] [shared]]... ]

[where условия_отбора]

[group by выражение_без_агрегации [,

выражение_без_агрегации]... ]

[having условия_отбора])

Подзапросы могут быть вложенными в конструкциях (предложениях) where или having внешних операторов выбора (select), вставки (insert), обновления (update) или удаления (delete), а также вложенными в другие подзапросы или помещены в список выбора.

В языке Transact-SQL подзапрос можно помещать почти везде, где допустимы выражения, если этот подзапрос возвращает единственное значение в качестве результата.

Ограничения на подзапросы

На подзапросы накладываются следующие ограничения:

· Подзапросы нельзя использовать в списках предложений order by, group by и compute by.

· Подзапрос не может содержать предложения for browse или union.

· Список выбора внутреннего подзапроса, которому предшествует операция сравнения, может содержать только одно выражение или название столбца, и подзапрос должен возвращать единственный результат. При этом тип данных столбца, указанного в конструкции where внешнего оператора, должен быть совместим c типом данных в столбце, указанным в списке выбора подзапроса (правила здесь такие же как и при соединении).

· В подзапросах не допускаются текстовые (text) и графические (image) данные.

· Подзапросы не могут обрабатывать свои результаты внутренним образом, т.е. подзапрос не может содержать конструкций order by, compute, или ключевого слова into.

· Коррелирующиеся (повторяющиеся) подзапросы не допускаются в конструкции select обновляемого курсора, определенного с помощью declare cursor (определить курсор).

· Количество вложенных уровней для подзапросов не должно превышать 16.

· Максимальное число подзапросов на каждой стороне объединения не больше 16.

Расширенные названия столбцов

В следующем примере столбец pub_id в конструкции where внешнего запроса неявно определяется таблицей publishers из конструкции from этого запроса. Обращение к столбцу pub_id в списке выбора подзапроса определяется конструкцией from подзапроса, т.е. таблицей titles:

select pub_name

from publishers

where pub_id in

(select pub_id

from titles

where type = "business")

Общее правило таково: названия столбцов в операторе неявно определяются таблицей, которая указана в конструкции from этого уровня вложенности.

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

select pub_name

from publishers

where publishers.pub_id in

(select titles.pub_id

from titles

where type = "business")

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

Подзапрос может содержать в себе один или несколько подзапросов следующего уровня. Оператор может содержать подзапросы 16 уровней вложенности.

Типы подзапросов

Существуют два основных типа подзапросов:

· Подзапросы, которым предшествует немодифицированная операция сравнения и которые возвращают единственное значение, называются подзапросами- выражениями (скалярными подзапросами).

· Подзапросы, которые возвращают список значений и которым предшествует ключевое слово in (принадлежит) или операция сравнения, модифицированная кванторами any (некоторый) или all (все), а также подзапросы, проверяющие существование с помощью квантора exists (существует), называются квантифицированными предикатными подзапросами.

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

· Некоррелированный подзапрос может вычисляться как независимый запрос. Иначе говоря, результаты подзапроса подставляются в основной оператор (или внешний запрос). Это не значит, что SQL-сервер именно так выполняет операторы с подзапросами. Некорреляционные подзапросы могут быть заменены соединением и будут выполняться как соединения SQL-сервером.

· Коррелированные подзапросы не могут выполняться как независимые запросы, поскольку они могут обращаться к данным, находящихся в столбцах таблицы, указанной в списке from внешнего запроса. Коррелированные подзапросы детально обсуждаются в конце этой главы.

9. Варианты соединения в языке SQL, трехзначная логика и обработка NULL-значений

10. Управляющие конструкции в языке SQL, хранимые процедуры, триггеры.

Дата добавления: 2015-05-09