Вложенные запросы (подзапросы)

Язык SQL позволяет создавать запросы внутри других запросов или вложенные запросы.

Это бывает нужно, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT. В таком случае используются операторы SELECT, внедренные в тело другого оператора SELECT.

Вложенным запросом называется запрос, помещаемый в инструкцию SELECT, INSERT, UPDATE или DELETE или в другой вложенный запрос. Подзапрос может быть использован везде, где разрешены выражения.

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

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

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

Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции

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

Внутренний запрос — это запрос, именуемый подзапросом, результирующий набор которого используется внешним запросом.

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

Подзапрос может быть как независимым (простым), так и связанным (коррелированным).

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

Внутренние запросы (подзапросы или вложенные запросы) могут также быть помещены непосредственно после оператора сравнения (=, <, >, <=, >=, <>) в предложения WHERE и HAVING внешнего оператора SELECT.

Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE (см. далее).

Текст подзапроса должен быть заключен в скобки.

Пример: вложенный запрос используется в качестве выражения для столбца с именем MaxUnitPrice в инструкции SELECT

SELECT Ord.SalesOrderID, Ord.OrderDate,

(SELECT MAX(OrdDet.UnitPrice)

FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet

WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice

FROM AdventureWorks.Sales.SalesOrderHeader AS Ord

Вложенный во внешнюю инструкцию SELECT запрос, имеет следующие компоненты:

• обычный запрос SELECT, включающий обычные компоненты списка выборки;

• обычное предложение FROM, включающее одно или более имен таблиц или представлений;

• необязательное предложение WHERE;

• необязательное предложение GROUP BY;

• необязательное предложение HAVING.

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

• фраза ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе;

• список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений – за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS;

• по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы или полные имена);

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

Подзапрос может быть

• однозначным (скалярный);

• многозначным;

• табличным.

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

Использование подзапросов, возвращающих единичное значение

Пример. Определить дату продажи максимальной партии товара.

SELECT Дата, Количество

FROM Сделка

WHERE Количество=(SELECT Max(Количество) FROM Сделка)

Примечание: нельзя прямо использовать предложение WHERE Количество=Max(Количество), поскольку применять агрегирующие функции в предложениях WHERE запрещено. Для достижения желаемого результата следует создать подзапрос, вычисляющий максимальное значение количества, а затем использовать его во внешнем операторе SELECT, предназначенном для выборки дат сделок, где количество товара совпало с максимальным значением.

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

Пример. Определить даты сделок, превысивших по количеству товара среднее значение и указать для этих сделок превышение над средним уровнем.

SELECT Дата, Количество,

Количество-(SELECT Avg(Количество)

FROM Сделка) AS Превышение

FROM Сделка

WHERE Количество>

(SELECT Avg(Количество)

FROM Сделка)

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

Для того чтобы скалярный подзапрос был корректным, он всегда должен возвращать единственное значение. Если такой подзапрос может вернуть несколько значений, то он может во время выполнения завершится ошибкой.

Пример скалярного подзапроса для вывода номеров всех сотрудников, фамилии которых начинаются с буквы B.

SELECT orderid

FROM Sales.Orders

WHERE empid =

(SELECT E.empid

FROM HR.Employees AS E

WHERE E.lastname LIKE N'B%');

GO

Запрос выдает номера заказов (только одна фамилия начинается на букву B):

Так как подзапрос может вернуть несколько значений, то такой подзапрос некорректен и может в каких-то случаях завершиться ошибкой.

SELECT orderid

FROM Sales.Orders

WHERE empid =

(SELECT E.empid

FROM HR.Employees AS E

WHERE E.lastname LIKE N'D%');

GO

Для другой буквы (D) запрос выдает ошибку:

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Если скалярный подзапрос не возвращает ни одного значения, его результат преобразуется в значение NULL и запрос вернет пустой результирующий набор.

Использование подзапросов, возвращающих множество значений

Подзапрос с множеством значений — это подзапрос, который возвращает множественные значения в виде одного столбца.

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

Предикат in применяется в следующем виде:

<скалярное_выражение> IN (<подзапрос_с__множеством_значений>)

Если скалярное_выражение равно одному из значений, возвращенных подзапросом, предикат принимает значение TRUE.

Корректный запрос предыдущего примера будет иметь следующий вид:

SELECT orderid

FROM Sales.Orders

WHERE empid IN

(SELECT E.empid

FROM HR.Employees AS E

WHERE E.lastname LIKE N'D%');

Пример: выбор информации о клиентах из USA

SELECT custid, orderid, orderdate, empid

FROM Sales.Orders

WHERE custid IN

(SELECT C.custid

FROM Sales.Customers AS C

WHERE C.country = N'USA');

Пример: выбор информации о клиентах, не сделавших ни одного заказа

SELECT custid, companyname

FROM Sales.Customers

WHERE custid NOT IN

(SELECT O.custid

FROM Sales.Orders AS O);

Пример. Определить список товаров, которые имеются на складе.

SELECT Название

FROM Товар

WHERE КодТовара In

(SELECT КодТовара FROM Склад)

Пример. Определить список отсутствующих на складе товаров.

SELECT Название

FROM Товар

WHERE КодТовара Not In (SELECT КодТовара

FROM Склад)

Механизм SQL server обеспечивает удаление дубликатов.

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

• { WHERE | HAVING } выражение [ NOT ] IN (подзапрос);

• { WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос);

• {WHERE | HAVING } [ NOT ] EXISTS (подзапрос);

Ключевые слова SOME и ANY эквивалентны.

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

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

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

Предикат EXISTS принимает на входе подзапрос, а на выходе возвращает значение TRUE, если подзапрос отбирает хотя бы одну строку, и FALSE в противном случае.

Предикат EXISTS использует двоичную, а не троичную логику.

Пример: Найти поставщиков, которые поставляют все детали.

SELECT DISTINCT Номер_поставщика

FROM SP

GROUP BY Номер_поставщика

HAVING Count(DISTINCT номер_детали) =

(SELECT Count( номер_детали)

FROM P)

Пример: представлены семантически эквивалентные запросы, а также показаны различия в использовании ключевых слов EXISTS и IN. В обоих примерах приведены допустимые вложенные запросы, извлекающие по одному экземпляру продукции каждого наименования, для которых модель продукта — «long sleeve logo jersey» (кофта с длинными рукавами, с эмблемой), а значения столбцов ProductModelID таблиц Product и ProductModel совпадают.

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product AS p

WHERE EXISTS

(SELECT *

FROM Production.ProductModel AS pm

WHERE p.ProductModelID = pm.ProductModelID

AND pm.Name = 'Long-sleeve logo jersey') ;

GO

Или

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product

WHERE ProductModelID IN

(SELECT ProductModelID

FROM Production.ProductModel

WHERE Name = 'Long-sleeve logo jersey') ;

GO

Результат:

Связанные (коррелированные) подзапросы ссылаются на атрибуты из таблицы, используемой в выходном запросе.

SELECT custid, orderid, orderdate, empid

FROM Sales.Orders AS O1

WHERE orderid =

(SELECT MAX(O2.orderid)

FROM Sales.Orders AS O2

WHERE O2.custid = O1.custid);

Внешний запрос обращен к экземпляру таблицы orders, названному О1; он отбирает заказы, в которых ID заказа равен значению, возвращенному подзапросом.

Подзапрос выбирает из второго экземпляра таблицы Orders, названного О2, заказы, у которых внутренний ID клиента равен внешнему ID клиента, и возвращает максимальный ID заказа среди отобранных. Проще говоря, для каждой строки из О1 подзапрос должен вернуть максимальный ID заказа для текущего клиента. Если ID заказа в О1 и возвращенный подзапросом ID заказа совпадают, ID заказа из О1 является максимальным для текущего клиента и в этом случае строка экземпляра О1 возвращается запросом.

Результат:

Рассмотрим, например, строку результата

85 10739 2007-11-12 00:00:00.000 3

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

SELECT MAX(О2.orderid)

FROM Sales.Orders AS О2

WHERE О2.custid = 85;

Пример:

SELECT emp_lname   FROM employee

WHERE 'p3' IN   (SELECT project_no 

 FROM works_on      

WHERE works_on.emp_no = employee.emp_no);

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

Как система может обрабатывать запрос в примере? Во-первых, система отыскивает первую строку в таблице employee (для внешнего запроса) и сравнивает номер служащего в этом столбце со значениями столбца works_on.emp_no во внутреннем запросе. Если условие внешнего запроса (WHERE 'p3' IN)    не выполнено, то ни одна строка не возвращается внешнему запросу для этого служащего.

Затем система отыскивает следующую строку в таблице employee и повторяет сравнение номеров служащих в обеих таблицах.

Если один из элементов результирующего набора равен значению рЗ, то условие возвращает истинное значение, и отображается соответствующее значение столбца emp_lname.

  

Предикат EXISTS

Как было сказано выше, предикат EXISTS принимает на входе подзапрос, а на выходе возвращает значение TRUE, если подзапрос отбирает хотя бы одну строку, и FALSE в противном случае.

Пример: запрос возвращает клиентов из Испании, сделавших заказы:

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE country = N'Spain'

AND EXISTS

(SELECT * FROM Sales.Orders AS O

WHERE O.custid = C.custid);

Внешний запрос к таблице Customers отбирает только клиентов из Испании, для которых предикат EXISTS возвращает значение TRUE.

Примечание: выше было сказано, что список в предложении SELECT подзапроса состоит из имен отдельных столбцов или составленных из них выражений – за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS. В данном примере используется *.

Пример: выбрать служащих, которые работают над проектом p1.

SELECT emp_lname   

FROM employee   

WHERE EXISTS

(SELECT *       FROM works_on      WHERE employee.emp_no = works_on.emp_no     AND project_no = 'p1');

В предикате EXISTS можно задавать отрицание.

Пример: запрос возвращает клиентов из Испании, не сделавших ни одного заказа.

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE country = N'Spain'

AND NOT EXISTS

(SELECT * FROM Sales.Orders AS O

WHERE O.custid = C.custid);

Пример: выбрать служащих, которые не работают в отделах. расположенных в Seattle.

SELECT emp_lname

 FROM employee   

WHERE NOT EXISTS

 (SELECT *       FROM department       WHERE employee.dept_no = department.dept_no       AND location = 'Seattle');

Пример: база данных сдачи сессии в некотором учебном заведении из трех отношений R1 = (ФИО, Дисциплина, Оценка); R2 = (ФИО, Группа); R₃ = (Группы, Дисциплина)

R1
ФИО	Дисциплина		Оценка
Петров Ф. И.	Базы данных		5
Сидоров К. А.	Базы данных		4
…………….	…………………..		….
Миронов А. В.	Теория информации		Null
…………….	…………………..		….
R2
ФИО	Группа
Петров Ф. И.	4906
Сидоров К. А.	4906
……………	……….

R3
Группа	Дисциплина
4906	Базы данных
4906	Теория информации
…….	…………………..

Список тех, кто сдал все положенные экзамены:

SELECT ФИО

FROM R1 as a

WHERE Оценка > 2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R2, R3

WHERE R2.Группа=R3.Группа AND ФИО=a.ФИО)

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

Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SELECT ФИО

FROM R2, R3

WHERE R2.Группа=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS (SELECT ФИО

FROM R1

WHERE ФИО=R2.ФИО AND Дисциплина = "БД")

Пример: в коррелированном вложенном запросе используется ключевое слово IN. Это запрос, зависящий от результатов выполнения другого запроса. Запрос повторно выполняется для каждой строки, выбранной с помощью другого запроса. Данный запрос получает имена и фамилии сотрудников, для которых значение премии в таблице SalesPerson составляет 5000.00, а соответствующие им идентификационные номера в таблицах Employee и SalesPerson совпадают.

USE AdventureWorks ;

GO

SELECT DISTINCT c.LastName, c.FirstName

FROM Person.Contact AS c JOIN HumanResources.Employee AS e

ON e.ContactID = c.ContactID WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.EmployeeID = sp.SalesPersonID) ;

GO

Примечание: вложенный запрос данной инструкции не может быть выполнен независимо от внешнего запроса. Требуется значение параметра Employee.EmployeeID, однако в процессе обработки строк Employee компонентом SQL Server 2005 Database Engine указанное значение меняется.

Пример: Коррелированный вложенный запрос также может использоваться в предложении HAVING внешнего запроса. В данном примере осуществляется поиск моделей продуктов, для которых максимальная цена в каталоге в два раза превышает среднюю цену по нему.

USE AdventureWorks

GO

SELECT p1.ProductModelID

FROM Production.Product AS p1

GROUP BY p1.ProductModelID

HAVING MAX(p1.ListPrice) >= ALL

(SELECT 2 * AVG(p2.ListPrice)

FROM Production.Product AS p2

WHERE p1.ProductModelID = p2.ProductModelID);

GO

Подзапросы или соединения.

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

Написание оператора select, использующего оператор соединения, обычно является более простым для чтения и восприятия, а также может помочь Database Engine в поиске наиболее эффективной стратегии выборки нужных данных.

Однако существует несколько проблем, которые проще могут быть решены при использовании подзапросов. Также могут существовать иные проблемы, которые проще решить при помощи соединений.

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

Пример: Получение номеров служащих и дат начала работы для всех служащих, у которых дата начала работы равна самой ранней дате:

SELECT emp_no, enter_date

   FROM works_on    WHERE enter_date = (SELECT min(enter_date)                            FROM works_on)

В этом случае нельзя использовать соединение, потому что нужно написать агрегатную функцию в предложении WHERE, что недопустимо.

Соединения имеют преимущества над подзапросами, если список выбора select в запросе содержит столбцы более чем из одной таблицы.

Пример: Получение номеров служащих, фамилий и работ для всех служащих, которые приступили к работе над проектами 15 октября 2007 г.:

SELECT employee.emp_no, emp_lname, job

    FROM employee, works_on

   WHERE employee.emp_no = works_on.emp_no

   AND enter_date = '10.15.2007';

Список выбора select в запросе содержит столбцы emp_no и emp_lname из таблицы employee и столбец job из таблицы works_on. Эквивалентное решение при использовании подзапроса приведет к выдаче сообщения об ошибке, потому что в этом случае может отображаться информация только из внешней таблицы.

Табличные выражения.

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

Существует два типа табличных выражений:

• наследуемые (производные таблицы)

• общие табличные выражения

Наследуемые (производные таблицы)

Производные таблицы (также называемые табличными подзапросами) определяются в элементе from внешнего запроса.

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

К такому отношению невозможно обратиться по имени из какого-либо другого места запроса.

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

Следует отметить, что табличные выражения применяются из соображений логики (а не производительности) обработки. Табличные выражения не оказывают ни положительного, ни отрицательного влияния на производительность.

Пример: В приведенном примере описана производная таблица USACusts (Клиенты из США), основанная на запросе, который возвращает всех клиентов из Соединенных Штатов, а внешний запрос выбирает строки из производной таблицы.

SELECT *

FROM (SELECT custid, companyname

FROM Sales.Customers

WHERE country = N'USA') AS USACusts;

Результат:

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

1. Порядок следования строк должен быть произвольным. Предполагается, что табличное выражение представляет собой реляционную таблицу, а у строк реляционной таблицы определенного порядка следования нет. По этой причине стандарт ANSI SQL не разрешает применять элемент order BY в запросах, определяющих табличные выражения. Язык T-SQL соблюдает это ограничение в большинстве случаев за исключением элемента тор. В запросе с необязательным элементом тор синтаксический элемент ORDER BY служит логической цели: определяет для элемента тор, какие строки выбирать. Если в запросе с элементами тор и orDER BY задается табличное выражение, элемент order by гарантированно применяется только для логического упорядочивания, необходимого элементу тор, а не как обычно для представления данных. Если во внешнем запросе к табличному выражению нет элемента orDER BY для представления результата, определенный порядок его вывода не гарантирован.

2. У всех столбцов должны быть имена. Следовательно, необходимо присвоить псевдонимы столбцов всем выражениям в списке select запроса, применяемого для задания табличного выражения.

3. Все имена столбцов должны быть уникальны. Следовательно, табличное выражение с несколькими столбцами, имеющими одно и то же имя, не корректно. Подобное может произойти, если в запросе, определяющем табличное выражение, соединяются две таб­лицы, и в них обоих есть столбец с одним и тем же именем. Если нужно включить оба столбца в табличное выражение, у них должны быть разные имена столбцов. Эту проблему можно решить, присвоив двум столбцам разные псевдонимы столбцов.

Одно из преимуществ использования табличных выражений — возможность ссылаться в любом синтаксическом элементе внешнего запроса на псевдонимы столбцов, присвоенные в элементе select внутреннего запроса. Это помогает обойти ограничение, запрещающее ссылаться на присвоенные в элементе select псевдонимы столбцов в элементах запроса, которые логически обрабатываются до элемента select (таких как where или group by).

Предположим, что нужно написать запрос к таблице Sales.Orders (Заказы) и вернуть за все годы, указанные в датах заказов, количество разных клиентов, делавших заказы в течение каждого года.

Следующий пример запроса не корректен, т. к. элемент group by ссылается на псевдоним столбца, который был присвоен в элементе select, а синтаксический элемент GROUP BY логически обрабатывается до элемента select.

SELECT

YEAR(orderdate) AS orderyear,

COUNT(DISTINCT custid) AS numcusts

FROM Sales.Orders

GROUP BY orderyear;

Другая конструкция SELECT допустима, но используется две копии одного выражения (в этом случае выражение простое).

SELECT YEAR(orderdate) AS orderyear,

COUNT(DISTINCT custid) AS numcusts

FROM Sales.Orders

GROUP BY YEAR(orderdate);

Лучше применять табличное выражение:

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM (SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders) AS D

GROUP BY orderyear;

В данном программном коде определяется таблица D, получаемая из запроса к таблице Orders (Заказы), который возвращает год заказа и идентификатор клиента из каждой строки.

В списке SELECT внутреннего запроса для присвоения в элементах group by и SELECT псевдонима столбца orderyear выражению YEAR(orderdate) используется формат внутреннего или встроенного назначения псевдонимов. Внешний запрос может ссылаться на псевдоним столбца в обоих синтаксических элементах, поскольку в своей области действия он обращается к таблице D, имеющей столбцы, названные orderyear и custid (id клиента).

Следует отметить, что SQL Server раскрывает определение табличного выражения и обращается непосредственно к его базовым объектам. Если раскрыть этот запрос, то он имеет вид

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts

FROM Sales.Orders

GROUP BY YEAR(orderdate);

В программном коде рассмотренного раньше примера используется формат встраиваемого в элемент запроса назначения выражениям псевдонимов столбцов (псевдонимы внутри вложенного запроса).

Синтаксическая запись для встраиваемого назначения псевдонимов:

<выражение> [AS] <псевдоним>

Хотя в этой конструкции ключевое слово AS необязательно, но оно облегчает чтение программного кода.

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

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

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM (SELECT YEAR(orderdate), custid

FROM Sales.Orders) AS D(orderyear, custid)

GROUP BY orderyear;

GO

Результат:

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

Пример: запрос, применяемый для определения производной таблицы D, ссылается на локальную переменную в элементе where.

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM (SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders

WHERE empid = @empid) AS D

GROUP BY orderyear;

GO

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

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

SELECT orderyear, numcusts

FROM (SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM

(SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders) AS D1

GROUP BY orderyear) AS D2

WHERE numcusts > 70;

Задача самой внутренней производной таблицы, Dl, - присвоить псевдоним столбца orderyear (год заказа) выражению year (orderdate). Запрос к D2 ссылается на orderyear в элементах GROUP BY и SELECT и присваивает псевдоним столбца numcusts (количество клиентов) выражению COUNT(DISTINCT custid).

Запрос к Dl используется для задания производной таблицы D2. Запрос к D2 ссылается на numcusts в элементе WHERE для того, чтобы выбрать годы, в которых более 70 клиентов сделали заказы в течение года.

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

Общие табличные выражения

Общие табличные выражения (ОТВ, Common table expression (CTE)) — другой тип табличных выражений, очень похожих на производные таблицы, но с важными дополнительными преимуществами.

ОТВ были введены в SQL Server 2005 и являются частью стандарт языка ANSI SQL:1999 и более поздних версий стандартов.

ОТВ определяются с помощью инструкции with и имеют следующий синтаксис:

WITH <ОТВ_Имя> [(<Список_столбцов_результата>)]

AS

(

<внутренний_запрос_задающий_ОТВ>

)

<внешний_запрос_к_ОТВ>;

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

Примечание: Синтаксический элемент WITH в языке T-SQL применяется для разных целей. Для того, чтобы исключить неоднозначность, если элемент WITH используется для определения ОТВ, предыдущая инструкция в том же фрагменте, если таковая существует, должна заканчиваться точкой с запятой.

Пример: определяется ОТВ USACusts (Клиенты из США), основанное на запросе, который возвращает всех клиентов из Соединенных Штатов, а внешний запрос выбирает из ОТВ все строки.

WITH USACusts AS

(

SELECT custid, companyname

FROM Sales.Customers

WHERE country = N'USA'

)

SELECT * FROM USACusts;

Результат:

ОТВ также поддерживают две формы назначения псевдонимов столбцов: встраиваемую и внешнюю.

Во встраиваемой форме используется конструкция

<выражение> as <псевдоним_столбца>

Пример встраиваемой формы:

WITH C AS

(

SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders

)

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM C

GROUP BY orderyear;

Во внешней форме используется список результирующих столбцов в круглых скобках, указываемых сразу после имени ОТВ.

Пример внешней формы:

WITH C(orderyear, custid) AS

(

SELECT YEAR(orderdate), custid

FROM Sales.Orders

)

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM C

GROUP BY orderyear;

GO

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

Пример:

WITH C AS

(

SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders

WHERE empid = @empid

)

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM C

GROUP BY orderyear;

GO

Определение множественных ОТВ

Использование ОТВ дает ряд преимуществ по сравнению с производными таблицами.

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

Любое ОТВ может ссылаться на все ОТВ, определенные прежде, и внешний запрос может ссылаться на все ОТВ.

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

SELECT orderyear, numcusts

FROM (SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM (SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders) AS D1

GROUP BY orderyear) AS D2

WHERE numcusts > 70;

Приводится альтернатива этому примеру с использованием множественных ОТВ:

WITH C1 AS

(

SELECT YEAR(orderdate) AS orderyear, custid

FROM Sales.Orders

)

C2 AS

(

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts

FROM C1

GROUP BY orderyear;

)

SELECT orderyear, numcusts

FROM C2

WHERE numcusts > 70;

Каждое ОТВ определено в программе отдельно, в модульном стиле программирования.

Предварительное определение ОТВ, до его использования в запросе, дает и еще одно преимущество. На этапе обработки элемента from внешнего запроса ОТВ уже существует и можно ссылаться на множественные экземпляры одного и того же ОТВ.

Операции над множествами

Операции над множествами— это операции, применяемые к двум входным множествам или, точнее, к двум мультимножествам, результатам двух входных запросов. Мультимножество может содержать дубликаты (результирующие наборы двух входных запросов могут содержать дубликаты). Результирующий набор, также может быть мультимножеством.

Язык T-SQL поддерживает три операции над множествами: union, intersect и except. Операции intersect и except были введены в версии Microsoft SQL Server 2005.

У операции над множествами следующий синтаксис:

Входной запрос1

<операция_над_множествами>

Входной запрос2

[ORDER ВУ ...]

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

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

Можно вставить элемент order by к конечному результату операции над множествами.

Два запроса, участвующие в операции над множествами, должны формировать резуль­тирующие наборы с одинаковым количеством столбцов и у аналогичных столбцов должны совместимые типы данных. Под совместимостью типов данных понимается возможность неявного преобразования типа данных с более низким приоритетом в тип данных с более высоким приоритетом.

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

При сравнении строк операция над множествами считает два значения null равными.

Стандарт ANSI SQL поддерживает две разновидности каждой операции над множествами - distinct (по умолчанию) и all. Элемент distinct логически удаляет дубликаты из двух входных мультимножеств, превращая их в множества, и операция возвращает результат в виде множества.

Элемент all обрабатывает два мультимножества без удаления дубликатов, и операция возвращает также мультимножество, в котором могут быть дубликаты.

SQL Server поддерживает distinct для всех трех операций над множествами, а all — только с операцией union.

Синтаксически нельзя явно задать элемент distinct. Вместо этого он предполагается, когда явно не задан элемент all.

Объединение (UNION) R S отношений R и S можно получить в результате их конкатенации с образованием одного отношения с исключением кортежей-дубликатов. При этом отношения R и S должны быть совместимы, т.е. иметь одинаковое количество полей с совпадающими типами данных. Иначе говоря, отношения должны быть совместимы по объединению.

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

	R
	a1		a2
	A		1
	A		2
	B		1
	B		3
	B		4

S
b1	b2
1	H
2	G
3	H

SELECT R.a1, R.a2

FROM R

UNION

SELECT S.b2, S.b1

FROM S

a1	a2
A	1
A	2
B	1
B	3
B	4
G	2
H	1
H	3

SELECT S1.b1, S1.b2

FROM S1

UNION

SELECT R1.a2, R1.a1

FROM R1

b1	b2
1	A
1	B
1	H
2	A
2	G
3	B
3	H
4	B

В языке T-SQL операция над множествами union объединяет результирующие наборы двух входных запросов. Если строка присутствует в любом из входных наборов, бна пой результате операции union. T-SQL поддерживает обе разновидности операции union all и union (неявный элемент distinct).

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

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

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

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

Синтаксис

    { <query_specification> | ( <query_expression> ) }

  UNION [ ALL ]

  <query_specification | ( <query_expression> )

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

    [ ...n ] ]

Аргументы

<query_specification> | ( <query_expression> ) - Спецификация запроса или выражение запроса, возвращающее данные для объединения с данными из другой спецификации запроса или выражения запроса.

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

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

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

Операция над множествами UNION ALL возвращает все строки, которые встречаются в любом из результирующих мультимножеств, полученных во входных запросах операции, без реального сравнения строк и исключения дубликатов. Если Запрос1 возвращает т строк, а Запрос2 — п строк, тогда Запрос1 UNION ALL Запрос2 вернет т + п строк.

Пример:

Запрос

USE AdventureWorks ;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (98, 118, 61, 3)

дает результат

Запрос

USE AdventureWorks ;

GO

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name ;

дает результат

Запрос

USE AdventureWorks ;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (98, 118, 61, 3)

UNION All

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name

дает результат

Обратите внимание на строки дубликаты.

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

Пример:

USE AdventureWorks ;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID IN (98, 118, 61, 3)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name ;

дает результат без повторяющихся строк

Когда следует применять операцию union all, а когда — union? Если дубликаты возможны после объединения двух множеств операцией объединения, и необходимо вернуть дубликаты, то следует применять union all. Если вероятность наличия дубликатов существует, но нужно вернуть только отличающиеся друг от друга строки, следует использовать union. Если дубликатов быть не может после объединения двух множеств, операции union и Union all логически равнозначны. В этом случае рекомендуется применять Union all, потому что элемент all устраняет дополнительные затраты SQL Server, связанные с про­веркой на наличие дубликатов.

Пересечение (INTERSECT) R S определяет отношение, которое содержит кортежи, присутствующие как в отношении R, так и в отношении S. Отношения R и S должны быть совместимы по объединению.

Пересечением двух таблиц R и S является таблица, содержащая все строки, присутствующие в обеих исходных таблицах одновременно.

R.a1	R.a2	S.b1	S.b2
A	1	A	1
A	2	G	2
B	1	H	1
B	3	B	3

SELECT R.a1, R.a2

FROM R

INTERSECT

SELECT S.b1, S.b2

FROM S

R.a1	R.a2
A	1
B	3

Разность множеств (EXCEPT) – это множество элементов, принадлежащих первому и не принадлежащих второму. В языке T-SQL разность множеств реализуется с помощью операции except. Операция except действует на результирующие наборы двух входных запросов и возвращает строки, которые встречаются в первом наборе, но не встречаются во втором.

SELECT R.a1, R.a2

FROM R

EXCEPT

SELECT S.b1, S.b2

FROM S

Результат:

В Transact-SQL имеются операторы EXCEPT и INTERSECT.

Эти операторы возвращают различные значения, сравнивая результаты двух запросов.

Оператор EXCEPT возвращает все различные значения, возвращенные левым запросом и отсутствующие в результатах выполнения правого запроса.

Оператор INTERSECT возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса.

Основные правила объединения результирующих наборов двух запросов с оператором EXCEPT или INTERSECT таковы:

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

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

Синтаксис

{ <query_specification> | ( <query_expression> ) }

{ EXCEPT | INTERSECT }

{ <query_specification> | ( <query_expression> ) }

<query_specification> | ( <query_expression> ) - Спецификация запроса или выражение запроса, возвращающее данные для объединения с данными из другой спецификации запроса или выражения запроса. Определения столбцов, обрабатываемых при операции EXCEPT или INTERSECT, могут быть разными, но они должны поддерживать возможность сравнения путем неявного преобразования типов. Если типы данных различаются, тип, используемый при выполнении сравнения и возврате результатов, определяется на основе правил приоритета типов данных.

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

Имена столбцов в результирующем наборе, возвращаемом оператором EXCEPT или INTERSECT, совпадают с именами, возвращаемыми запросом, который указан слева от оператора.

Имена столбцов или псевдонимы в предложениях ORDER BY должны ссылаться на имена столбцов, возвращаемых запросом, указанным слева от оператора.

Возможность хранения пустых значений (NULL) в каком-либо столбце результирующего набора, возвращаемого оператором EXCEPT или INTERSECT, зависит от того, поддерживает ли это соответствующий столбец, возвращаемый запросом, указанным слева от оператора.

Если сравниваются строки с целью определения различных значений, два значения NULL считаются равными.

Операция INTERSECT DISTINCT. Операция над множествами intersect логически сначала удаляет дублирующиеся строки из обоих входных мультимножеств, превращая последние в множества, а затем возвращает только те строки, которые встречаются в обоих множествах (строка возвращается при условии, что она встречается хотя бы один раз в обоих входных мультимножествах).

Например, следующий программный код вернет отличные друг от друга местонахождения, которые определяют местонахождения как сотрудников, так и клиентов:

SELECT country, region, city FROM HR.Employees

INTERSECT

SELECT country, region, city FROM Sales.Customers;

Результат:

Пример.

Первый запрос возвращает все значения из таблицы Production.Product

USE AdventureWorks;

GO

SELECT ProductID

FROM Production.Product ;

--Результат: 504 Строки

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

INUSE AdventureWorks;

GO

SELECT ProductID

FROM Production.Product

INTERSECT

SELECT ProductID

FROM Production.WorkOrder ;

--Результат: 238 Строки (products that have work orders, продукты имеющие заказы). Возвращает все различные значения, входящие в результаты выполнения запросов, указанных как слева, так и справа от оператора INTERSECT.

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

USE AdventureWorks;

GO

SELECT ProductID

FROM Production.Product

EXCEPT

SELECT ProductID

FROM Production.WorkOrder ;

--Результат: 266 Строки (products without work orders, продукты не имеющие заказы)

Приоритет. В языке SQL для операций над множествами определен приоритет. У операции INTERSECT более высокий приоритет, чем у операций union и except, а у двух последних приоритет равный. В запросе, содержащем многочисленные операции над множествами, первыми выполняются операции intersect, а затем операции с одинаковым приоритетом в порядке их следования.

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

SELECT country, region, city FROM Production.Suppliers

EXCEPT

SELECT country, region, city FROM HR.Employees

INTERSECT

SELECT country, region, city FROM Sales.Customers;

Для управления порядком выполнения операций над множествами можно применять скобки.

Пример: Запрос вернет местонахождения поставщиков, не являющиеся местонахождениями сотрудников, но совпадающих с местонахождениями клиентов.

(SELECT country, region, city FROM Production.Suppliers

EXCEPT

SELECT country, region, city FROM HR.Employees)

INTERSECT

SELECT country, region, city FROM Sales.Customers;