Использование автономных или связанных вложенных запросов

  • 3 мин

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

Работа со связанными вложенными запросами

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

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

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

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

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

Написание связанных запросов

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

  • Напишите внешний запрос, чтобы принять соответствующий возвращаемый результат из внутреннего запроса. Если внутренний запрос является скалярным, можно использовать операторы равенства и сравнения, такие как =, <, > и <>, в предложении WHERE. Если внутренний запрос может возвращать несколько значений, используйте предикат IN. Планируйте обработку результатов NULL.
  • Определите столбец из внешнего запроса, на который будет ссылаться связанный вложенный запрос. Объявите псевдоним для таблицы, которая является источником для столбца во внешнем запросе.
  • Определите столбец из внутренней таблицы, который будет сравниваться со столбцом внешней таблицы. Создайте псевдоним для исходной таблицы, как вы уже это делали для внешнего запроса.
  • Напишите внутренний запрос, чтобы получить значения из источника на основе входного значения из внешнего запроса. Например, используйте внешний столбец в предложении WHERE внутреннего запроса.

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

Работа с EXISTS

Помимо получения значений из вложенного запроса, T-SQL предоставляет механизм проверки того, будут ли возвращены какие-либо результаты из запроса. Предикат EXISTS определяет, существуют ли какие-либо строки, соответствующие заданному условию, но вместо них возвращает значение TRUE или FALSE. Этот метод полезен для проверки данных без дополнительных затрат на извлечение и обработку результатов.

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

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

В первом примере запроса во вложенном запросе используется предикат COUNT:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

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

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

В первом примере вложенный запрос должен подсчитать все вхождения каждого объекта custid в таблице Sales.SalesOrderHeader и сравнить результаты подсчета с нулевым значением, чтобы показать, что клиент разместил заказы.

Во втором запросе предикат EXISTS возвращает значение TRUE для custid, как только в таблице Sales.SalesOrderHeader будет найден соответствующий заказ. Нет необходимости полностью учитывать каждое вхождение. Также обратите внимание, что при использовании формы с EXISTS вложенный запрос не ограничивается возвратом одного столбца. В нашем запросе мы используем SELECT *. Возвращаемые столбцы не имеют значения, так как мы проверяем только, возвращаются ли вообще какие-либо строки, а не их значения.

С перспективы логической обработки две формы запроса эквивалентны. С перспективы производительности ядро СУБД может обрабатывать запросы по-разному, так как оптимизирует их для выполнения. Протестируйте каждую форму, чтобы выбрать оптимальную.

Примечание.

При преобразовании вложенного запроса с оператором COUNT(*) во вложенный запрос с оператором EXISTS убедитесь, что в нем уже используется оператор SELECT *, а не SELECT COUNT(*). SELECT COUNT(*) всегда возвращает строку, поэтому оператор EXISTS всегда будет возвращать значение TRUE.

Другим полезным применением оператора EXISTS является инверсия вложенного запроса с оператором NOT, как показано в указанном ниже примере. Это позволяет вернуть список всех клиентов, которые никогда не размещали заказ:

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);

SQL Server не нужно возвращать данные о связанных заказах для клиентов, которые разместили заказы. Если объект custid найден в таблице Sales.SalesOrderHeader, оператор NOT EXISTS принимает значение FALSE, а вычисление быстро завершается.

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

  • Ключевое слово EXISTS следует непосредственно после WHERE. Ему не предшествует имя столбца (или другое выражение), кроме случаев, когда оператор NOT также используется.
  • Во вложенном запросе используйте SELECT *. Вложенный запрос не возвращает строки, поэтому не нужно указывать какой-либо столбец.