2018-01-21
316
Подзапрос – это оператор SELECT, вложенный в различные разделы (предложения) внешнего оператора Select. В работе рассматривается использование подзапросов в предикате фильтрации (where или having).
Подзапросы могут иметь несколько уровней вложенности.
По умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM этого подзапроса. При ссылке на столбцы таблицы, указанной во фразе FROM внешнего запроса, применяются имена столбцов обязательно с указанием таблицы (в том числе с использованием псевдонимов).
Имеется три варианта соединения подзапроса с внешним предложением, по отношению к предложению WHERE внешнего запроса:
1. Подзапросы, которые не возвращают ни одного или возвращают несколько элементов (начинаются с IN или с оператора сравнения, могут содержать ключевые слова ANY или ALL).
2. Подзапросы, которые возвращают единственное значение (начинаются с простого оператора сравнения).
3. Подзапросы, которые представляют собой тест на существование (начинаются с EXISTS, практически всегда являются коррелированными).
Существует два типа обработки подзапросов: а) коррелированные и б) некоррелированные.
При выполнении некоррелированного подзапроса вначале выполняется внутренний запрос (подзапрос), а затем, по его результатам – внешний.
а.1) Некоррелированные подзапросы, возвращающие несколько или не возвращающие ни одного элемента
| Пример 1. SELECT FirstName, MiddleName, LastName FROM Person.Contact WHERE ContactID IN (SELECT ContactID FROM HumanResources.Employee WHERE Gender='F') | Пример 2. SELECT FirstName, MiddleName, LastName FROM Person.Contact WHERE ContactID IN (SELECT ContactID FROM HumanResources.Employee WHERE ManagerID IN (SELECT EmployeeID FROM HumanResources.Employee WHERE Gender='F') AND Gender='M') | |||||||||||||||||||
| Если записи подзапроса предшествует ключевое слово ANY, то условие сравнения считается выполненным, когда оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса | ||||||||||||||||||||
| Пример 3. SELECT FirstName, MiddleName, LastName FROM Person.Contact WHERE ContactID = ANY (SELECT ContactID FROM HumanResources.Employee WHERE MaritalStatus='M') | Пример 4. SELECT SalesOrderID, OrderDate, Status FROM Sales.SalesOrderHeader WHERE Status=5 AND OrderDate > ANY (SELECT OrderDate FROM Sales.SalesOrderHeader); | |||||||||||||||||||
| В данном примере действие оператора ANY аналогично действию оператора IN в Примере 1. Однако оператор ANY может использовать другие реляционные операторы, помимо равно (=), и, таким образом, делать сравнения, которые превосходят возможности IN. | Например, можно получить номера заказов, дата выполнения которых будет больше, чем одна из дат, находящихся в таблице Sales.SalesOrderHeader (т.е. в выборке не будет самой ранней даты из таблицы). Результирующие строки будут отсортированы по возрастанию OrderDate. | |||||||||||||||||||
| Пример 5. SELECT CustomerID FROM Sales.Customer WHERE TerritoryID <> ANY (SELECT TerritoryID FROM Sales.SalesPerson) | Пример 6. SELECT CustomerID FROM Sales.Customer WHERE TerritoryID NOT IN (SELECT TerritoryID FROM Sales.SalesPerson) | |||||||||||||||||||
| В результат включены все заказчики, кроме тех, чьим территориям продаж соответствует NULL. Внутренний запрос находит все территории продаж, обслуживаемые менеджерами по продажам, а затем для каждой территории внешний запрос находит заказчиков, которые ей не принадлежат | Не выведет ни одного значения, так как так как любая территория, которая может быть назначенная заказчику, обслуживается менеджером по продажам. | |||||||||||||||||||
| Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным, только когда оно выполняется для всех значений в результирующем столбце подзапроса | ||||||||||||||||||||
| Пример 7. SELECT VendorID FROM Purchasing.Vendor WHERE CreditRating > ALL (SELECT CreditRating FROM Purchasing.Vendor WHERE Name = 'Allenson Cycles') | Пример 8. SELECT VendorID FROM Purchasing.Vendor WHERE CreditRating > ALL (SELECT CreditRating FROM Purchasing.Vendor WHERE Name = 'A%') | |||||||||||||||||||
| В результат включены поставщики, кредитный рейтинг которых больше, чем кредитный рейтинг 'Allenson Cycles' | В результат включены поставщики, кредитный рейтинг которых больше, чем кредитный рейтинг всех поставщиков, название которых начинается с 'A' | |||||||||||||||||||
| ||||||||||||||||||||
| Если в результате выполнения подзапроса получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY – невыполненным. |
а.2) Некоррелированные подзапросы, возвращающие единственное значение
Если для сравнения используется оператор = (равенство), то необходимо убедиться (семантически, с помощью агрегирующих функций и т.д.), что подзапрос всегда возвращает одно единственное значение
| Пример 9. SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE BillToAddressID = (SELECT AddressID FROM Person.Address WHERE City = 'New York') | Пример 10. SELECT SalesOrderID, OrderDate, CustomerID FROM Sales.SalesOrderHeader WHERE SubTotal >= (SELECT MAX (LineTotal) FROM Sales.SalesOrderDetail) |
При выполнении коррелированного подзапроса внешний оператор SQL предоставляет значения для внутреннего подзапроса, затем результаты выполнения подзапроса возвращаются во внешний запрос.
б.1) Коррелированные подзапросы, возвращающие несколько или не возвращающие ни одного элемента
| Пример 11. SELECT EmployeeID FROM HumanResources.Employee WHERE MaritalStatus='M' AND ContactID IN (SELECT ContactID FROM Person.Contact WHERE AdditionalContactInfo IS NULL AND ContactID=HumanResources.Employee. ContactID); | Пример 12. SELECT OH.CustomerID, COUNT(*)*75 AS Rebate FROM Sales.SalesOrderHeader OH WHERE Datepart(yy,OrderDate) = '2002' GROUP BY OH.CustomerID HAVING 750 < ALL (SELECT OH1.SubTotal FROM Sales.SalesOrderHeader OH1 WHERE OH1.CustomerID = OH.CustomerID AND Datepart(yy,OrderDate) = '2002' ) |
б.2) Коррелированные подзапросы, возвращающие единственное значение
| Пример 13. SELECT t1.ProductID, t1.SpecialOfferID FROM Sales.SalesOrderDetail t1 WHERE t1.LineTotal> (SELECT avg (t2.LineTotal) FROM Sales.SalesOrderDetail t2 WHERE t1.SpecialOfferID = t2.SpecialOfferID) | Пример 14. SELECT EmployeeID FROM HumanResources.Employee WHERE Title = (SELECT E.Title FROM Person.Contact C JOIN HumanResources.Employee E ON C.ContactID=E.ContactID WHERE FirstName = 'John' AND MiddleName = 'T' AND LastName = 'Kane') |
| В этом примере коррелированный подзапрос имитирует действие оператора GROUP BY: с помощью самообъединения в предложении WHERE подзапроса определяются средние цены по каждой рекламной акции |
б.3) Коррелированные подзапросы, которые представляют собой тест на существование
| Пример 15. SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE EXISTS (SELECT * FROM Sales.Customer WHERE Sales.SalesOrderHeader. CustomerID=CustomerID AND TerritoryID = 1) | Пример 16. SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE NOT EXISTS (SELECT * FROM Sales.Customer WHERE Sales.SalesOrderHeader. CustomerID=CustomerID AND TerritoryID IS NULL) |
