Главная » Microsoft SQL Server, Базы данных » Коррелированные подзапросы

0

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

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

1.              Внешний запрос выполняется один раз.

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

3.              Результаты подзапроса интегрируются в результирующий набор данных.

Если внешний запрос возвращает 100 строк, то SQL Server выполнит логический эквивалент 101 запроса: один раз выполнит внешний запрос и по одному разу выполнит подзапрос для каждой строки его результата. На самом деле оптимизатор найдет способ, как выполнить коррелированный подзапрос, фактически не выполняя 101 запрос. На практике мне приходилось видеть, когда коррелированные подзапросы превосходили по производительности другие планы запросов. Если они способны решить стоящую перед вами задачу, не пренебрегайте ими только из соображений производительности.

При рассмотрении коррелированных подзапросов в качестве примера мы будем использовать учебную базу данных Outer Banks Adventures. При этом мы будем сравнивать местоположение заказчиков и базовых лагерей. В первую очередь мы скорректируем данные таблиц с помощью следующего пакета запросов:

USE CHA2

UPDATE dbo.BaseCamp SET Region =        ‘NC’           WHERE BaseCampID = 1

UPDATE dbo.BaseCamp SET Region =        ‘NC’           WHERE BaseCampID = 2

UPDATE dbo.BaseCamp SET Region =        ‘BA’           WHERE BaseCampID = 3

UPDATE dbo.BaseCamp SET Region =        1FL’           WHERE BaseCampID = 4

UPDATE dbo.BaseCamp SET Region =        ‘WV’          WHERE BaseCampID = 5

UPDATE dbo.Customer SET Region =          ‘ ND1         WHERE CustomerlD = 1

UPDATE dbo.Customer SET Region =          ‘NC’           WHERE CustomerlD = 2

UPDATE dbo.Customer SET Region =         ‘NJ’            WHERE CustomerlD = 3

UPDATE dbo.Customer SET Region =         ‘NE’            WHERE CustomerlD = 4

UPDATE dbo.Customer SET Region =         ‘ND’           WHERE CustomerlD = 5

UPDATE dbo.Customer SET Region =         ‘NC’           WHERE CustomerlD = 6

UPDATE dbo.Customer SET Region =         ‘NC’           WHERE CustomerlD = 7

UPDATE dbo.Customer SET Region =         ‘BA’           WHERE CustomerlD = 8

UPDATE dbo.Customer SET Region =         ‘NC’           WHERE CustomerlD = 9

UPDATE dbo.Customer SET Region =         1FL1           WHERE CustomerlD = 10

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

SELECT DISTINCT Customer.Region, BaseCamp.Region FROM dbo.Customer

JOIN dbo.Event_mm_Customer

ON Customer.CustomerlD = Event_mm_Customer.CustomerlD JOIN dbo.Event

ON Event_mm_Customer.EventID = Event.EventID JOIN dbo.Tour

ON Event.TourlD = Tour.TourlD JOIN dbo.BaseCamp

ON Tour.BaseCampID = BaseCamp.BaseCampID WHERE Customer.Region IS NOT NULL GROUP BY Customer.Region, BaseCamp.Region ORDER BY Customer.Region, BaseCamp.Region

Получим следующий результат:

Customer BaseCamp Region Region

BA BA

BA FL

BA NC

FL FL

FL NC

FL WV

NC BA

NC FL

NC NC

NC WV

ND BA

ND FL

ND NC

NE FL

NE WV

NJ FL

NJ NC

NJ WV

Имея в наличии эти данные, можно задать первый вопрос: “Кто живет в регионе, в котором находится один из базовых лагерей?” Запрос использует коррелированный подзапрос для

поиска базовых лагерей, которые находятся в регионе заказчика. Этот подзапрос выполняется для каждой строки таблицы Customer, используя именованный диапазон внешнего запроса (С) для обращения к нему. Если существует некоторый базовый лагерь, соответствующий данной строке, то условие EXISTS становится истинным и строка добавляется в результирующий набор данных:

SELECT С.FirstName, С.LastName, С.Region FROM dbo.Customer AS С WHERE EXISTS (SELECT * FROM dbo.BaseCamp AS В WHERE B.Region = C.Region)

ORDER BY LastName, FirstName

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

SELECT DISTINCT С.FirstName, С.LastName, С.Region FROM Customer С JOIN dbo.BaseCamp В

ON C.Region = B.Region ORDER BY LastName, FirstName

Получим следующий результат:

FirstName LastName    Region

Jane                              Doe                BA

Francis                         Franklin         FL

Melissa Anderson        NC

Lauren                          Davis             NC

Wilson                          Davis             NC

John                             Frank             NC

Можно сформулировать более сложный вопрос: “Кто заказывал тур в своем регионе?” Ответ находится в таблице Event_mm_Customer — разрешающей (или объединяющей) таблице между таблицами Event и Customer, служащей для хранения логических отношений “многие ко многим” между клиентами и событиями (много клиентов могут участвовать в одном мероприятии, а один клиент может посещать несколько мероприятий). Таблицу Event_ mm_Customer можно воспринимать как аналог билета, приобретенного одним заказчиком на одно мероприятие.

Внешний запрос логически запускается для каждой строки таблицы Event_mm_Customer, чтобы определить, даст ли операция EXISTS какой-либо результат из коррелированного подзапроса. Подзапрос осуществляет фильтрацию по текущему идентификатору события (Event ID) и региону клиента (Reg ion ID), взятому из внешнего запроса.

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

USE СНА2

SELECT DISTINCT С.FirstName, С.LastName, С.Region AS Home FROM dbo.Customer С JOIN dbo.Event_mm_Customer E ON C.CustomerlD = E.CustomerlD WHERE C.Region IS NOT NULL

AND EXISTS (SELECT *

FROM dbo.Event JOIN dbo.Tour ON Event.TourlD = Tour.TourlD JOIN dbo.BaseCamp ON Tour.BaseCampID = BaseCamp.BaseCampID WHERE BaseCamp.Region = C.Region AND Event.EventID = E.EventID)

Будет получен следующий результат:

FirstName LastName Home

Francis     Franklin              FL

Jane                Doe              BA

John             Frank              NC

Lauren          Davis              NC

Melissa  Anderson              NC

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

SELECT Distinct С.FirstName, С.LastName, С.Region AS Home,

Tour.TourName, BaseCamp.Region FROM dbo.Customer С JOIN dbo.Event_mm_Customer

ON C.CustomerlD = Event_mm_Customer.CustomerlD JOIN dbo.Event

ON Event_mm_Customer.EventID = Event.EventID JOIN dbo.Tour

ON Event.TourlD = Tour.TourlD JOIN dbo.BaseCamp

ON Tour.BaseCampID = BaseCamp.BaseCampID AND C.Region = BaseCamp.Region AND C.Region IS NOT NULL ORDER BY C.LastName

Этот запрос имеет преимущество: он включает столбцы из таблицы Tour, не требуя их явного возвращения подзапросом. В то же время в результатах этого запроса клиенты Lauren Davis и John Frank упоминаются дважды — по одному разу для каждого тура:

FirstName LastName Home TourName                                                     Region

Melissa             Anderson          NC                Outer Banks Lighthouses         NC

Lauren                     Davis          NC                             Appalachian Trail         NC

Lauren                     Davis          NC                Outer Banks Lighthouses         NC

Jane                           Doe          BA                                  Bahamas Dive         BA

John                        Frank          NC                             Appalachian Trail         NC

John                        Frank          NC                 Outer Banks Lighthouses         NC

Francis                Franklin          FL                                    Amazon Trek         FL

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии