Применение коррелированного подзапроса в условии WHERE

Что такое - Коррелированный Подзапрос?

Коррелированный подзапрос - это оператор SELECT, вложенный в другой оператор T-SQL, и ссылающийся на один или несколько столбцов внешнего запроса. Поэтому можно сказать, что коррелированный подзапрос зависит от внешнего запроса. Это - главное различие между коррелированным и простым подзапросом. Простой подзапрос не ссылается на внешний запрос, он может быть выполнен независимо от него. После того как коррелированный подзапрос будет связан с внешним запросом, он будет возвращать сообщение о синтаксической ошибке, если попытается вызвать самого себя.
Коррелированный подзапрос может быть исполнен несколько раз в процессе обработки оператора T-SQL, содержащего такой подзапрос. Он будет исполняться для каждой строки, отобранной во внешнем запросе. На каждом из этих шагов поля внешнего запроса, на которые ссылается коррелированный подзапрос, будут сравниваться с результатами выборки коррелированного подзапроса. Результат выполнения коррелированного подзапроса определит, попадет ли строка внешнего запроса в результирующую выборку.

Применение коррелированного подзапроса в условии WHERE

Предположим, что Вы хотите получить список всех OrderID, для которых покупатели приобрели не больше 10% от среднего объёма продаж каждого из товаров. Подобный анализ покажет тех покупателей, с которыми нужно связаться, чтобы выяснить причину столь низкого интереса к приобретённому товару. Для этих целей можно использовать коррелированный подзапрос, который будет помещён в предложении WHERE. Вот запрос, который вернет интересующий нас список товаров:

select distinct OrderId from Northwind.dbo.[Order Details] OD where Quantity <= (select avg(Quantity) *.1 from Northwind.dbo.[Order Details] where OD.ProductID = ProductID)

В приведённом выше запросе коррелированный подзапрос располагается в круглых скобках. Как Вы могли заметить, этот коррелированный подзапрос содержит ссылку на "OD.ProductID". Эта ссылка участвует в сравнении "ProductID" внешнего запроса с "ProductID" внутреннего запроса. Движок SQL Server будет исполнять внутренний запрос (коррелированный подзапрос) для каждой записи "[Order Details]". Этот внутренний запрос подсчитает среднее количество (Quantity) для записей каждого товара (ProductID), отобранных во внешнем запросе. Средствами коррелированного подзапроса будет определено, возвращает ли внутренний запрос значение, удовлетворяющее условию WHERE. Если да, то строка, возвращенная внешним запросом, будет включена в итоговую выборку всего запроса T-SQL.

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

select CompanyName, ContactName, Address, City, Country, PostalCode from Northwind.dbo.Customers OuterC where CustomerID in ( select top 2 InnerC.CustomerId from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O on OD.OrderId = O.OrderID join Northwind.dbo.Customers InnerC on O.CustomerID = InnerC.CustomerId Where Region = OuterC.Region group by Region, InnerC.CustomerId order by sum(UnitPrice * Quantity * (1-Discount)) desc ) order by Region

Как видно из примера, внутренний запрос - коррелированный, потому что он ссылается на "OuterC", псевдоним использующейся во внешнем запросе таблицы "Northwind.DBO.Customer". Внутренний запрос использует значения поля "Region" для определения двух лучших покупателей в каждом регионе, ассоциированных со строкой внешнего запроса. В результирующую выборку попадут записи о тех двух "CustomerID" из внешнего запроса, которые попадут в число лучших покупателей.

Дата добавления: 2015-12-08