Версия для печати

На главную страницу

Коррелирующие подзапросы

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

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

SELECT id_psg, MAX(date) FROM pass_in_trip GROUP BY id_psg

Однако тут нет рейса. Если мы попытаемся включить рейс в список вывода:

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

SELECT id_psg, trip_no, MAX(date) FROM pass_in_trip GROUP BY id_psg, trip_no

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

SELECT id_psg, trip_no, [date] FROM pass_in_trip pt_1
  WHERE [date]=(
      SELECT MAX([date]) FROM pass_in_trip pt_2
         WHERE pt_1.id_psg=pt_2.id_psg)

дает то, что нужно:

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

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

SELECT pt_1.id_psg, trip_no, [date]
   FROM pass_in_trip pt_1
      JOIN (
      SELECT id_psg, MAX([date]) md
         FROM pass_in_trip GROUP BY id_psg) pt_2
      ON pt_1.id_psg=pt_2.id_psg AND [date]=md

Однако, как это ни покажется удивительным, оптимизатор SQL Server 2000 выберет для обоих запросов идентичный план выполнения при наличии естественного индекса (кластерного) по первичному ключу. Соответственно, и время выполнения запросов не должно отличаться.

» Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

На главную страницу

  Версия для печати